1 PACKAGE BODY igs_pe_gen_001 AS
2 /* $Header: IGSPE12B.pls 120.7 2006/05/30 09:57:10 vskumar ship $ */
3 /* Change Hisotry
4 Who When What
5 ------------------------------
6 pkpatel 30-SEP-2002 Bug No: 2600842
7 Added the functions get_hold_auth, validate_hold_desp and release_hold
8 ssawhney 17-feb-2003 Bug 2758856 external holds design change, ENCUMB TBH parameter added.
9 pkpatel 5-FEB-2003 Bug 2683186
10 Modify the error message from 'IGS_PE_HOLD_AUTH_REL' to l_message_name in validate_hold_resp procedure.
11 pkpatel 8-APR-2003 Bug 2804863
12 Modified the procedures validate_hold_resp and release_hold
13 ssaleem 13-OCT-2003 modified the cursor query in get_person_encumb and included
14 Inactive condition
15 asbala 26-dec-03 3304598, added date check in cursor c1 of get_privacy_lvl_format_str
16 prbhardw 18-Aug-2005 Bug No: 3690826 Changed use of IGS_PE_PRIV_LEVEL_V to IGS_PE_PRIV_LEVEL
17 ssawhney 30-Aug-2005 Added function Get_Hold_Count
18 pkpatel 8-Sep-2005 Bug No: 3690826 (removed the cursor c2 in Get_Privacy_Lvl_Format_Str)
19 */
20 FUNCTION Get_Privacy_Lvl_Format_Str (
21 p_person_id igs_pe_priv_level.person_id%TYPE
22 ) RETURN VARCHAR2 AS
23 ------------------------------------------------------------------
24 --Created by : kumma , Oracle India
25 --Date created: 04-JUN-2002
26 --
27 --Purpose:
28 --
29 --
30 --Known limitations/enhancements and/or remarks:
31 --
32 --Change History:
33 --Who When What
34 --asbala 26-dec-03 3304598, added date check in cursor c1
35 -------------------------------------------------------------------
36 lvcDisplayLevel VARCHAR2(1) := 'Y';
37 lnLevel NUMBER(10);
38 lvcLevelDes VARCHAR2(30);
39 lvcPrivacyLevel VARCHAR2(80);
40 lvcPersonPrivacyLevel VARCHAR2(200) := '';
41 ln_data_Group_Id NUMBER(15);
42
43 cursor c1 (lnpersonid number) is
44 SELECT max(dg.lvl) Max_Level, lvl.data_group_id, lvl_description
45 FROM IGS_PE_PRIV_LEVEL lvl, IGS_PE_DATA_GROUPS DG
46 WHERE lvl.person_id = lnpersonid
47 AND TRUNC(SYSDATE) BETWEEN lvl.start_date AND NVL(lvl.end_date,TRUNC(SYSDATE))
48 and lvl.DATA_GROUP_ID = DG.DATA_GROUP_ID
49 GROUP BY lvl.data_group_id, lvl_description
50 ORDER BY 1 desc;
51
52 cursor c3(cp_lookup_type VARCHAR2, cp_lookup_code VARCHAR2) is
53 SELECT meaning
54 FROM igs_lookup_values
55 WHERE lookup_type = cp_lookup_type AND lookup_code = cp_lookup_code;
56
57 lvlinfo c1%rowtype;
58 plinfo c3%rowtype;
59
60 BEGIN
61
62 OPEN c1(p_person_id);
63 FETCH c1 INTO lvlinfo;
64
65 LOOP
66 IF (c1%NOTFOUND) THEN
67 lvcDisplayLevel := 'N';
68 ELSE
69 lnLevel := lvlInfo.Max_Level;
70 ln_data_Group_Id := lvlInfo.Data_Group_Id;
71 lvcLevelDes := lvlInfo.lvl_description;
72 END IF;
73 EXIT;
74 END LOOP;
75 CLOSE c1;
76
77 IF lvcDisplayLevel = 'Y' THEN
78 OPEN c3('PRIVACY_LEVEL', 'LEVEL') ;
79 FETCH c3 INTO plinfo;
80 IF (c3%NOTFOUND) THEN
81 lvcDisplayLevel := 'N';
82 ELSE
83 lvcPrivacyLevel := plinfo.meaning;
84 END IF;
85 CLOSE c3;
86 END IF;
87
88 IF lvcDisplayLevel = 'Y' THEN
89 lvcPersonPrivacyLevel := '*' || lvcLevelDes || ' ' || TO_CHAR(lnLevel) || ' - ' || SUBSTR(lvcPrivacyLevel, 1, 7);
90 END IF;
91
92 RETURN lvcPersonPrivacyLevel;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 return '';
97 END Get_Privacy_Lvl_Format_Str;
98
99 FUNCTION get_person_encumb(p_person_id igs_pe_person.person_id%TYPE) RETURN VARCHAR2 IS
100 ------------------------------------------------------------------
101 --Created by : rboddu , Oracle India
102 --Date created: 16-JUL-2002
103 --
104 --Purpose: 2403680
105 --
106 --
107 --Known limitations/enhancements and/or remarks:
108 --
109 --Change History:
110 --Who When What
111 --ssaleem 13-OCT-2003 modified the cursor query and included
112 -- Inactive condition
113 -------------------------------------------------------------------
114
115 cursor cur_deceased_hold(cp_person_id NUMBER) is
116 SELECT DECODE(pp.date_of_death, NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_flag,
117 igs_en_gen_003.enrp_get_encmbrd_ind(p.party_id) encumbered_ind,
118 p.status status
119 FROM hz_parties p,
120 igs_pe_hz_parties pd,
121 hz_person_profiles pp
122 WHERE p.party_id = pp.party_id AND
123 p.party_id = pd.party_id (+) AND
124 sysdate between pp.effective_start_date AND
125 NVL(pp.effective_end_date,sysdate) AND
126 p.party_id = cp_person_id;
127
128 rec_deceased_hold cur_deceased_hold%ROWTYPE;
129
130 BEGIN
131 OPEN cur_deceased_hold(p_person_id);
132 FETCH cur_deceased_hold INTO rec_deceased_hold;
133 CLOSE cur_deceased_hold;
134
135 IF NVL(rec_deceased_hold.status,'Z') = 'I' THEN
136 FND_MESSAGE.SET_NAME('IGS','IGS_PS_INACTIVE');
137 RETURN FND_MESSAGE.GET;
138 ELSIF NVL(rec_deceased_hold.deceased_flag, 'Z') = 'Y' THEN
139 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PER_DECEASED');
140 RETURN FND_MESSAGE.GET;
141 ELSIF NVL(rec_deceased_hold.encumbered_ind, 'Z') = 'Y' THEN
142 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PER_ENC');
143 RETURN FND_MESSAGE.GET;
144 END IF;
145
146 RETURN '';
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 RETURN '';
151 END get_person_encumb;
152
153 PROCEDURE get_hold_auth
154 (p_fnd_user_id IN fnd_user.user_id%TYPE,
155 p_person_id OUT NOCOPY hz_parties.party_id%TYPE,
156 p_person_number OUT NOCOPY hz_parties.party_number%TYPE,
157 p_person_name OUT NOCOPY hz_person_profiles.person_name%TYPE,
158 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
159 ) IS
160 /*
161 || Created By : pkpatel
162 || Created On : 27-SEP-2002
163 || Purpose : This Procedure will get hold Authorizer Information
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 -- Cursor to find the Person ID of the user logged in
170 CURSOR person_cur IS
171 SELECT person_party_id
172 FROM fnd_user
173 WHERE user_id = p_fnd_user_id AND
174 SYSDATE between start_date AND NVL(end_date,SYSDATE);
175
176 -- Cursor to find the full name of the user logged in
177 CURSOR person_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
178 SELECT person_number,full_name
179 FROM igs_pe_person_base_v
180 WHERE person_id = cp_person_id;
181
182 l_staff VARCHAR2(1);
183 BEGIN
184 -- Check whether the User has a party account
185 OPEN person_cur;
186 FETCH person_cur INTO p_person_id;
187 IF person_cur%NOTFOUND THEN
188 CLOSE person_cur;
189 p_message_name := 'IGS_PE_HOLD_AUTH_CR';
190 RETURN;
191 END IF;
192 CLOSE person_cur;
193
194 IF p_person_id IS NULL THEN -- If no party account then RETURN
195 p_message_name := 'IGS_PE_HOLD_AUTH_CR';
196 RETURN;
197 ELSE
198 -- If party account is present then
199 -- check whether the person is an Active Staff.
200 -- If not then RETURN with setting the message
201 l_staff := igs_en_gen_003.get_staff_ind(p_person_id);
202
203 IF l_staff = 'N' THEN
204 p_message_name := 'IGS_PE_HOLD_AUTH_CR';
205 RETURN;
206 END IF;
207
208 -- Find the full name of the person
209 OPEN person_name_cur(p_person_id);
210 FETCH person_name_cur INTO p_person_number,p_person_name;
211 CLOSE person_name_cur;
212
213 END IF;
214
215 p_message_name := NULL;
216
217 END get_hold_auth;
218
219
220 PROCEDURE validate_hold_resp
221 (p_resp_id IN fnd_responsibility.responsibility_id%TYPE,
222 p_fnd_user_id IN fnd_user.user_id%TYPE,
223 p_person_id IN hz_parties.party_id%TYPE,
224 p_encumbrance_type IN igs_pe_pers_encumb.encumbrance_type%TYPE,
225 p_start_dt IN igs_pe_pers_encumb.start_dt%TYPE,
226 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
227 ) IS
228 /*
229 || Created By : pkpatel
230 || Created On : 27-SEP-2002
231 || Purpose : This Procedure will validate whether the Responsibility passed can release the hold applied on the person
232 || Known limitations, enhancements or remarks :
233 || Change History :
234 || Who When What
235 || pkpatel 5-FEB-2003 Bug 2683186
236 || Modify the error message from 'IGS_PE_HOLD_AUTH_REL' to l_message_name.
237 || pkpatel 8-APR-2003 Bug 2804863
238 || Added the check with igs_pe_gen_001.g_hold_validation for calling igs_pe_gen_001.get_hold_auth
239 || (reverse chronological order - newest change first)
240 */
241 CURSOR hold_cur IS
242 SELECT auth_resp_id
243 FROM igs_pe_pers_encumb
244 WHERE person_id = p_person_id AND
245 encumbrance_type = p_encumbrance_type AND
246 start_dt = p_start_dt;
247
248 hold_rec hold_cur%ROWTYPE;
249
250 l_person_id hz_parties.party_id%TYPE;
251 l_person_number hz_parties.party_number%TYPE;
252 l_person_name hz_person_profiles.person_name%TYPE;
253 l_message_name fnd_new_messages.message_name%TYPE;
254
255 BEGIN
256
257 -- Validate that the person who has logged in has a party account and
258 -- is a STAFF. If he fails any of the above then is not authorized to release the hold.
259 IF igs_pe_gen_001.g_hold_validation = 'Y' THEN
260
261 --when processing for a batch of persons the validation should not happen for each record.
262 --instead the validation should be done at the beginning. Hance the value of the variable
263 --igs_pe_gen_001.g_hold_validation should be 'N' for batch processing.
264
265 get_hold_auth(p_fnd_user_id,
266 l_person_id,
267 l_person_number,
268 l_person_name,
269 l_message_name);
270
271 IF l_message_name IS NOT NULL THEN
272 p_message_name := l_message_name;
273 RETURN;
274 END IF;
275
276 END IF;
277
278 -- Check that the data passed for the Hold is valid.
279 OPEN hold_cur;
280 FETCH hold_cur INTO hold_rec;
281 IF hold_cur%NOTFOUND THEN
282 CLOSE hold_cur;
283 p_message_name := 'IGS_PE_HOLD_AUTH_REL';
284 RETURN;
285 END IF;
286 CLOSE hold_cur;
287
288 -- Check that the responsibility of the person logged in and that of the authoriser are same.
289 -- If not then he is not allowed to release the hold.
290 IF p_resp_id <> hold_rec.auth_resp_id THEN
291 p_message_name := 'IGS_PE_HOLD_AUTH_REL';
292 RETURN;
293 END IF;
294
295 p_message_name := NULL;
296
297
298 END validate_hold_resp;
299
300 PROCEDURE release_hold
301 (p_resp_id IN fnd_responsibility.responsibility_id%TYPE,
302 p_fnd_user_id IN fnd_user.user_id%TYPE,
303 p_person_id IN hz_parties.party_id%TYPE,
304 p_encumbrance_type IN igs_pe_pers_encumb.encumbrance_type%TYPE,
305 p_start_dt IN igs_pe_pers_encumb.start_dt%TYPE,
306 p_expiry_dt IN igs_pe_pers_encumb.expiry_dt%TYPE,
307 p_override_resp IN VARCHAR2,
308 p_comments IN igs_pe_pers_encumb.comments%TYPE,
309 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
310 ) IS
311 /*
312 || Created By : pkpatel
313 || Created On : 27-SEP-2002
314 || Purpose : This Procedure will be the API that will be used to release the hold applied on the person.
315 || For p_override_resp = 'Y' the validation of security as per authorizing responsibility will not happen
316 || 'N' validation will happen
317 || 'X' external holds, security check will not happen
318 || Known limitations, enhancements or remarks :
319 || Change History :
320 || Who When What
321 || (reverse chronological order - newest change first)
322 || pkpatel 5-FEB-2003 Bug 2683186
323 || Passed proper value to the out parameter p_message_name
324 || ssawhney 17-feb-2003 Bug 2758856 - external holds new validations
325 || pkpatel 8-APR-2003 Bug 2804863
326 || Replaced the message IGS_PE_NOT_REL_HOLD with IGS_PE_CANT_REL_HOLD,IGS_PE_PERS_ENCUMB_NOTEXIST
327 */
328
329 CURSOR hold_cur IS
330 SELECT ROWID,pen.*
331 FROM igs_pe_pers_encumb pen
332 WHERE pen.person_id = p_person_id AND
333 pen.encumbrance_type = p_encumbrance_type AND
334 pen.start_dt = p_start_dt;
335
336 hold_rec hold_cur%ROWTYPE;
337 l_message_name VARCHAR2(30);
338 BEGIN
339 IF p_override_resp = 'X' THEN
340
341 -- external holds design, do not validate the resp/auth id for external holds.
342 IF p_person_id IS NULL OR p_encumbrance_type IS NULL OR p_start_dt IS NULL OR p_expiry_dt IS NULL THEN
343 p_message_name := 'IGS_AD_INVALID_PARAM_COMB';
344 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_PARAM_COMB');
345 IGS_GE_MSG_STACK.ADD;
346 APP_EXCEPTION.RAISE_EXCEPTION;
347 END IF;
348 ELSE
349 IF p_resp_id IS NULL OR p_fnd_user_id IS NULL OR p_person_id IS NULL OR p_encumbrance_type IS NULL OR p_start_dt IS NULL
350 OR ( p_expiry_dt IS NULL AND p_comments IS NULL ) THEN
351 p_message_name := 'IGS_AD_INVALID_PARAM_COMB';
352 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_PARAM_COMB');
353 IGS_GE_MSG_STACK.ADD;
354 APP_EXCEPTION.RAISE_EXCEPTION;
355 END IF;
356 END IF;
357
358
359 OPEN hold_cur;
360 FETCH hold_cur INTO hold_rec;
361 IF hold_cur%NOTFOUND THEN
362 CLOSE hold_cur;
363 p_message_name := 'IGS_PE_PERS_ENCUMB_NOTEXIST';
364 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PERS_ENCUMB_NOTEXIST');
365 IGS_GE_MSG_STACK.ADD;
366 APP_EXCEPTION.RAISE_EXCEPTION;
367 ELSE
368 -- if cursor found, and external hold is NOT NULL then raise error
369 IF ( p_override_resp <> 'X' AND hold_rec.external_reference IS NOT NULL) THEN
370 -- called internally and trying to release an external hold.
371 p_message_name := 'IGS_PE_CANT_REL_HOLD';
372 CLOSE hold_cur;
373 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CANT_REL_HOLD');
374 IGS_GE_MSG_STACK.ADD;
375 APP_EXCEPTION.RAISE_EXCEPTION;
376 END IF;
377 END IF;
378 IF hold_cur%ISOPEN THEN
379 CLOSE hold_cur;
380 END IF;
381
382 IF p_override_resp = 'N' THEN
383
384 igs_pe_gen_001.validate_hold_resp
385 (p_resp_id => p_resp_id,
386 p_fnd_user_id => p_fnd_user_id,
387 p_person_id => p_person_id,
388 p_encumbrance_type => p_encumbrance_type,
389 p_start_dt => p_start_dt,
390 p_message_name => l_message_name);
391
392 IF l_message_name IS NOT NULL THEN
393 p_message_name := l_message_name;
394 FND_MESSAGE.SET_NAME('IGS',l_message_name);
395 IGS_GE_MSG_STACK.ADD;
396 APP_EXCEPTION.RAISE_EXCEPTION;
397 END IF;
398 END IF;
399
400
401 igs_pe_pers_encumb_pkg.update_row(
402 x_rowid => hold_rec.rowid ,
403 x_person_id => hold_rec.person_id,
404 x_encumbrance_type => hold_rec.encumbrance_type ,
408 x_comments => NVL(p_comments, hold_rec.comments) ,
405 x_start_dt => hold_rec.start_dt,
406 x_expiry_dt => p_expiry_dt ,
407 x_authorising_person_id => hold_rec.authorising_person_id ,
409 x_spo_course_cd => hold_rec.spo_course_cd,
410 x_spo_sequence_number => hold_rec.spo_sequence_number,
411 x_auth_resp_id => hold_rec.auth_resp_id,
412 x_external_reference => hold_rec.external_reference,
413 x_mode => 'R' );
414
415 -- There is no exception section for this. If any error occured then that will be handled in the the respective calling procedures
416 END release_hold;
417
418 FUNCTION Get_Res_Status (
419 p_person_id hz_parties.party_id%TYPE,
420 p_residency_class igs_pe_res_dtls_all.residency_class_cd%TYPE,
421 p_cal_type igs_ca_inst.cal_type%TYPE,
422 p_sequence_number igs_ca_inst.sequence_number%TYPE
423 ) RETURN VARCHAR2 AS
424 /*
425 || Created By : ssawhney
426 || Created On : 8-nov-2004
427 || Purpose : This function would get the res status of the person passed...for the term/or nearest term residency for term passed.
428
429 || Known limitations, enhancements or remarks :
430 || Change History :
431 || Who When What
432 || (reverse chronological order - newest change first)
433 || vskumar 25-May-2006 Xbuild3 performance fix. Replace c_gap_res cursor query.
434 */
435 CURSOR c_residency ( cp_person_id hz_parties.party_id%TYPE,
436 cp_residency_class igs_pe_res_dtls_all.residency_class_cd%TYPE,
437 cp_cal_type igs_ca_inst.cal_type%TYPE,
438 cp_sequence_number igs_ca_inst.sequence_number%TYPE
439 ) IS
440 SELECT residency_status_cd
441 FROM igs_pe_res_dtls_all
442 WHERE person_id = cp_person_id AND
443 residency_class_cd = cp_residency_class AND
444 cal_type = cp_cal_type AND
445 sequence_number = cp_sequence_number;
446 residency_rec c_residency%ROWTYPE;
447
448
449 CURSOR c_ca ( cp_cal_type igs_ca_inst.cal_type%TYPE,
450 cp_sequence_number igs_ca_inst.sequence_number%TYPE
451 ) IS
452 SELECT cal.start_dt, cal.end_dt FROM igs_ca_inst cal
453 WHERE cal.cal_type = cp_cal_type AND
454 cal.sequence_number = cp_sequence_number;
455 ca_rec c_ca%ROWTYPE;
456
457
458 CURSOR c_gap_res ( cp_person_id hz_parties.party_id%TYPE,
459 cp_residency_class igs_pe_res_dtls_all.residency_class_cd%TYPE,
460 cp_start_dt DATE) IS
461 SELECT res.residency_status_cd, ci.start_dt
462 FROM igs_pe_res_dtls_all res,IGS_CA_INST_ALL ci
463 WHERE res.person_id = cp_person_id AND
464 res.residency_class_cd = cp_residency_class AND
465 res.start_dt <= cp_start_dt AND
466 res.CAL_TYPE = CI.CAL_TYPE AND
467 res.SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
468 ORDER BY res.start_dt desc;
469
470
471 gap_rec c_gap_res%ROWTYPE;
472
473 l_prog_label VARCHAR2(200);
474 l_label VARCHAR2(200);
475 l_debug_str VARCHAR2(2000);
476
477 BEGIN
478 l_prog_label := 'igs.plsql.igs_pe_gen_001.get_res_status';
479
480 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
481 l_label := 'igs.plsql.igs_pe_gen_001.get_res_status.begin';
482 l_debug_str := 'start of proc get_res_status. Parameters p_person_id/p_residency_class/p_cal_type/p_sequence_number: '||
483 p_person_id||'/'||p_residency_class||'/'||p_cal_type||'/'||p_sequence_number;
484 fnd_log.string_with_context(fnd_log.level_procedure, l_label,l_debug_str,NULL,NULL,NULL,NULL,NULL,NULL);
485 END IF;
486
487 --check all parameters passed.
488 IF ( p_person_id IS NULL OR p_residency_class IS NULL OR p_cal_type IS NULL OR p_sequence_number IS NULL ) THEN
489 RETURN NULL;
490 END IF;
491
492 OPEN c_residency(p_person_id,p_residency_class ,p_cal_type , p_sequence_number);
493 FETCH c_residency INTO residency_rec;
494 IF c_residency%FOUND THEN
495 CLOSE c_residency;
496 RETURN residency_rec.residency_status_cd; --return record if direct found for the term.
497 ELSE
498 CLOSE c_residency;
499
500 -- if direct term record not found, then check for gaps, get the most nearest term record
501 -- for which residency is defined...
502 OPEN c_ca(p_cal_type , p_sequence_number);
503 FETCH c_ca INTO ca_rec;
504 IF c_ca%FOUND THEN
505
506 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
507 l_label := 'igs.plsql.igs_pe_gen_001.get_res_status.Nearest Term1';
508 l_debug_str := 'No Residency Status defined for the Term passed. Returning the Status defined for the Nearest Term.';
509 fnd_log.string_with_context(fnd_log.level_procedure, l_label,l_debug_str,NULL,NULL,NULL,NULL,NULL,NULL);
510 END IF;
511
512 CLOSE c_ca;
513 OPEN c_gap_res(p_person_id, p_residency_class, ca_rec.start_dt);
514 FETCH c_gap_res INTO gap_rec;
515 IF c_gap_res%FOUND THEN
516
517 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
518 l_label := 'igs.plsql.igs_pe_gen_001.get_res_status.Nearest Term2';
519 l_debug_str := 'Residency Status: '||gap_rec.residency_status_cd;
520 fnd_log.string_with_context(fnd_log.level_procedure, l_label,l_debug_str,NULL,NULL,NULL,NULL,NULL,NULL);
521 END IF;
522
523 CLOSE c_gap_res;
524 RETURN gap_rec.residency_status_cd;
525 ELSE
526 -- no residency defined for the term or below..
527 CLOSE c_gap_res;
528 RETURN NULL;
529 END IF; -- c_gap
530 ELSE
531 CLOSE c_ca;
532 RETURN NULL;
533 END IF; --c_ca
534 END IF; --c_res
535
536 END Get_Res_Status;
537
538 FUNCTION GET_SS_PRIVACY_LVL (
539 P_person_id igs_pe_priv_level.person_id%TYPE )
540 RETURN VARCHAR2 AS
541 ------------------------------------------------------------------
542 --Created by : gmaheswa , Oracle India
543 --Date created: 29-JUN-2005
544 --
545 --Purpose:
546 --
547 --Known limitations/enhancements and/or remarks:
548 --
549 --Change History:
550 --Who When What
551 -------------------------------------------------------------------
552 lvcDisplayLevel VARCHAR2(1) := 'Y';
553 lnLevel NUMBER(10);
554 lvcLevelDes VARCHAR2(30);
555 lvcPrivacyLevel VARCHAR2(80);
556 lvcPersonPrivacyLevel VARCHAR2(200) := '';
557 ln_data_Group_Id NUMBER(15);
558
559 CURSOR cur_lvl_data_group (lnpersonid number) IS
560 SELECT max(dg.lvl) Max_Level, lvl.DATA_GROUP_ID , dg.lvl_description
561 FROM IGS_PE_PRIV_LEVEL lvl, IGS_PE_DATA_GROUPS DG
562 WHERE lvl.person_id = lnpersonid
563 AND TRUNC(SYSDATE) BETWEEN lvl.start_date AND NVL(lvl.end_date,TRUNC(SYSDATE))
564 and lvl.DATA_GROUP_ID = DG.DATA_GROUP_ID
565 GROUP BY lvl.data_group_id ,dg.lvl_description
566 ORDER BY 1 desc;
567
568 lvlinfo cur_lvl_data_group%ROWTYPE;
569
570 BEGIN
571
572 OPEN cur_lvl_data_group(p_person_id);
573 FETCH cur_lvl_data_group INTO lvlinfo;
574
575 LOOP
576 IF (cur_lvl_data_group%NOTFOUND) THEN
577 lvcDisplayLevel := 'N';
578 ELSE
579 lnLevel := lvlInfo.Max_Level;
580 ln_data_Group_Id := lvlInfo.Data_Group_Id;
581 lvcLevelDes := lvlInfo.lvl_description;
582 END IF;
583 EXIT;
584 END LOOP;
585 CLOSE cur_lvl_data_group;
586
587 IF lvcDisplayLevel = 'Y' THEN
588 lvcPersonPrivacyLevel := lvcLevelDes || ', ' || TO_CHAR(lnLevel);
589 END IF;
590
591 RETURN lvcPersonPrivacyLevel;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 RETURN '';
596 END GET_SS_PRIVACY_LVL;
597
598
599
600 FUNCTION Get_Hold_Count (p_person_id IN hz_parties.party_id%TYPE )
601 RETURN NUMBER AS
602 /*
603 || Created By : ssawhney
604 || Created On : 27-SEP-2002
605 || Purpose : Function returns the count of no. of active holds on the passed person as of sysdate.
606 || Who When What
607 */
608
609 -- future dated holds are NOT active as of sysdate.
610 -- and holds are not valid as on the expiry date...so we need the exp_dt -1 logic.
611
612 CURSOR c_prsn_encumb_cnt
613 (cp_person_id IN hz_parties.party_id%TYPE,
614 cp_sysdate DATE) IS
615 SELECT count(*)
616 FROM IGS_PE_PERS_ENCUMB
617 WHERE person_id = cp_person_id AND
618 (cp_sysdate BETWEEN start_dt AND (expiry_dt - 1) OR
619 (expiry_dt IS NULL AND start_dt <= cp_sysdate));
620
621 l_count NUMBER :=0;
622 l_sysdate DATE := TRUNC(SYSDATE);
623
624 BEGIN
625
626 OPEN c_prsn_encumb_cnt(p_person_id, l_sysdate);
627 FETCH c_prsn_encumb_cnt INTO l_count;
628 CLOSE c_prsn_encumb_cnt;
629
630 RETURN l_count;
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 RETURN l_count;
635 END Get_Hold_Count ;
636
637
638 END igs_pe_gen_001;
[Home] [Help]