[Home] [Help]
PACKAGE BODY: APPS.PER_SIT_PKG
Source
1 PACKAGE BODY PER_SIT_PKG as
2 /* $Header: pesit01t.pkb 115.3 2004/06/15 01:00:26 vanantha ship $ */
3 --
4 -- Constants to define Special Information Type Categories
5 -- used by this package
6 --
7 G_JOB varchar2(10) := 'JOB';
8 G_POSITION varchar2(10) := 'POS';
9 G_SKILL varchar2(10) := 'SKILL';
10 G_OTHER varchar2(10) := 'OTHER';
11 G_OSHA varchar2(10) := 'OSHA';
12 G_ADA varchar2(10) := 'ADA';
13 --
14 ----------------------------------------------------------------------
15 -- check_unique_sit
16 --
17 -- Ensures that the Special Info Type is unique within the Business Group
18 ----------------------------------------------------------------------
19 --
20 procedure check_unique_sit(p_special_information_type_id in number
21 ,p_bg_id in number
22 ,p_id_flex_num in number) is
23 cursor c is
24 select 'x'
25 from per_special_info_types
26 where id_flex_num = p_id_flex_num
27 and (p_special_information_type_id is null or
28 (p_special_information_type_id is not null and
29 special_information_type_id <> p_special_information_type_id))
30 and business_group_id = p_bg_id;
31 --
32 l_exists varchar2(1);
33 begin
34 hr_utility.set_location('per_sit_pkg.check_unique_sit',1);
35 open c;
36 fetch c into l_exists;
37 if c%found then
38 close c;
39 hr_utility.set_message(801,'PER_7836_DEF_FUR_EXISTS');
40 hr_utility.raise_error;
41 end if;
42 close c;
43 end check_unique_sit;
44 --
45 --
46 ----------------------------------------------------------------------
47 -- sit_flex_used
48 --
49 -- Determines whether the Flex Structure has been used in Personal
50 -- Analyses
51 ----------------------------------------------------------------------
52 --
53 function sit_flex_used(p_bg_id number
54 ,p_id_flex_num number) return boolean is
55 cursor c is
56 select 'x'
57 from per_person_analyses pa,
58 per_special_info_types c
59 where pa.id_flex_num = c.id_flex_num
60 and pa.business_group_id = p_bg_id
61 and c.business_group_id = pa.business_group_id
62 and pa.id_flex_num = p_id_flex_num; --bug 3648683
63 --
64 l_exists varchar2(1);
65 begin
66 hr_utility.set_location('per_sit_pkg.sit_flex_used',1);
67 open c;
68 fetch c into l_exists;
69 if c%found then
70 close c;
71 return(TRUE);
72 else
73 close c;
74 return(FALSE);
75 end if;
76 end sit_flex_used;
77 --
78 --
79 ----------------------------------------------------------------------
80 -- sit_del_validation
81 --
82 -- Delete Validation
83 ----------------------------------------------------------------------
84 --
85 procedure sit_del_validation(p_bg_id number
86 ,p_id_flex_num number) is
87 begin
88 hr_utility.set_location('per_sit_pkg.sit_del_validation',1);
89 if sit_flex_used(p_bg_id
90 ,p_id_flex_num) then
91 hr_utility.set_message(801,'PER_7837_DEF_FUR_IN_USE');
92 hr_utility.raise_error;
93 end if;
94 end sit_del_validation;
95 --
96 --
97 ----------------------------------------------------------------------
98 -- populate_fields
99 --
100 -- POST-QUERY population of non-base table fields
101 ----------------------------------------------------------------------
102 --
103 procedure populate_fields(p_id_flex_num number
104 ,p_name IN OUT NOCOPY varchar2
105 ,p_flex_enabled IN OUT NOCOPY varchar2) is
106 cursor c is
107 select id_flex_structure_name
108 , enabled_flag
109 from fnd_id_flex_structures_vl
110 where id_flex_code = 'PEA'
111 and id_flex_num = p_id_flex_num;
112 --
113 begin
114 hr_utility.set_location('per_sit_pkg.populate_fields',1);
115 open c;
116 fetch c into p_name, p_flex_enabled;
117 close c;
118 end populate_fields;
119 --
120 --
121 ----------------------------------------------------------------------
122 -- get_special_info_type_id
123 --
124 -- Retrives next UNIQUE ID
125 ----------------------------------------------------------------------
126 --
127 function get_special_info_type_id return number is
128 l_id number;
129 cursor c is
130 select per_special_info_types_s.nextval
131 from sys.dual;
132 --
133 begin
134 open c;
135 fetch c into l_id;
136 close c;
137 return(l_id);
138 end;
139 --
140 ----------------------------------------------------------------------
141 -- ins_sit
142 --
143 -- Inserts a record into PER_SPECIAL_INFO_TYPES
144 ----------------------------------------------------------------------
145 --
146 procedure ins_sit (p_SPECIAL_INFORMATION_TYPE_ID in out nocopy NUMBER,
147 p_BUSINESS_GROUP_ID in NUMBER,
148 p_ID_FLEX_NUM in NUMBER,
149 p_COMMENTS in VARCHAR2,
150 p_ENABLED_FLAG in VARCHAR2,
151 p_REQUEST_ID in NUMBER,
152 p_PROGRAM_APPLICATION_ID in NUMBER,
153 p_PROGRAM_ID in NUMBER,
154 p_PROGRAM_UPDATE_DATE in DATE,
155 p_ATTRIBUTE_CATEGORY in VARCHAR2,
156 p_ATTRIBUTE1 in VARCHAR2,
157 p_ATTRIBUTE2 in VARCHAR2,
158 p_ATTRIBUTE3 in VARCHAR2,
159 p_ATTRIBUTE4 in VARCHAR2,
160 p_ATTRIBUTE5 in VARCHAR2,
161 p_ATTRIBUTE6 in VARCHAR2,
162 p_ATTRIBUTE7 in VARCHAR2,
163 p_ATTRIBUTE8 in VARCHAR2,
164 p_ATTRIBUTE9 in VARCHAR2,
165 p_ATTRIBUTE10 in VARCHAR2,
166 p_ATTRIBUTE11 in VARCHAR2,
167 p_ATTRIBUTE12 in VARCHAR2,
168 p_ATTRIBUTE13 in VARCHAR2,
169 p_ATTRIBUTE14 in VARCHAR2,
170 p_ATTRIBUTE15 in VARCHAR2,
171 p_ATTRIBUTE16 in VARCHAR2,
172 p_ATTRIBUTE17 in VARCHAR2,
173 p_ATTRIBUTE18 in VARCHAR2,
174 p_ATTRIBUTE19 in VARCHAR2,
175 p_ATTRIBUTE20 in VARCHAR2,
176 p_MULTIPLE_OCCURRENCES_FLAG in VARCHAR2) is
177 --
178 begin
179 --
180 check_unique_sit(p_special_information_type_id => null
181 ,p_bg_id => p_business_group_id
182 ,p_id_flex_num => p_id_flex_num);
183 --
184 p_special_information_type_id := get_special_info_type_id;
185 --
186 insert into per_special_info_types
187 (SPECIAL_INFORMATION_TYPE_ID,
188 BUSINESS_GROUP_ID,
189 ID_FLEX_NUM,
190 COMMENTS,
191 ENABLED_FLAG,
192 REQUEST_ID,
193 PROGRAM_APPLICATION_ID,
194 PROGRAM_ID,
195 PROGRAM_UPDATE_DATE,
196 ATTRIBUTE_CATEGORY,
197 ATTRIBUTE1,
198 ATTRIBUTE2,
199 ATTRIBUTE3,
200 ATTRIBUTE4,
201 ATTRIBUTE5,
202 ATTRIBUTE6,
203 ATTRIBUTE7,
204 ATTRIBUTE8,
205 ATTRIBUTE9,
206 ATTRIBUTE10,
207 ATTRIBUTE11,
208 ATTRIBUTE12,
209 ATTRIBUTE13,
210 ATTRIBUTE14,
211 ATTRIBUTE15,
212 ATTRIBUTE16,
213 ATTRIBUTE17,
214 ATTRIBUTE18,
215 ATTRIBUTE19,
216 ATTRIBUTE20,
217 MULTIPLE_OCCURRENCES_FLAG)
218 values
219 (p_SPECIAL_INFORMATION_TYPE_ID,
220 p_BUSINESS_GROUP_ID,
221 p_ID_FLEX_NUM,
222 p_COMMENTS,
223 p_ENABLED_FLAG,
224 p_REQUEST_ID,
225 p_PROGRAM_APPLICATION_ID,
226 p_PROGRAM_ID,
227 p_PROGRAM_UPDATE_DATE,
228 p_ATTRIBUTE_CATEGORY,
229 p_ATTRIBUTE1,
230 p_ATTRIBUTE2,
231 p_ATTRIBUTE3,
232 p_ATTRIBUTE4,
233 p_ATTRIBUTE5,
234 p_ATTRIBUTE6,
235 p_ATTRIBUTE7,
236 p_ATTRIBUTE8,
237 p_ATTRIBUTE9,
238 p_ATTRIBUTE10,
239 p_ATTRIBUTE11,
240 p_ATTRIBUTE12,
241 p_ATTRIBUTE13,
242 p_ATTRIBUTE14,
243 p_ATTRIBUTE15,
244 p_ATTRIBUTE16,
245 p_ATTRIBUTE17,
246 p_ATTRIBUTE18,
247 p_ATTRIBUTE19,
248 p_ATTRIBUTE20,
249 p_MULTIPLE_OCCURRENCES_FLAG);
250 --
251 end;
252
253 ----------------------------------------------------------------------
254 -- upd_sit
255 --
256 -- Updates a record into PER_SPECIAL_INFO_TYPES
257 ----------------------------------------------------------------------
258 --
259 procedure upd_sit (p_SPECIAL_INFORMATION_TYPE_ID in NUMBER,
260 p_BUSINESS_GROUP_ID in NUMBER,
261 p_ID_FLEX_NUM in NUMBER,
262 p_COMMENTS in VARCHAR2,
263 p_ENABLED_FLAG in VARCHAR2,
264 p_REQUEST_ID in NUMBER,
265 p_PROGRAM_APPLICATION_ID in NUMBER,
266 p_PROGRAM_ID in NUMBER,
267 p_PROGRAM_UPDATE_DATE in DATE,
268 p_ATTRIBUTE_CATEGORY in VARCHAR2,
269 p_ATTRIBUTE1 in VARCHAR2,
270 p_ATTRIBUTE2 in VARCHAR2,
271 p_ATTRIBUTE3 in VARCHAR2,
272 p_ATTRIBUTE4 in VARCHAR2,
273 p_ATTRIBUTE5 in VARCHAR2,
274 p_ATTRIBUTE6 in VARCHAR2,
275 p_ATTRIBUTE7 in VARCHAR2,
276 p_ATTRIBUTE8 in VARCHAR2,
277 p_ATTRIBUTE9 in VARCHAR2,
278 p_ATTRIBUTE10 in VARCHAR2,
279 p_ATTRIBUTE11 in VARCHAR2,
280 p_ATTRIBUTE12 in VARCHAR2,
281 p_ATTRIBUTE13 in VARCHAR2,
282 p_ATTRIBUTE14 in VARCHAR2,
283 p_ATTRIBUTE15 in VARCHAR2,
284 p_ATTRIBUTE16 in VARCHAR2,
285 p_ATTRIBUTE17 in VARCHAR2,
286 p_ATTRIBUTE18 in VARCHAR2,
287 p_ATTRIBUTE19 in VARCHAR2,
288 p_ATTRIBUTE20 in VARCHAR2,
289 p_MULTIPLE_OCCURRENCES_FLAG in VARCHAR2) is
290 --
291 begin
292 --
293 check_unique_sit
294 (p_special_information_type_id => p_special_information_type_id
295 ,p_bg_id => p_business_group_id
296 ,p_id_flex_num => p_id_flex_num);
297 --
298 update per_special_info_types
299 set BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID,
300 ID_FLEX_NUM = p_ID_FLEX_NUM,
301 COMMENTS = p_COMMENTS,
302 ENABLED_FLAG = p_ENABLED_FLAG,
303 REQUEST_ID = p_REQUEST_ID,
304 PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID,
305 PROGRAM_ID = p_PROGRAM_ID,
306 PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE,
307 ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY,
308 ATTRIBUTE1 = p_ATTRIBUTE1,
309 ATTRIBUTE2 = p_ATTRIBUTE2,
310 ATTRIBUTE3 = p_ATTRIBUTE3,
311 ATTRIBUTE4 = p_ATTRIBUTE4,
312 ATTRIBUTE5 = p_ATTRIBUTE5,
313 ATTRIBUTE6 = p_ATTRIBUTE6,
314 ATTRIBUTE7 = p_ATTRIBUTE7,
315 ATTRIBUTE8 = p_ATTRIBUTE8,
316 ATTRIBUTE9 = p_ATTRIBUTE9,
317 ATTRIBUTE10 = p_ATTRIBUTE10,
318 ATTRIBUTE11 = p_ATTRIBUTE11,
319 ATTRIBUTE12 = p_ATTRIBUTE12,
320 ATTRIBUTE13 = p_ATTRIBUTE13,
321 ATTRIBUTE14 = p_ATTRIBUTE14,
322 ATTRIBUTE15 = p_ATTRIBUTE15,
323 ATTRIBUTE16 = p_ATTRIBUTE16,
324 ATTRIBUTE17 = p_ATTRIBUTE17,
325 ATTRIBUTE18 = p_ATTRIBUTE18,
326 ATTRIBUTE19 = p_ATTRIBUTE19,
327 ATTRIBUTE20 = p_ATTRIBUTE20,
328 MULTIPLE_OCCURRENCES_FLAG = p_MULTIPLE_OCCURRENCES_FLAG
329 where SPECIAL_INFORMATION_TYPE_ID = p_special_information_type_id;
330 --
331 end;
332 --
333 ----------------------------------------------------------------------
334 -- lck
335 --
336 -- Locks a record into PER_SPECIAL_INFO_TYPES
337 ----------------------------------------------------------------------
338 --
339 Procedure lck (p_special_information_type_id in number) is
340 --
341 -- Cursor selects the 'current' row from the HR Schema
342 --
343 Cursor C_Sel1 is
344 select SPECIAL_INFORMATION_TYPE_ID
345 from per_special_info_types
346 where SPECIAL_INFORMATION_TYPE_ID = p_SPECIAL_INFORMATION_TYPE_ID
347 for update nowait;
348 --
349 l_dummy number;
350 Begin
351 --
352 Open C_Sel1;
353 Fetch C_Sel1 Into l_dummy;
354 If C_Sel1%notfound then
355 Close C_Sel1;
356 --
357 -- The primary key is invalid therefore we must error
358 --
359 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
360 hr_utility.raise_error;
361 End If;
362 Close C_Sel1;
363 --
364 -- We need to trap the ORA LOCK exception
365 --
366 Exception
367 When HR_Api.Object_Locked then
368 --
369 -- The object is locked therefore we need to supply a meaningful
370 -- error message.
371 --
372 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
373 hr_utility.set_message_token('TABLE_NAME', 'per_spcial_info_types');
374 hr_utility.raise_error;
375 End lck;
376 --
377 ----------------------------------------------------------------------
378 -- add_usage
379 --
380 -- Inserts a record into PER_SPECIAL_INFO_TYPE_USAGES for the
381 -- given type and category if the associated flag has been set
382 -- and the record does not already exist
383 ----------------------------------------------------------------------
384 --
385 procedure add_usage (p_special_information_type_id in number,
386 p_category_flag in varchar2,
387 p_special_info_category in varchar2) is
388 begin
389 --
390 if p_category_flag = 'Y' then
391 --
392 insert into per_special_info_type_usages
393 (special_information_type_id,
394 special_info_category)
395 select p_special_information_type_id,
396 p_special_info_category
397 from dual
398 where not exists
399 (select null
400 from per_special_info_type_usages
401 where special_information_type_id = p_special_information_type_id
402 and special_info_category = p_special_info_category);
403 --
404 end if;
405 --
406 end;
407
408 ----------------------------------------------------------------------
409 -- reset_usages
410 --
411 -- Removes any records in PER_SPECIAL_INFO_TYPE_USAGES which have
412 -- had their associated flag reset
413 -- Calls add_usage to insert any new usages for each category
414 --
415 ----------------------------------------------------------------------
416 --
417 procedure reset_usages (p_special_information_type_id in number,
418 p_job_category in varchar2,
419 p_position_category in varchar2,
420 p_skill_category in varchar2,
421 p_other_category in varchar2,
422 p_osha_category in varchar2,
423 p_ada_category in varchar2) is
424 --
425 begin
426 --
427 delete from per_special_info_type_usages
428 where special_information_type_id = p_special_information_type_id
429 and (
430 (special_info_category = G_JOB and p_job_category = 'N') or
431 (special_info_category = G_POSITION and p_position_category = 'N') or
432 (special_info_category = G_SKILL and p_skill_category = 'N') or
433 (special_info_category = G_OTHER and p_other_category = 'N') or
434 (special_info_category = G_OSHA and p_osha_category = 'N') or
435 (special_info_category = G_ADA and p_ada_category = 'N')
436 );
437 --
438 add_usage (p_special_information_type_id => p_special_information_type_id,
439 p_category_flag => p_job_category,
440 p_special_info_category => G_JOB);
441 --
442 add_usage (p_special_information_type_id => p_special_information_type_id,
443 p_category_flag => p_position_category,
444 p_special_info_category => G_POSITION);
445 --
446 add_usage (p_special_information_type_id => p_special_information_type_id,
447 p_category_flag => p_skill_category,
448 p_special_info_category => G_SKILL);
449 --
450 add_usage (p_special_information_type_id => p_special_information_type_id,
451 p_category_flag => p_other_category,
452 p_special_info_category => G_OTHER);
453 --
454 add_usage (p_special_information_type_id => p_special_information_type_id,
455 p_category_flag => p_osha_category,
456 p_special_info_category => G_OSHA);
457 --
458 add_usage (p_special_information_type_id => p_special_information_type_id,
459 p_category_flag => p_ada_category,
460 p_special_info_category => G_ADA);
461 --
462 --
463 end;
464
465 ----------------------------------------------------------------------
466 -- delete_usages
467 --
468 -- Deletes all records from PER_SPECIAL_INFO_TYPE_USAGES for a type
469 ----------------------------------------------------------------------
470 --
471 procedure delete_usages (p_special_information_type_id in number) is
472 begin
473 --
474 delete from per_special_info_type_usages
475 where special_information_type_id = p_special_information_type_id;
476 --
477 end;
478
479 ----------------------------------------------------------------------
480 -- del_sit
481 --
482 -- Deletes a record from PER_SPECIAL_INFO_TYPES
483 -- Calls delete_usages to also delete associated category usages
484 ----------------------------------------------------------------------
485 --
486 procedure del_sit (p_special_information_type_id in number) is
487 begin
488 --
489 delete_usages (p_special_information_type_id);
490 --
491 delete from per_special_info_types
492 where special_information_type_id = p_special_information_type_id;
493 --
494 end;
495
496 ----------------------------------------------------------------------
497 -- sit in use
498 --
499 -- checks if a special info types in a given category are in use
500 -- used in the check that a type can be reverted back to not being in a
501 -- category
502 --
503 ----------------------------------------------------------------------
504 --
505 function sit_in_use (p_business_group_id in number,
506 p_id_flex_num in number,
507 p_category in varchar2) return boolean is
508 --
509 cursor c_job_requirement is
510 select 'X'
511 from per_analysis_criteria ac
512 , per_job_requirements jr
513 where ac.id_flex_num = p_id_flex_num
514 and jr.analysis_criteria_id = ac.analysis_criteria_id
515 and jr.business_group_id = p_business_group_id --bug 3648683
516 and jr.job_id is not null;
517 --
518 cursor c_position_requirement is
519 select 'X'
520 from per_analysis_criteria ac
521 , per_job_requirements jr
522 where ac.id_flex_num = p_id_flex_num
523 and jr.analysis_criteria_id = ac.analysis_criteria_id
524 and jr.business_group_id = p_business_group_id --bug 3648683
525 and jr.position_id is not null;
526 --
527 cursor c_osha_analyses is
528 select 'X'
529 from per_person_analyses pa
530 where pa.id_flex_num = p_id_flex_num
531 and pa.business_group_id = p_business_group_id
532 and exists (select null
533 from pay_legislation_rules pl
534 where pl.rule_type = 'OSHA'
535 and pl.rule_mode = to_char(pa.id_flex_num));
536 --
537 cursor c_ada_analyses is
538 select 'X'
539 from per_person_analyses pa
540 where pa.id_flex_num = p_id_flex_num
541 and pa.business_group_id = p_business_group_id
542 and exists (select null
543 from pay_legislation_rules pl
544 where pl.rule_type in ('ADA_DIS_ACC','ADA_DIS')
545 and pl.rule_mode = to_char(pa.id_flex_num));
546 --
547 cursor c_other_analyses is
548 select 'X'
549 from per_person_analyses pa
550 where pa.id_flex_num = p_id_flex_num
551 and pa.business_group_id = p_business_group_id
552 and not exists (select null
553 from pay_legislation_rules pl
554 where pl.rule_type in ('OSHA','ADA_DIS_ACC','ADA_DIS')
555 and pl.rule_mode = to_char(pa.id_flex_num));
556 --
557 l_result boolean;
558 l_dummy varchar2(1);
559 --
560 begin
561 --
562 if p_category = G_JOB then
563 --
564 open c_job_requirement;
565 fetch c_job_requirement into l_dummy;
566 l_result := c_job_requirement%FOUND;
567 close c_job_requirement;
568 --
569 elsif p_category = G_POSITION then
570 --
571 open c_position_requirement;
572 fetch c_position_requirement into l_dummy;
573 l_result := c_position_requirement%FOUND;
574 close c_position_requirement;
575 --
576 elsif p_category = G_OSHA then
577 --
578 open c_osha_analyses;
579 fetch c_osha_analyses into l_dummy;
580 l_result := c_osha_analyses%FOUND;
581 close c_osha_analyses;
582 --
583 elsif p_category = G_ADA then
584 --
585 open c_ada_analyses;
586 fetch c_ada_analyses into l_dummy;
587 l_result := c_ada_analyses%FOUND;
588 close c_ada_analyses;
589 --
590 elsif p_category = G_OTHER then
591 --
592 open c_other_analyses;
593 fetch c_other_analyses into l_dummy;
594 l_result := c_other_analyses%FOUND;
595 close c_other_analyses;
596 --
597 else
598 --
599 l_result := false;
600 --
601 end if;
602 --
603 Return l_result;
604 --
605 end;
606
607 END PER_SIT_PKG;