1 PACKAGE BODY igs_pe_config_pvt AS
2 /* $Header: IGSPE09B.pls 120.3 2005/07/18 08:03:05 appldev ship $ */
3 /*************************************************************************
4 Created By : mesriniv
5 Date Created By : 2002/02/03
6 Purpose : To be used in Self Service Build,to check if
7 student latest info is available in the System
8
9 Known Limitations,Enhancements or Remarks
10 ENH Bug No.:
11 Bug Desc :
12 Change History :
13 Who When What
14 pkpatel 25-OCT-2002 Bug No: 2613704
15 Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
16 pkpatel 2-DEC-2002 Bug No: 2599109
17 Added column birth_city, birth_country in the call to TBH igs_pe_hz_parties_pkg
18 pkpatel 25-FEB-2003 Bug 2750800
19 Modified the Cursor cur_term_date in PROCEDURE verify_pe_info for performance tuning
20 skpandey 04-JUN-2005 Bug : 4327807
21 The parameter FELONY_CONVICTED_FLAG is added in update_row procedure of igs_pe_hz_parties_pkg package.
22 mmkumar 18-JUL-2005 Party number impact, passed NULL for OSS_ORG_UNIT_CD in call to update_row of IGS_PE_HZ_PARTIES_PKG
23 **********************************************************************/
24
25 g_pkg_name CONSTANT VARCHAR2(30) := 'PE_INFO';
26
27 -- Returns true if the person needs to perform verification, based on user's profile setup
28
29 PROCEDURE verify_pe_info(
30 p_person_id IN NUMBER,
31 p_api_version IN NUMBER ,
32 p_init_msg_list IN VARCHAR2 ,
33 p_commit IN VARCHAR2 ,
34 p_validation_level IN NUMBER ,
35 x_result OUT NOCOPY VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2)
39 /*************************************************************************
40 Created By : mesriniv
41 Date Created : 2002/02/03
42 Purpose : To check if student has to update the latest information
43 x_result is an out NOCOPY variable which will indicate whether the information returned is TRUE or FALSE and does
44 the information needs to be verified.
45 This pe_info_verify was a function and is now made into a procedure and the boolean of the function will
46 be taken care in x_result.
47 If the x_return is TRUE taht means that the STUDENT needs to verify his information again.
48 Known Limitations,Enhancements or Remarks
49 Change History :
50 Who When What
51 ssawhney function converted to procedure
52 ssawhney 22-AUG-2002 Bug 2524217 : defaults removed in params, p_commit defaulted in spec.
53 pkpatel 25-FEB-2003 Bug 2750800 : Modified the Cursor cur_term_date for performance tuning
54 gmaheswa 14-Jul-2005 Bug 4327807 : Added logic for Hiding Verification page in self-service.
55
56 **********************************************************************/
57
58 AS
59
60
61 --Cursor to fetch the Min Load Calendar Start Date and Max Load Calendar End Date so that the student last verified date
62 --and also the Load Calendar is ACTIVE and the dates lie between the start and end dates
63 --can be checked to lie within the Load Calendar Dates
64 CURSOR cur_term_date IS
65 SELECT MAX(ci.start_dt)
66 FROM IGS_CA_INST_ALL ci,
67 IGS_CA_TYPE ct,
68 IGS_CA_STAT ca
69 WHERE ci.cal_type = ct.cal_type
70 AND ct.s_cal_cat = 'LOAD'
71 AND SYSDATE BETWEEN ci.start_dt AND ci.end_dt
72 AND ci.cal_status = ca.cal_status
73 AND ca.s_cal_status='ACTIVE';
74
75
76
77 --Cursor to fetch the Latest Verification date for the person
78 CURSOR cur_verify_time IS
79 SELECT pe_info_verify_time
80 FROM IGS_PE_HZ_PARTIES
81 WHERE party_id = p_person_id;
82
83
84 l_verify_mode fnd_profile_option_values.profile_option_value%TYPE;
85 l_term_date DATE;
86 l_verify_time DATE;
87
88 l_api_name CONSTANT VARCHAR2(30) := 'PE_INFO';
89 l_api_version CONSTANT NUMBER := 1.0;
90
91 BEGIN
92 -- Standard Start of API savepoint
93 SAVEPOINT sp_config;
94
95 -- Initialize message list if p_init_msg_list is set to TRUE.
96 -- default the x_result variable to true, so that if error occurs, then aslo the return status is TRUE
97
98 x_result := 'TRUE' ;
99
100 IF NOT FND_API.Compatible_Api_Call( l_api_version,
101 p_api_version,
102 l_api_name,
103 g_pkg_name) THEN
104
105 -- if the versions of the API and the version passed are different then raise then
106 -- unexpected error message
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112
113 IF FND_API.to_Boolean (p_init_msg_list) THEN
114 FND_MSG_PUB.initialize;
115 END IF;
116
117 -- API body
118
119 --Profile is taken as Every Time Student Logs on to the SS Page
120 l_verify_mode := NVL(FND_PROFILE.VALUE('IGS_PE_VERIFY_MODE'), 'E');
121
122 IF l_verify_mode = 'E' THEN
123 x_result :='TRUE';
124 RETURN;
125 ELSIF l_verify_mode = 'N' THEN
126 -- When the profile IGS: Student Information Verify Mode / IGS_PE_VERIFY_MODE is set as No Student Verification / N,
127 -- then when navigated through the OSS Student Self-Service responsibility the Student Home page should open up directly
128 -- instead of the Student Verify My Information page.
129 x_result := 'FALSE';
130 RETURN;
131 END IF;
132
133 --If the Profile is set for Term Calendar Date or Time Difference
134 --We need to get the Latest date when the STudent has verified the
135 --information
136
137
138 OPEN cur_verify_time;
139 FETCH cur_verify_time into l_verify_time;
140
141 CLOSE cur_verify_time;
142 IF l_verify_time IS NULL THEN
143 x_result :='TRUE';
144 RETURN;
145 END IF;
146
147 --Profile is set for Difference Between the Sysdate and the Last Verified Date
148 IF l_verify_mode = 'D' THEN
149
150
151 IF (TRUNC(SYSDATE) - TRUNC(l_verify_time)) >=
152 TO_NUMBER(NVL(FND_PROFILE.VALUE('IGS_PE_VERIFY_INT'), '0')) THEN
153
154 x_result :='TRUE';
155 RETURN;
156 ELSE
157 x_result :='FALSE';
158 RETURN;
159 END IF;
160 END IF;
161
162 --Profile is set for Term Calendar check with the Last Time the Student has
163 --verified the Information.
164 --If the Verification date does not lie within the Term calendar then
165 --Student Needs to Update the Latest Info
166 --Else its not required
167
168 IF l_verify_mode = 'T' THEN
169
170 OPEN cur_term_date;
171 FETCH cur_term_date INTO l_term_date;
172 CLOSE cur_term_date;
173
174 -- if the cursor does not retrieve any value then return TRUE
175
176 IF l_term_date IS NULL THEN
177 x_result :='TRUE';
178 RETURN;
179 END IF;
180
181 IF (l_verify_time < l_term_date ) THEN
182 x_result :='TRUE';
183 RETURN;
184 ELSE
185 x_result :='FALSE';
186 RETURN;
187 END IF;
188 END IF;
189
190 -- If everything is ok re
191 x_result :='TRUE';
192 RETURN;
193
194 -- End of API body.
195
196
197 EXCEPTION
198 WHEN FND_API.G_EXC_ERROR THEN
199
200 ROLLBACK TO sp_config;
201 x_result :='TRUE';
202 x_return_status := NVL(FND_API.G_RET_STS_ERROR,'E');
203
204
205 FND_MSG_PUB.Count_And_Get (
206 -- p_encoded => FND_API.G_FALSE,
207 p_count => x_msg_count,
208 p_data => x_msg_data );
209
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211
212 ROLLBACK TO sp_config;
213 x_result :='TRUE';
214 x_return_status := NVL(FND_API.G_RET_STS_UNEXP_ERROR,'U');
215
216 FND_MSG_PUB.Count_And_Get (
217 -- p_encoded => FND_API.G_FALSE,
218 p_count => x_msg_count,
219 p_data => x_msg_data );
220
221 WHEN OTHERS THEN
222
223 ROLLBACK TO sp_config;
224 x_result :='TRUE';
225 x_return_status := NVL(FND_API.G_RET_STS_UNEXP_ERROR,'U');
226 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
227 THEN
228 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name );
229 END IF;
230
231
232 -- FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
233 -- FND_MESSAGE.SET_TOKEN('NAME','VERIFY_PE_INFO: '||SQLERRM);
234 -- FND_MSG_PUB.ADD;
235
236 FND_MSG_PUB.Count_And_Get(
237 --p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count,
239 p_data => x_msg_data);
240
241 END verify_pe_info ;
242
243
244
245 PROCEDURE set_pe_info_verify_time(
246 p_person_id IN NUMBER,
247 p_api_version IN NUMBER ,
248 p_init_msg_list IN VARCHAR2 ,
249 p_commit IN VARCHAR2 ,
250 p_validation_level IN NUMBER ,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2
254 )
255 /*************************************************************************
256 Created By : mesriniv
257 Date Created : 2002/02/03
258 Purpose : To Update the Last Verified Date as
259 SYSDATE whenever Student Updates his/her latest info
260 Known Limitations,Enhancements or Remarks
261 Change History :
262 Who When What
263 ssawhney API standards implementation
264 ssawhney 22aug2002 Bug 2524217 : defaults removed in params, p_commit defaulted in spec.
265 pkpatel 25-OCT-2002 Bug No: 2613704
266 Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
267 pkpatel 2-DEC-2002 Bug No: 2599109
268 Added column birth_city, birth_country in the call to TBH igs_pe_hz_parties_pkg
269
270 **********************************************************************/
271 AS
272
273
274 --Cursor to fetch the Person ID from HZ Parties to update the Latest Verification Date
275 CURSOR cur_get_person IS
276 SELECT rowid,hz.*
277 FROM igs_pe_hz_parties hz
278 WHERE hz.party_id =p_person_id
279 FOR UPDATE OF hz.party_id NOWAIT;
280
281 l_cur_person cur_get_person%ROWTYPE;
282
283 l_api_name CONSTANT VARCHAR2(30) := 'PE_INFO';
284 l_api_version CONSTANT NUMBER := 1.0;
285
286 BEGIN
287 -- Standard Start of API savepoint
288 SAVEPOINT sp_verify;
289
290 IF NOT FND_API.Compatible_Api_Call( l_api_version,
291 p_api_version,
292 l_api_name,
293 g_pkg_name) THEN
294
295 -- if the versions of the API and the version passed are different then raise then
296 -- unexpected error message
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize message list if p_init_msg_list is set to TRUE.
301
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 --Initialize message list if p_init_msg_list is set to TRUE.
305 IF FND_API.to_Boolean (p_init_msg_list) THEN
306 FND_MSG_PUB.initialize;
307 END IF;
308
309
310 --Call the HZ Parties Table Handler to Update the Verify Date
311 OPEN cur_get_person;
312 FETCH cur_get_person INTO l_cur_person;
313 IF cur_get_person%FOUND THEN
314
315
316 igs_pe_hz_parties_pkg.update_row(
317 x_rowid =>l_cur_person.rowid,
318 x_party_id =>l_cur_person.party_id,
319 x_deceased_ind =>l_cur_person.deceased_ind,
320 x_archive_exclusion_ind =>l_cur_person.archive_exclusion_ind,
321 x_archive_dt =>l_cur_person.archive_dt,
322 x_purge_exclusion_ind =>l_cur_person.purge_exclusion_ind,
323 x_purge_dt =>l_cur_person.purge_dt,
324 x_oracle_username =>l_cur_person.oracle_username,
325 x_proof_of_ins =>l_cur_person.proof_of_ins,
326 x_proof_of_immu =>l_cur_person.proof_of_immu,
327 x_level_of_qual =>l_cur_person.level_of_qual ,
328 x_military_service_reg =>l_cur_person.military_service_reg ,
329 x_veteran =>l_cur_person.veteran ,
330 x_institution_cd =>l_cur_person.institution_cd,
331 x_oi_local_institution_ind =>l_cur_person.oi_local_institution_ind ,
332 x_oi_os_ind =>l_cur_person.oi_os_ind ,
333 x_oi_govt_institution_cd =>l_cur_person.oi_govt_institution_cd ,
334 x_oi_inst_control_type =>l_cur_person.oi_inst_control_type,
335 x_oi_institution_type =>l_cur_person.oi_institution_type ,
336 x_oi_institution_status =>l_cur_person.oi_institution_status ,
337 x_ou_start_dt =>l_cur_person.ou_start_dt ,
338 x_ou_end_dt =>l_cur_person.ou_end_dt ,
339 x_ou_member_type =>l_cur_person.ou_member_type ,
340 x_ou_org_status =>l_cur_person.ou_org_status ,
341 x_ou_org_type =>l_cur_person.ou_org_type ,
342 x_inst_org_ind =>l_cur_person.inst_org_ind ,
343 x_inst_priority_cd =>l_cur_person.inst_priority_cd ,
344 x_inst_eps_code =>l_cur_person.inst_eps_code ,
345 x_inst_phone_country_code =>l_cur_person.inst_phone_country_code ,
346 x_inst_phone_area_code =>l_cur_person.inst_phone_area_code,
347 x_inst_phone_number =>l_cur_person.inst_phone_number ,
348 x_adv_studies_classes =>l_cur_person.adv_studies_classes ,
349 x_honors_classes =>l_cur_person.honors_classes ,
350 x_class_size =>l_cur_person.class_size ,
351 x_sec_school_location_id =>l_cur_person.sec_school_location_id ,
352 x_percent_plan_higher_edu =>l_cur_person.percent_plan_higher_edu ,
353 x_fund_authorization =>l_cur_person.fund_authorization ,
354 x_pe_info_verify_time =>TRUNC(SYSDATE) ,
355 x_birth_city =>l_cur_person.birth_city ,
356 x_birth_country =>l_cur_person.birth_country ,
357 x_oss_org_unit_cd => NULL,
358 x_felony_convicted_flag =>l_cur_person.felony_convicted_flag ,
359 x_mode =>'R'
360 );
361 END IF;
362 CLOSE cur_get_person;
363
364
365
366 -- End of Updation.
367
368 -- Standard check of p_commit.
369 IF FND_API.To_Boolean( p_commit ) THEN
370 COMMIT;
371 END IF;
372
373 -- Standard call to get message count and if count is 1, get message info.
374 FND_MSG_PUB.Count_And_Get
375 ( p_count => x_msg_count ,
376 p_data => x_msg_data
377 );
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381
382 ROLLBACK TO sp_verify;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 FND_MSG_PUB.Count_And_Get (--p_encoded => FND_API.G_FALSE,
385 p_count => x_msg_count,
386 p_data => x_msg_data );
387
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389
390 ROLLBACK TO sp_verify;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 FND_MSG_PUB.Count_And_Get ( --p_encoded => FND_API.G_FALSE,
393 p_count => x_msg_count,
394 p_data => x_msg_data );
395
396 WHEN OTHERS THEN
397
398 ROLLBACK TO sp_verify;
399
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
402 THEN
403 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name );
404 END IF;
405 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
406 FND_MESSAGE.SET_TOKEN('NAME',' SET_PE_INFO_VERIFY_TIME: '||SQLERRM);
407 FND_MSG_PUB.ADD;
408
409 FND_MSG_PUB.Count_And_Get(--p_encoded => FND_API.G_FALSE,
410 p_count => x_msg_count,
411 p_data => x_msg_data);
412 END set_pe_info_verify_time;
413
414
415 END igs_pe_config_pvt;
[Home] [Help]