1 PACKAGE BODY PQH_FR_UTILITY AS
2 /* $Header: pqfrutil.pkb 120.8 2008/06/27 06:06:11 rbabla noship $ */
3
4 -- This procedure is used to check is there any duplicate records, while
5 -- retrieving the records in mass update of employee assignment form
6 --
7 PROCEDURE DELETE_DUPLICATE_ASG_RECORDS (P_COPY_ENTITY_RESULT_ID in NUMBER
8 ,P_COPY_ENTITY_TXN_ID IN NUMBER
9 ,P_RESULT_TYPE_CD in VARCHAR2
10 ,P_INFORMATION2 IN VARCHAR2
11 ,P_INFORMATION67 IN VARCHAR2)
12 IS
13 -- Cursors
14 CURSOR CSR_CHK_DUP_REC IS
15 SELECT COUNT(*)
16 FROM PQH_COPY_ENTITY_RESULTS
17 WHERE COPY_ENTITY_TXN_ID = P_COPY_ENTITY_TXN_ID
18 AND INFORMATION2 = P_INFORMATION2
19 AND COPY_ENTITY_RESULT_ID <> P_COPY_ENTITY_RESULT_ID
20 AND RESULT_TYPE_CD = P_RESULT_TYPE_CD;
21
22 -- Mofied for Bug 6031763
23 --CURSOR CSR_GET_LEGISLATION IS
24 CURSOR CSR_GET_LEGISLATION(CSR_L_INFORMATION67 VARCHAR2) IS
25 SELECT ORG_INFORMATION9
26 FROM HR_ORGANIZATION_INFORMATION HOI,
27 PER_ALL_ASSIGNMENTS_F PAF
28 WHERE PAF.ASSIGNMENT_ID = P_INFORMATION2
29 AND FND_DATE.CANONICAL_TO_DATE(CSR_L_INFORMATION67) BETWEEN PAF.EFFECTIVE_START_DATE
30 AND PAF.EFFECTIVE_END_DATE
31
32 AND HOI.ORGANIZATION_ID = PAF.BUSINESS_GROUP_ID
33 AND UPPER(HOI.ORG_INFORMATION_CONTEXT) = 'BUSINESS GROUP INFORMATION';
34 --
35
36 CURSOR CSR_GET_CONTEXT IS
37 SELECT CONTEXT
38 FROM PQH_COPY_ENTITY_TXNS
39 WHERE COPY_ENTITY_TXN_ID = P_COPY_ENTITY_TXN_ID;
40
41 -- Local Variables
42 l_duplicate number;
43
44 l_legislation hr_organization_information.org_information9%type;
45
46 l_context pqh_copy_entity_txns.context%type;
47
48 -- Added for Bug 6031763
49 l_information67 varchar2(100);
50 --
51
52 BEGIN
53 --
54 /* Added for GSI Bug 5472781 */
55 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
56 hr_utility.set_location('Leaving : pqh_fr_utility.delete_duplicate_asg_records' , 5);
57 return;
58 END IF;
59 --
60 HR_UTILITY.SET_LOCATION('P_COPY_ENTITY_RESULT_ID'||P_COPY_ENTITY_RESULT_ID, 5);
61 HR_UTILITY.SET_LOCATION('P_COPY_ENTITY_TXN_ID'||P_COPY_ENTITY_TXN_ID, 5);
62 HR_UTILITY.SET_LOCATION('P_INFORMATION2'||P_INFORMATION2, 5);
63 HR_UTILITY.SET_LOCATION('P_INFORMATION67'||P_INFORMATION67, 5);
64 HR_UTILITY.SET_LOCATION('P_RESULT_TYPE_CD'||P_RESULT_TYPE_CD, 5);
65
66 open csr_get_context;
67 fetch csr_get_context into l_context;
68 if l_context = 'PQH_ASSIGNMENT_UPDATE' then
69
70 HR_UTILITY.SET_LOCATION('In pqh_assignment_update context', 20);
71 -- Added for Bug 6031763
72 if NOT(length(p_information67) > 11) then
73 l_information67 := fnd_date.date_to_canonical(fnd_date.string_to_date(p_information67,NVL(fnd_profile.value_specific('ICX_DATE_FORMAT_MASK'),'DD-MON-YYYY')));
74 else
75 l_information67 := p_information67;
76 end If;
77 --
78 -- Modified for Bug 6031763
79 --open csr_get_legislation;
80 open csr_get_legislation(l_information67);
81 --
82 fetch csr_get_legislation into l_legislation;
83 if csr_get_legislation%found then
84
85 HR_UTILITY.SET_LOCATION('In Legislation', 10);
86 if l_legislation = 'FR' then
87
88 HR_UTILITY.SET_LOCATION('In France Legislation', 15);
89 open csr_chk_dup_rec;
90 fetch csr_chk_dup_rec into l_duplicate;
91 if l_duplicate > 0 then
92
93 hr_utility.set_location('Has Duplicate Records', 25);
94 fnd_message.set_name('PER', 'HR_FR_DUP_ORG_MASS_UPD');
95 fnd_message.raise_error;
96 end if;
97 close csr_chk_dup_rec;
98 end if; -- no FR legislation
99 end if; -- legislation doesn't exists
100 close csr_get_legislation;
101 end if; -- context different
102 close csr_get_context;
103
104 HR_UTILITY.SET_LOCATION('l_duplicate'||l_duplicate, 25);
105
106 END DELETE_DUPLICATE_ASG_RECORDS;
107
108 FUNCTION get_kf_id_flex_num(p_id_flex_code Varchar2,
109 p_structure_code VARCHAR2) RETURN NUMBER IS
110 Cursor C_Id_Flex IS
111 SELECT id_flex_num
112 FROM fnd_id_flex_structures
113 WHERE id_flex_code = p_id_flex_code
114 AND id_flex_structure_code = p_structure_code;
115
116 l_id_flex_num fnd_id_flex_structures.id_flex_num%TYPE;
117 BEGIN
118 OPEN C_Id_Flex;
119 FETCH C_Id_Flex INTO l_id_flex_num;
120 IF C_Id_Flex%NOTFOUND THEN
121 l_id_flex_num := TO_NUMBER(NULL);
122 END IF;
123 CLOSE C_Id_Flex;
124
125 RETURN l_id_flex_num;
126
127 END get_kf_id_flex_num;
128
129 FUNCTION Get_Award_Type(p_person_id Number,
130 p_award_category Varchar2,
131 p_award_type Varchar2)
132 RETURN VARCHAR2 IS
133
134 l_id_flex_num NUMBER(15);
135
136 Cursor C_national_award_date IS
137 SELECT MAX(fnd_date.canonical_to_date(pea.segment5))
138 FROM per_analysis_criteria pea,
139 per_person_analyses ppa
140 WHERE ppa.person_id = p_person_id
141 AND pea.id_flex_num = l_id_flex_num
142 AND pea.segment1 = p_award_type
143 AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
144
145 Cursor C_ministry_award_date IS
146 SELECT MAX(fnd_date.canonical_to_date(pea.segment7))
147 FROM per_analysis_criteria pea,
148 per_person_analyses ppa
149 WHERE ppa.person_id = p_person_id
150 AND pea.id_flex_num = l_id_flex_num
151 AND pea.segment1 = p_award_type
152 AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
153
154 l_max_date DATE;
155
156
157 -- Selects the most recent award (award type) for a given person, for National award
158 CURSOR C_national_award_type IS
159 SELECT pea.segment1
160 FROM per_analysis_criteria pea,
161 per_person_analyses ppa
162 WHERE ppa.person_id = p_person_id
163 AND pea.id_flex_num = l_id_flex_num
164 AND pea.analysis_criteria_id = ppa.analysis_criteria_id
165 AND pea.segment1 = p_award_type
166 AND fnd_date.canonical_to_date(pea.segment5) = l_max_date;
167
168 -- Selects the most recent award (award type) for a given person, for Ministry award
169 CURSOR C_ministry_award_type IS
170 SELECT pea.segment1
171 FROM per_analysis_criteria pea,
172 per_person_analyses ppa
173 WHERE ppa.person_id = p_person_id
174 AND pea.id_flex_num = l_id_flex_num
175 AND pea.analysis_criteria_id = ppa.analysis_criteria_id
176 AND pea.segment1 = p_award_type
177 AND fnd_date.canonical_to_date(pea.segment7) = l_max_date;
178
179 l_award_type hr_lookups.lookup_code%TYPE;
180
181 BEGIN
182 IF p_award_category = 'NATIONAL' THEN
183 l_id_flex_num := get_kf_id_flex_num('PEA','FR_PQH_NATIONAL_AWARD');
184 IF l_id_flex_num IS NULL THEN
185 RETURN TO_CHAR(NULL);
186 END IF;
187 OPEN C_national_award_date;
188 FETCH C_national_award_date INTO l_max_date;
189 CLOSE C_national_award_date;
190 IF l_max_date IS NULL THEN
191 RETURN TO_CHAR(NULL);
192 ELSE
193 OPEN C_national_award_type;
194 FETCH C_national_award_type INTO l_award_type;
195 CLOSE C_national_award_type;
196 RETURN l_award_type;
197 END IF;
198 ELSIF p_award_category = 'MINISTRY' THEN
199 l_id_flex_num := get_kf_id_flex_num('PEA','FR_PQH_MINISTRY_AWARDS');
200 IF l_id_flex_num IS NULL THEN
201 RETURN TO_CHAR(NULL);
202 END IF;
203 OPEN C_ministry_award_date;
204 FETCH C_ministry_award_date INTO l_max_date;
205 CLOSE C_ministry_award_date;
206 IF l_max_date IS NULL THEN
207 RETURN TO_CHAR(NULL);
208 ELSE
209 OPEN C_ministry_award_type;
210 FETCH C_ministry_award_type INTO l_award_type;
211 CLOSE C_ministry_award_type;
212 RETURN l_award_type;
213 END IF;
214 END IF;
215
216 END Get_Award_Type;
217
218 FUNCTION Get_Award_Grade_Level(p_person_id Number,
219 p_award_category Varchar2,
220 p_award_type Varchar2)
221 RETURN VARCHAR2 IS
222
223 l_id_flex_num NUMBER(15);
224
225 Cursor C_national_award_date IS
226 SELECT MAX(fnd_date.canonical_to_date(pea.segment5))
227 FROM per_analysis_criteria pea,
228 per_person_analyses ppa
229 WHERE ppa.person_id = p_person_id
230 AND pea.id_flex_num = l_id_flex_num
231 AND pea.segment1 = p_award_type
232 AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
233
234 Cursor C_ministry_award_date IS
235 SELECT MAX(fnd_date.canonical_to_date(pea.segment7))
236 FROM per_analysis_criteria pea,
237 per_person_analyses ppa
238 WHERE ppa.person_id = p_person_id
239 AND pea.id_flex_num = l_id_flex_num
240 AND pea.segment1 = p_award_type
241 AND pea.analysis_criteria_id = ppa.analysis_criteria_id;
242
243 l_max_date DATE;
244
245
246 -- Selects the most recent award (award Grade) for a given person, for National award
247 CURSOR C_national_award_grade IS
248 SELECT pea.segment2
249 FROM per_analysis_criteria pea,
250 per_person_analyses ppa
251 WHERE ppa.person_id = p_person_id
252 AND pea.id_flex_num = l_id_flex_num
253 AND pea.analysis_criteria_id = ppa.analysis_criteria_id
254 AND pea.segment1 = p_award_type
255 AND fnd_date.canonical_to_date(pea.segment5) = l_max_date;
256
257 -- Selects the most recent award (award level) for a given person, for Ministry award
258 CURSOR C_ministry_award_level IS
259 SELECT pea.segment4
260 FROM per_analysis_criteria pea,
261 per_person_analyses ppa
262 WHERE ppa.person_id = p_person_id
263 AND pea.id_flex_num = l_id_flex_num
264 AND pea.segment1 = p_award_type
265 AND pea.analysis_criteria_id = ppa.analysis_criteria_id
266 AND fnd_date.canonical_to_date(pea.segment7) = l_max_date;
267
268 l_award_type hr_lookups.lookup_code%TYPE;
269
270 BEGIN
271 IF p_award_category = 'NATIONAL' THEN
272 l_id_flex_num := get_kf_id_flex_num('PEA','FR_PQH_NATIONAL_AWARD');
273 IF l_id_flex_num IS NULL THEN
274 RETURN TO_CHAR(NULL);
275 END IF;
276 OPEN C_national_award_date;
277 FETCH C_national_award_date INTO l_max_date;
278 CLOSE C_national_award_date;
279 IF l_max_date IS NULL THEN
280 RETURN TO_CHAR(NULL);
281 ELSE
282 OPEN C_national_award_grade;
283 FETCH C_national_award_grade INTO l_award_type;
284 CLOSE C_national_award_grade;
285 RETURN l_award_type;
286 END IF;
287 ELSIF p_award_category = 'MINISTRY' THEN
288 l_id_flex_num := get_kf_id_flex_num('PEA','FR_PQH_MINISTRY_AWARDS');
289 IF l_id_flex_num IS NULL THEN
290 RETURN TO_CHAR(NULL);
291 END IF;
292 OPEN C_ministry_award_date;
293 FETCH C_ministry_award_date INTO l_max_date;
294 CLOSE C_ministry_award_date;
295 IF l_max_date IS NULL THEN
296 RETURN TO_CHAR(NULL);
297 ELSE
298 OPEN C_ministry_award_level;
299 FETCH C_ministry_award_level INTO l_award_type;
300 CLOSE C_ministry_award_level;
301 RETURN l_award_type;
302 END IF;
303 END IF;
304
305 END Get_Award_Grade_Level;
306
307 FUNCTION Get_Entitlement_Item(p_business_group_id NUMBER,
308 p_item_type Varchar2) RETURN NUMBER IS
309
310 CURSOR C_Acco_Item IS
311 SELECT information2
312 FROM per_shared_types
313 WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
314 AND system_type_cd = 'ACCOMMODATION'
315 AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
316
317 CURSOR C_Ministry_Item IS
318 SELECT information2
319 FROM per_shared_types
320 WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
321 AND system_type_cd = 'MINISTRY_AWARD'
322 AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
323
324 CURSOR C_National_Item IS
325 SELECT information2
326 FROM per_shared_types
327 WHERE lookup_type = 'FR_PQH_ENTITLEMENT_SETUP'
328 AND system_type_cd = 'NATIONAL_AWARD'
329 AND (business_group_id = p_business_group_id OR business_group_id IS NULL);
330 l_entitlement_item NUMBER(15);
331 BEGIN
332 IF p_item_type = 'ACCOMMODATION' THEN
333 OPEN C_Acco_Item;
334 FETCH C_Acco_Item INTO l_entitlement_item;
335 CLOSE C_Acco_Item;
336 ELSIF p_item_type = 'MINISTRY_AWARD' THEN
337 OPEN C_Ministry_Item;
338 FETCH C_Ministry_Item INTO l_entitlement_item;
339 CLOSE C_Ministry_Item;
340 ELSIF p_item_type = 'NATIONAL_AWARD' THEN
341 OPEN C_National_Item;
342 FETCH C_National_Item INTO l_entitlement_item;
343 CLOSE C_National_Item;
344 END IF;
345
346 Return l_entitlement_item;
347
348 END Get_Entitlement_Item;
349
350 FUNCTION Check_PS_Installed (p_business_group_id NUMBER)
351 RETURN VARCHAR2 IS
352 BEGIN
353 IF pqh_utility.is_pqh_installed(p_business_group_id) = TRUE THEN
354 RETURN 'Y';
355 ELSE
356 RETURN 'N';
357 END IF;
358 END Check_PS_Installed;
359
360 PROCEDURE Get_DateTrack_Mode(p_effective_date IN DATE,
361 p_base_table_name IN Varchar2,
362 p_base_key_column IN Varchar2,
363 p_base_key_value IN Number,
364 p_datetrack_mode OUT NOCOPY VARCHAR2) IS
365 l_correction BOOLEAN;
366 l_update BOOLEAN;
367 l_update_override BOOLEAN;
368 l_update_change_insert BOOLEAN;
369
370 BEGIN
371 DT_API.FIND_DT_UPD_MODES(p_effective_date => TRUNC(p_effective_date),
372 p_base_table_name => p_base_table_name,
373 p_base_key_column => p_base_key_column,
374 p_base_key_value => p_base_key_value,
375 p_correction => l_correction,
376 p_update => l_update,
377 p_update_override => l_update_override,
378 p_update_change_insert =>l_update_change_insert);
379
380 IF l_update_change_insert = TRUE THEN
381 p_datetrack_mode := 'UPDATE_CHANGE_INSERT';
382 ELSIF l_update = TRUE THEN
383 p_datetrack_mode := 'UPDATE';
384 ELSE
385 p_datetrack_mode := 'CORRECTION';
386 END IF;
387 END Get_DateTrack_Mode;
388 --
389 FUNCTION Get_Accommodation_status (p_accommodation_id IN NUMBER,
390 p_effective_date IN DATE) RETURN VARCHAR2
391 IS
392 Cursor csr_assignment_exist_for_acco IS
393 SELECT 'Y'
394 FROM DUAL
395 WHERE EXISTS( Select NULL
396 from pqh_assign_accommodations_f
397 where accommodation_id = p_accommodation_id
398 and trunc(p_effective_date) between effective_start_date and effective_end_date
399 and accommodation_given ='Y');
400
401 --
402 l_var varchar2(10) := 'N';
403 Begin
404 --
405 Open csr_assignment_exist_for_acco;
406
407 Fetch csr_assignment_exist_for_acco into l_var;
408
409 Close csr_assignment_exist_for_acco;
410
411 If l_var ='Y' then -- Accommodation is Occupied
412 return hr_general.decode_lookup('PQH_ACCO_STATUS','03') ;
413 else -- Accommodation is Available
414 return hr_general.decode_lookup('PQH_ACCO_STATUS','02');
415 End If;
416 --
417 End Get_Accommodation_status;
418 --
419 FUNCTION get_lookup_shared_type( p_lookup_type VARCHAR2, p_lookup_code VARCHAR2,
420 p_business_group_id NUMBER, p_return_value VARCHAR2) RETURN VARCHAR2
421 is
422
423 Cursor csr_glb_shared_types Is
424 Select shared_type_id, shared_type_name
425 From per_shared_types_vl
426 Where lookup_type = p_lookup_type
427 And system_type_cd = p_lookup_code
428 And business_group_id is null;
429
430 Cursor csr_bg_shared_types is
431 Select shared_type_id, shared_type_name
432 From per_shared_types_vl
433 Where lookup_type = p_lookup_type
434 And system_type_cd = p_lookup_code
435 And business_group_id = p_business_group_id;
436
437 l_bg_return csr_bg_shared_types%ROWTYPE;
438 l_glb_return csr_glb_shared_types%ROWTYPE;
439
440 Begin
441 Open csr_bg_shared_types;
442 Fetch csr_bg_shared_types into l_bg_return;
443 IF csr_bg_shared_types%FOUND THEN
444 Close csr_bg_shared_types;
445 If (p_return_value = 'ID') then
446 Return l_bg_return.shared_type_id;
447 Else
448 Return l_bg_return.shared_type_name;
449 End if;
450 END IF;
451
452 Open csr_glb_shared_types;
453 Fetch csr_glb_shared_types into l_glb_return;
454 Close csr_glb_shared_types;
455 If (p_return_value = 'ID') then
456 Return l_glb_return.shared_type_id;
457 Else
458 Return l_glb_return.shared_type_name;
459 End if;
460 END get_lookup_shared_type;
461 --
462 -- Admin Career Validations
463
464
465 procedure admin_effective_warning( p_person_id in number ,p_effective_date in varchar2,p_return_status out NOCOPY varchar) IS
466
467 Cursor csr_suggest_eff_dt Is
468 Select max(effective_start_date)
469 from per_all_assignments_f
470 where person_id = p_person_id
471 and primary_flag ='Y';
472 --
473 l_date date;
474 Begin
475
476 hr_multi_message.enable_message_list;
477
478 Open csr_suggest_eff_dt;
479 Fetch csr_suggest_eff_dt into l_date;
480 Close csr_suggest_eff_dt;
481
482
483 fnd_message.set_name('PQH','PQH_FR_DATE_SUGGESTION');
484 fnd_message.set_token('DATE',l_date);
485
486 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_INFORMATION_MSG);
487
488 HR_MULTI_MESSAGE.end_validation_set;
489
490
491 End admin_effective_warning;
492 ---
493 procedure employment_terms_validations (p_person_id in number, p_effective_date in varchar2)
494 IS
495 Cursor csr_chk_person IS
496 Select PER_INFORMATION15
497 from per_all_people_f
498 where person_id = p_person_id
499 and to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date;
500 --
501 Cursor csr_chk_career IS
502 Select grade_ladder_pgm_id
503 from per_all_assignments_f
504 where person_id = p_person_id
505 and primary_flag ='Y'
506 and to_date(p_effective_date, 'RRRR-MM-DD') between effective_start_date and effective_end_date;
507
508
509
510
511 l_temp varchar2(10);
512 Begin
513
514 Open csr_chk_person;
515 Fetch csr_chk_person into l_temp;
516
517 If csr_chk_person%notfound then
518 --
519 -- There is no record as of effective date
520 --
521 fnd_message.set_name('PQH','PQH_FR_NO_PERSON_EXIST');
522 fnd_message.set_token('DATE',to_date(p_effective_date,'RRRR-MM-DD'));
523 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_ERROR_MSG);
524 --
525 End if;
526 Close csr_chk_person;
527
528 If ( l_temp = '01') then -- Fonctionnaire
529 --
530 Open csr_chk_career;
531 Fetch csr_chk_career into l_temp;
532 If (l_temp is null) then -- Career is Not definied
533 -- No Career Exist as on DATE
534 --
535 fnd_message.set_name('PQH','PQH_FR_NO_CAREER');
536 fnd_message.set_token('DATE',to_date(p_effective_date,'RRRR-MM-DD'));
537 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_ERROR_MSG);
538 --
539 End if;
540 Close csr_chk_career;
541 --
542 End if;
543
544
545
546
547 End employment_terms_validations;
548 --
549
550 procedure admin_career_validations(p_person_id in number, p_effective_date in varchar2)
551 IS
552
553 Cursor csr_chk_person IS
554 Select null
555 from per_all_people_f
556 where person_id = p_person_id
557 and to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date;
558
559 l_temp varchar2(10);
560
561 Begin
562 --
563
564 Open csr_chk_person;
565 Fetch csr_chk_person into l_temp;
566
567 If csr_chk_person%notfound then
568 --
569 -- There is no record as of effective date
570 --
571 fnd_message.set_name('PQH','PQH_FR_NO_PERSON_EXIST');
572 fnd_message.set_token('DATE',to_date(p_effective_date,'RRRR-MM-DD'));
573 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_ERROR_MSG);
574 --
575 End if;
576 Close csr_chk_person;
577
578
579 ---
580 End admin_career_validations;
581 --
582 procedure affectations_validations(p_person_id in number,p_effective_date in varchar2)
583 IS
584 Cursor csr_normal_hours IS
585 Select normal_hours
586 from per_all_assignments_f
587 where to_date(p_effective_date,'RRRR-MM-DD') between effective_start_date and effective_end_date
588 and person_id = p_person_id
589 and primary_flag ='Y';
590
591 l_normal_hours per_all_assignments_f.normal_hours%type;
592
593 Begin
594 --
595 admin_career_validations(p_person_id,p_effective_date);
596 /*
597 Check the Normal Hours are defined for the Person or Not ...
598 */
599 Open csr_normal_hours;
600 Fetch csr_normal_hours into l_normal_hours;
601 If (l_normal_hours is null ) then
602 ---
603 fnd_message.set_name('PQH','PQH_FR_NO_NORMAL_HOURS');
604 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_ERROR_MSG);
605 --
606 End if;
607
608 Close csr_normal_hours;
609
610 employment_terms_validations(p_person_id,p_effective_date);
611 --
612 End affectations_validations;
613 --
614 procedure hr_actions_validate_person (p_person_id in number, p_return_status out NOCOPY varchar2,
615 p_effective_date in varchar2, p_function_name in Varchar2)
616 IS
617
618 Begin
619
620 hr_multi_message.enable_message_list;
621
622 -- Administratvie Career Check
623 If (p_function_name = 'PQH_FR_HR_ADMIN_CAREER') Then
624 --
625 admin_career_validations(p_person_id, p_effective_date);
626 --
627 ElsIf (p_function_name = 'PQH_FR_HR_ADMIN_EMPL_TERMS') then
628 --
629 employment_terms_validations(p_person_id, p_effective_date);
630 --
631 ElsIf (p_function_name = 'PQH_FR_HR_ADMIN_AFFECTATIONS_H') Then
632 --
633 affectations_validations(p_person_id,p_effective_date);
634 --
635 End if;
636
637 HR_MULTI_MESSAGE.end_validation_set;
638 EXCEPTION
639 when hr_multi_message.error_message_exist then
640 -- P_RETURN_STATUS := hr_multi_message.get_return_status;
641 null;
642 End;
643
644
645 -- Get_DateTrack_Mode Function
646 Function Get_DateTrack_Mode ( p_effective_date IN DATE,
647 p_base_table_name IN VARCHAR2,
648 p_base_key_column IN VARCHAR2,
649 p_base_key_value IN NUMBER) Return varchar2
650 Is
651 l_DateTrack_Mode varchar2(100);
652 begin
653 Get_DateTrack_mode(p_effective_date => p_effective_date
654 ,p_base_table_name => p_base_table_name
655 ,p_base_key_column => p_base_key_column
656 ,p_base_key_value => p_base_key_value
657 ,p_datetrack_mode => l_dateTrack_mode);
658
659 Return l_dateTrack_Mode;
660
661 End get_DateTrack_mode; --Function
662
663 --
664 Function Get_available_hours(p_person_id IN NUMBER, p_effective_date in date) return number
665 IS
666 Cursor csr_available_hours(p_flag varchar2) IS
667 Select sum(normal_hours)
668 from per_all_assignments_f
669 where person_id = p_person_id
670 and p_effective_date between effective_start_date and effective_end_date
671 and assignment_status_type_id = 1
672 and primary_flag = p_flag;
673
674 --
675 l_normal_hours per_all_assignments_f.normal_hours%type;
676 l_consumed_hours per_all_assignments_f.normal_hours%type;
677 --
678 Begin
679 --
680 Open csr_available_hours('N');
681 Fetch csr_available_hours into l_consumed_hours;
682 Close csr_available_hours;
683
684 Open csr_available_hours('Y');
685 Fetch csr_available_hours into l_normal_hours;
686 Close csr_available_hours;
687
688 Return l_normal_hours - nvl(l_consumed_hours,0);
689 --
690 End Get_available_hours;
691
692 --
693 FUNCTION get_salary_share (p_shard_type_cd IN VARCHAR2)
694 RETURN VARCHAR2
695 IS
696 CURSOR cur_bg_salary_share
697 IS
698 SELECT information1
699 FROM per_shared_types
700 WHERE lookup_type = 'FR_PQH_PHYSICAL_SHARE'
701 AND business_group_id = hr_general.get_business_group_id
702 AND system_type_cd = p_shard_type_cd;
703
704 CURSOR global_salary_share
705 IS
706 SELECT information1
707 FROM per_shared_types
708 WHERE lookup_type = 'FR_PQH_PHYSICAL_SHARE'
709 AND business_group_id IS NULL
710 AND system_type_cd = p_shard_type_cd;
711
712 l_salary_share per_shared_types.information1%TYPE;
713 BEGIN
714 OPEN cur_bg_salary_share;
715
716 FETCH cur_bg_salary_share
717 INTO l_salary_share;
718
719 IF cur_bg_salary_share%FOUND
720 THEN
721 CLOSE cur_bg_salary_share;
722
723 RETURN l_salary_share;
724 END IF;
725
726 CLOSE cur_bg_salary_share;
727
728 OPEN global_salary_share;
729
730 FETCH global_salary_share
731 INTO l_salary_share;
732
733 CLOSE global_salary_share;
734
735 RETURN l_salary_share;
736 END get_salary_share;
737 --
738 Function Get_contract_reference(p_contract_id in Number, p_effective_date in Date) return varchar2
739 IS
740 Cursor csr_contract_ref IS
741 Select Reference
742 from per_contracts_f
743 where contract_id = p_contract_id
744 and p_effective_date between effective_start_date and effective_end_date;
745 --
746 l_contract_reference varchar2(240) := null;
747 Begin
748
749 Open csr_contract_ref;
750 Fetch csr_contract_ref into l_contract_reference;
751 Close Csr_contract_ref;
752
753 Return l_contract_reference;
754
755 End;
756 --
757 Function is_worker_employee(p_person_id in number, p_effective_date in date) return boolean
758 IS
759
760 Begin
761
762 return hr_person_type_usage_info.is_person_of_type
763 (p_effective_date => p_effective_date
764 ,p_person_id => p_person_id
765 ,p_system_person_type => 'EMP');
766
767 End is_worker_employee;
768
769 Function is_worker_CWK(p_person_id in number, p_effective_date in date) return boolean
770 IS
771 Begin
772
773 return hr_person_type_usage_info.is_person_of_type
774 (p_effective_date => p_effective_date
775 ,p_person_id => p_person_id
776 ,p_system_person_type => 'CWK');
777
778 End is_worker_CWK;
779 --
780 PROCEDURE Default_Employment_Terms(p_person_id IN NUMBER,
781 p_emp_type IN VARCHAR2) IS
782 l_agent_type Varchar2(30);
783 l_emp_type Varchar2(30);
784 CURSOR csr_primary_asg(p_person_id IN NUMBER) IS
785 SELECT assignment_id, object_version_number
786 FROM per_all_assignments_f
787 WHERE person_id = p_person_id
788 AND hr_general.effective_date between effective_start_date and effective_end_date
789 AND primary_flag = 'Y';
790 l_asg_id NUMBER(15);
791 l_asg_ovn NUMBER(9);
792 l_scl_id NUMBER(15);
793 CURSOR csr_bg_hours(p_bg_id IN NUMBER) IS
794 SELECT working_hours, frequency
795 FROM per_business_groups
796 WHERE business_group_id = p_bg_id;
797 l_bg_hours csr_bg_hours%ROWTYPE;
798 l_cagr_grade_def_id NUMBER(15);
799 l_cagr_grade_segments Varchar2(2000);
800 l_conc_segments varchar2(2000);
801 l_comment_id NUMBER(15);
802 l_esd DATE;
803 l_eed DATE;
804 l_no_mgrs boolean;
805 l_other_mgrs BOOLEAN;
806 l_hourly BOOLEAN;
807 l_gsp_warn varchar2(2000);
808 --
809 --
810 Cursor csr_situation_details IS
811 Select statutory_situation_id
812 from pqh_fr_stat_situations_v sit , per_shared_types_vl sh
813 where sh.shared_type_id = type_of_ps
814 and sh.system_type_cd = nvl(pqh_fr_utility.GET_BG_TYPE_OF_PS,sh.system_type_cd)
815 and sit.business_group_id = hr_general.get_business_group_id
816 and sit.default_flag = 'Y'
817 and sit.situation_type = 'IA'
818 and sit.sub_type = 'IA_N'
819 and trunc(sysdate) between date_from and nvl(date_to,hr_general.end_of_time);
820 --
821 Cursor csr_person_details IS
822 Select per.per_information15, pps.orig_hire_dt
823 from per_all_people_f per,
824 (SELECT min(PPS1.DATE_START) orig_hire_dt
825 FROM PER_PERIODS_OF_SERVICE PPS1
826 WHERE pps1.person_id = p_person_id) pps
827 where per.person_id =p_person_id
828 and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
829
830 /* --commented by deenath and replaced by above cursor
831 Cursor csr_person_details IS
832 Select per_information15,original_date_of_hire
833 from per_all_people_f
834 where person_id =p_person_id
835 and trunc(sysdate) between effective_start_date and effective_end_date;
836 */
837
838
839 l_object_version_number number(9);
840 l_emp_stat_situation_id number(15);
841 l_statutory_situation_id number(15);
842 l_date_of_hire date;
843
844 BEGIN
845 OPEN csr_primary_asg(p_person_id);
846 FETCH csr_primary_asg INTO l_asg_id, l_asg_ovn;
847 CLOSE csr_primary_asg;
848
849 OPEN csr_bg_hours(hr_general.get_business_group_id);
850 FETCH csr_bg_hours INTO l_bg_hours.working_hours, l_bg_hours.frequency;
851 CLOSE csr_bg_hours;
852
853 HR_ASSIGNMENT_API.update_emp_asg(
854 p_effective_date => hr_general.effective_date
855 ,p_datetrack_update_mode => 'CORRECTION'
856 ,p_assignment_id => l_asg_id
857 ,p_object_version_number => l_asg_ovn
858 ,p_normal_hours => fnd_number.canonical_to_number(l_bg_hours.working_hours)
859 ,p_frequency => l_bg_hours.frequency
860 ,p_segment9 => '100'
861 ,p_segment2 => p_emp_type
862 ,p_soft_coding_keyflex_id => l_scl_id
863 ,p_cagr_grade_def_id => l_cagr_grade_def_id
864 ,p_cagr_concatenated_segments => l_cagr_grade_segments
865 ,p_concatenated_segments => l_conc_segments
866 ,p_comment_id => l_comment_id
867 ,p_effective_start_date => l_esd
868 ,p_effective_end_date => l_eed
869 ,p_no_managers_warning => l_no_mgrs
870 ,p_other_manager_warning => l_other_mgrs
871 ,p_hourly_salaried_warning => l_hourly
872 ,p_gsp_post_process_warning => l_gsp_warn);
873
874 Open csr_person_details;
875 Fetch csr_person_details into l_agent_type,l_date_of_hire;
876 Close csr_person_details;
877
878 If (l_agent_type = '01') Then
879 --- Only For Fonctionnaire
880 Open csr_situation_details;
881 Fetch csr_situation_details into l_statutory_situation_id;
882 --
883 If csr_situation_details%NOTFOUND then
884 --
885 fnd_message.set_name('PQH','PQH_FR_NO_DEFAULT_SITUATION');
886 hr_multi_message.add(p_message_type=>HR_MULTI_MESSAGE.G_ERROR_MSG);
887 --
888 End if;
889
890 Close csr_situation_details;
891
892
893 pqh_psu_ins.ins
894 (
895 p_effective_date => trunc(sysdate)
896 ,P_STATUTORY_SITUATION_ID => l_statutory_situation_id
897 ,P_PERSON_ID => p_person_id
898 ,P_PROVISIONAL_START_DATE => l_date_of_hire
899 ,P_PROVISIONAL_END_DATE => hr_general.end_of_time
900 ,P_APPROVAL_FLAG => 'Y'
901 ,P_ACTUAL_START_DATE => l_date_of_hire
902 ,P_EMP_STAT_SITUATION_ID => l_emp_stat_situation_id
903 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
904 );
905
906
907 ---
908 End If;
909
910
911 EXCEPTION
912 WHEN Others Then
913 Raise;
914 END;
915 --
916 Function is_action_valid(p_function_name IN varchar2, p_person_id in Number,p_effective_date in Date)
917 return varchar2
918 IS
919 l_return_value varchar2(10) := 'Y';
920 Begin
921 --
922 /* Its is a custom function, will be used in hrAdminActions/server/ActionsVO
923 and this function in default return Y, and input parameters are function_name (Menu function
924 name and person_id . Using this function developer can restrict any menu function to be restricted
925 As per the FR requirments, for Agent Type = Fonctionnaire and Person Type is CWK worker then
926 he/she will not have career definition.
927 */
928 If p_function_name = 'PQH_FR_HR_ADMIN_CAREER' Then
929 --
930 If is_worker_CWK(p_person_id,p_effective_date) Then
931 --
932 l_return_value := 'N';
933 --
934 End if;
935 --
936 End if;
937
938 return l_return_value;
939 --
940 End is_action_valid;
941 ---
942 Function get_position_name (p_admin_career_id in Varchar2, p_effective_date in Date) return varchar2
943 IS
944
945 Begin
946
947 return hr_general.decode_position_latest_name(get_position_id(p_admin_career_id,p_effective_date));
948
949 End get_position_name;
950
951 Function get_position_id (p_admin_career_id in Varchar2, p_effective_date in Date) return number
952 IS
953 Cursor csr_position_id IS
954 Select position_id
955 from per_all_assignments_f
956 where assignment_id = p_admin_career_id
957 and p_effective_date between effective_start_date and effective_end_date;
958 --
959 l_position_id Number;
960
961 Begin
962
963 Open csr_position_id;
964 Fetch csr_position_id into l_position_id;
965 Close csr_position_id;
966
967 Return l_position_id;
968 End get_position_id;
969
970
971
972 FUNCTION GET_STEP_RATE (p_step_id IN NUMBER, p_effective_date IN DATE, p_gl_currency IN VARCHAR2) RETURN NUMBER IS
973 Cursor csr_step_dtls(p_assignment_id IN NUMBER, p_effective_date IN DATE) IS
974 SELECT psp.information1 "SCALE_TYPE",
975 ssp.information1 "GROSS_INDEX",
976 ssp.information2 "SALARY_RATE"
977 FROM per_spinal_point_steps_f sps,
978 per_parent_spines psp,
979 per_spinal_points ssp
980 WHERE sps.step_id = p_step_id
981 AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date
982 AND sps.spinal_point_id = ssp.spinal_point_id
983 AND psp.parent_spine_id = ssp.parent_spine_id;
984
985 l_step_dtls csr_step_dtls%ROWTYPE;
986
987 l_inm number(15);
988
989
990 l_conv_factor number(22,5) := 1.0;
991
992 l_step_rate Number(33,5) := 0;
993
994 CURSOR csr_sal_rate IS
995 SELECT basic_salary_rate,
996 currency_code
997 FROM pqh_fr_global_indices_f
998 WHERE type_of_record = 'INM'
999 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1000 l_bareme_currency varchar2(30);
1001 l_bareme_salary_rate number(22,5);
1002 BEGIN
1003
1004 OPEN csr_step_dtls(p_step_id,p_effective_date);
1005 FETCH csr_step_dtls INTO l_step_dtls;
1006 CLOSE csr_step_dtls;
1007
1008 IF l_step_dtls.scale_type = 'L' THEN -- for legislative scale
1009 l_inm := pqh_corps_utility.get_increased_index(l_step_dtls.gross_index,p_effective_date);
1010 OPEN csr_sal_rate;
1011 FETCH csr_sal_rate INTO l_bareme_salary_rate,l_bareme_currency;
1012 CLOSE csr_sal_rate;
1013 IF l_bareme_currency <> p_gl_currency THEN
1014 l_conv_factor := hr_currency_pkg.get_rate(p_from_currency => l_bareme_currency,
1015 p_to_currency => p_gl_currency,
1016 p_conversion_date => p_effective_date,
1017 p_rate_type => 'Corporate');
1018 END IF;
1019 l_step_rate := (l_inm*l_bareme_salary_rate)*l_conv_factor;
1020 ELSIF l_step_dtls.scale_type = 'E' THEN -- for exception scale
1021 l_step_rate := l_step_dtls.salary_rate;
1022 END IF;
1023
1024 RETURN NVL(l_step_rate,0);
1025
1026 END get_step_rate;
1027 --
1028 --
1029 --
1030 FUNCTION get_salary_rate(p_assignment_id NUMBER, p_effective_date DATE) RETURN NUMBER IS
1031
1032 Cursor Csr_asg_dtls (p_assignment_id IN NUMBER, p_effective_date IN DATE) IS
1033 SELECT asg.person_id,
1034 asg.grade_ladder_pgm_id "GRADE_LADDER_PGM_ID",
1035 scl.segment9 "PHYSICAL_SHARE",
1036 pqh_fr_utility.Get_Salary_Share(scl.segment9) "SALARY_SHARE",
1037 scl.segment8 "EMP_STAT_SITUATION_ID",
1038 sps.step_id "STEP_ID",
1039 sps.information4 "PGI"
1040 FROM per_all_assignments_f asg,
1041 hr_soft_coding_keyflex scl,
1042 per_spinal_point_placements_f sps
1043 WHERE asg.assignment_id = p_assignment_id
1044 AND p_effective_date BETWEEN asg.effective_start_date and asg.effective_end_date
1045 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1046 AND asg.assignment_id = sps.assignment_id
1047 AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date;
1048
1049 l_asg_dtls csr_asg_dtls%ROWTYPE;
1050 Cursor csr_agent_type(p_person_id IN Number, p_effective_date IN DATE) IS
1051 SELECT per_information15
1052 FROM per_all_people_F
1053 WHERE person_id = p_person_id
1054 AND p_effective_date BETWEEN effective_start_date and effective_end_date;
1055
1056 l_agent_type VARCHAR2(30);
1057
1058 Cursor csr_sts_dtls (p_emp_stat_situation_id IN NUMBER) IS
1059 SELECT sts.situation_type,
1060 sts.sub_type,
1061 sts.remuneration_paid,
1062 sts.pay_share
1063 FROM pqh_fr_stat_situations sts,
1064 pqh_fr_emp_stat_situations ess
1065 WHERE ess.emp_stat_situation_id = p_emp_stat_situation_id
1066 AND ess.statutory_situation_id = sts.statutory_situation_id;
1067 l_sts_dtls csr_sts_dtls%ROWTYPE;
1068
1069
1070 Cursor csr_gl_currency(p_gl_id IN NUMBER, p_effective_date IN DATE) IS
1071 SELECT pgm_uom
1072 FROM ben_pgm_f
1073 WHERE pgm_id = p_gl_id
1074 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1075
1076 l_gl_currency ben_pgm_f.pgm_uom%TYPE;
1077 l_step_rate NUMBER(30,5);
1078
1079 l_asg_salary NUMBER(30,5);
1080 BEGIN
1081 OPEN csr_asg_dtls(p_assignment_id, p_effective_date);
1082 FETCH csr_asg_dtls INTO l_asg_dtls;
1083 CLOSE csr_asg_dtls;
1084 OPEN csr_agent_type(l_asg_dtls.person_id,p_effective_date);
1085 FETCH csr_agent_type INTO l_agent_type;
1086 CLOSE csr_agent_type;
1087 IF l_asg_dtls.grade_ladder_pgm_id IS NOT NULL THEN
1088 OPEN csr_gl_currency(l_asg_dtls.grade_ladder_pgm_id,p_effective_date);
1089 FETCH csr_gl_currency INTO l_gl_currency;
1090 CLOSE csr_gl_currency;
1091 END IF;
1092 IF l_asg_dtls.pgi IS NOT NULL THEN
1093 l_step_rate := pqh_corps_utility.get_salary_rate(fnd_number.canonical_to_number(l_asg_dtls.pgi),p_effective_Date,null,l_gl_currency);
1094 ELSE
1095 l_step_rate := get_step_rate(l_asg_dtls.step_id, p_effective_date,l_gl_currency);
1096 END IF;
1097
1098 IF l_agent_type = '01' THEN -- for fonctionnaires check for the current stat. sit.
1099
1100 IF l_asg_dtls.emp_stat_situation_id IS NOT NULL THEN
1101 OPEN csr_sts_dtls(l_asg_dtls.emp_stat_situation_id);
1102 FETCH csr_sts_dtls INTO l_sts_dtls;
1103 CLOSE csr_sts_dtls;
1104 END IF;
1105
1106 IF NVL(l_sts_dtls.situation_type,'IA') = 'IA' AND NVL(l_sts_dtls.sub_type,'IA_N') = 'IA_N' THEN -- for inactivity-normal situation pay fully
1107 l_asg_salary := l_step_rate*nvl(fnd_number.canonical_to_number(l_asg_dtls.salary_share),100);
1108 ELSE
1109
1110 IF NVL(l_sts_dtls.remuneration_paid,'Y') = 'N' THEN -- if the situation doesn't entitle the civil servant for pay
1111 l_asg_salary := 0;
1112 ELSE -- situation allows the civil servant for pay
1113 l_asg_salary := l_step_rate*nvl(fnd_number.canonical_to_number(l_asg_dtls.salary_share),100)*nvl(l_sts_dtls.pay_share,0);
1114 END IF;
1115
1116 END IF;
1117
1118 ELSIF l_agent_type = '02' THEN -- for non-titulaires, no stat.sit.. so pay by salary share.
1119 l_asg_salary := l_step_rate*nvl(fnd_number.canonical_to_number(l_asg_dtls.salary_share),100);
1120 END IF;
1121 RETURN NVL(l_asg_salary,0);
1122 END get_salary_rate;
1123 --
1124 FUNCTION GET_DT_DIFF_FOR_DISPLAY(p_start_date IN DATE, p_end_date IN DATE) Return VARCHAR2
1125 IS
1126 l_return_value varchar2(100) :=null;
1127
1128 l_months number;
1129 l_days number;
1130
1131 Begin
1132
1133 l_months := trunc(months_between (p_end_date,p_start_date),0);
1134 l_days := p_end_date - add_months(p_start_date,l_months);
1135
1136 l_return_value := l_months ||' '||hr_general.decode_lookup('QUALIFYING_UNITS','M')||
1137 ' '||l_days ||' '||hr_general.decode_lookup('QUALIFYING_UNITS','D');
1138
1139 Return l_return_value;
1140
1141 End GET_DT_DIFF_FOR_DISPLAY;
1142 --
1143 Function GET_BG_TYPE_OF_PS RETURN VARCHAR2
1144 IS
1145 --
1146 Cursor csr_get_bg_typ_of_ps IS
1147 Select System_type_cd
1148 from per_shared_types_vl sh , hr_organization_information O
1149 where O.org_information_context = 'FR_PQH_GROUPING_UNIT_INFO'
1150 and O.organization_id = hr_general.get_business_group_id
1151 and sh.shared_type_id = o.org_information1;
1152 --
1153 l_return_value varchar2(10) := null;
1154 Begin
1155 --
1156 Open csr_get_bg_typ_of_ps;
1157 Fetch csr_get_bg_typ_of_ps into l_return_value;
1158 Close csr_get_bg_typ_of_ps;
1159
1160 Return l_return_value;
1161
1162 --
1163 End GET_BG_TYPE_OF_PS;
1164
1165 function view_start_date(p_assignment_id in number,
1166 p_start_date in date,
1167 p_action in varchar2) return date is
1168 l_start_date date := p_start_date - 1;
1169 cursor csr_asg_affect is
1170 SELECT nvl(scl.segment23,'-999') Identifier,
1171 scl.segment24 Type,
1172 scl.segment27 seg27,
1173 scl.segment26 seg26,
1174 assign.position_id Position,
1175 scl.segment25 PercentAffected,
1176 assign.normal_hours WorkingHours,
1177 assign.frequency Frequency,
1178 nvl(assign.supervisor_id,-999) Supervisor,
1179 assign.effective_start_date,
1180 assign.assignment_status_type_id
1181 from per_all_assignments_f assign, hr_soft_coding_keyflex scl
1182 WHERE assign.person_id = p_assignment_id
1183 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1184 AND assign.primary_flag ='N'
1185 AND assign.assignment_status_type_id <> 3
1186 and (assign.effective_start_date = p_start_date
1187 or l_start_date between assign.effective_start_date and assign.effective_end_date)
1188 ORDER by assign.effective_start_date;
1189
1190 cursor csr_asg_employ is
1191 SELECT asg.effective_start_date effective_start_date,
1192 nvl(asg.establishment_id,-999) establishment_id,
1193 nvl(asg.employment_category, '-999') category,
1194 nvl(asg.normal_hours,-999) normal_hours,
1195 nvl(asg.frequency,'-999') frequency,
1196 nvl(scl.segment19,'-999') reason,
1197 nvl(scl.segment9,'-999') share_part
1198 FROM per_all_assignments_f asg,
1199 hr_soft_coding_keyflex scl,
1200 per_shared_types pst
1201 WHERE asg.assignment_id = p_assignment_id
1202 AND pst.shared_type_id(+) = scl.segment9
1203 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1204 and (effective_start_date = p_start_date
1205 or l_start_date between effective_start_date and effective_end_date)
1206 ORDER by effective_start_date;
1207 --
1208 CURSOR csr_asg_career IS
1209 SELECT asg.effective_start_date effective_start_date
1210 ,scl.segment10 employment_category
1211 ,NVL(asg.grade_ladder_pgm_id,-1) grade_ladder_pgm_id
1212 ,NVL(asg.grade_id,-1) grade_id
1213 FROM per_all_assignments_f asg
1214 ,hr_soft_coding_keyflex scl
1215 WHERE asg.assignment_id = p_assignment_id
1216 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1217 AND(asg.effective_start_date = p_start_date
1218 OR l_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
1219 ORDER BY asg.effective_start_date;
1220 --
1221
1222 l_old_start_date date;
1223
1224 l_normal_hours per_all_assignments_f.normal_hours%type;
1225 l_supervisor_id per_all_assignments_f.supervisor_id%type;
1226 l_position per_all_assignments_f.position_id%type;
1227 l_frequency per_all_assignments_f.frequency%type;
1228 l_establish per_all_assignments_f.establishment_id%type;
1229 l_reason hr_soft_coding_keyflex.segment19%type;
1230 l_share hr_soft_coding_keyflex.segment9%type;
1231 l_seg26 hr_soft_coding_keyflex.segment26%type;
1232 l_seg27 hr_soft_coding_keyflex.segment27%type;
1233 l_identifier hr_soft_coding_keyflex.segment9%type;
1234 l_type hr_soft_coding_keyflex.segment9%type;
1235 l_percent number;
1236 l_category per_all_assignments_f.employment_category%type;
1237 l_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1238 l_emp_catg hr_soft_coding_keyflex.segment10%type;
1239 l_grd_ldr_pgm_id per_all_assignments_f.grade_ladder_pgm_id%type;
1240 l_grd_id per_all_assignments_f.grade_id%type;
1241
1242 l_old_normal_hours per_all_assignments_f.normal_hours%type;
1243 l_old_supervisor_id per_all_assignments_f.supervisor_id%type;
1244 l_old_position per_all_assignments_f.position_id%type;
1245 l_old_frequency per_all_assignments_f.frequency%type;
1246 l_old_establish per_all_assignments_f.establishment_id%type;
1247 l_old_reason hr_soft_coding_keyflex.segment19%type;
1248 l_old_share hr_soft_coding_keyflex.segment9%type;
1249 l_old_seg26 hr_soft_coding_keyflex.segment26%type;
1250 l_old_seg27 hr_soft_coding_keyflex.segment27%type;
1251 l_old_identifier hr_soft_coding_keyflex.segment9%type;
1252 l_old_type hr_soft_coding_keyflex.segment9%type;
1253 l_old_percent number;
1254 l_old_category per_all_assignments_f.employment_category%type;
1255 l_old_assign_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1256 l_old_emp_catg hr_soft_coding_keyflex.segment10%type;
1257 l_old_grd_ldr_pgm_id per_all_assignments_f.grade_ladder_pgm_id%type;
1258 l_old_grd_id per_all_assignments_f.grade_id%type;
1259
1260 begin
1261 -- this function is to be called from the view pages query to get the rows which are to be displayed
1262 -- logic of this routine will be check the value change as of earlier date, if there is a change
1263 -- then report true for this row otherwise return false
1264 -- data to be compared is dependent on action code passed
1265 -- cursor will return max 2 rows, we have to compare both the values
1266
1267 if p_action = 'CAREER' then
1268 --
1269 FOR i IN csr_asg_career LOOP
1270 hr_utility.set_location('inside the career loop',10);
1271 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1272 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1273 IF i.effective_start_date = p_start_date THEN
1274 hr_utility.set_location('current record',10);
1275 l_emp_catg := i.employment_category;
1276 l_grd_ldr_pgm_id := i.grade_ladder_pgm_id;
1277 l_grd_id := i.grade_id;
1278 ELSE
1279 hr_utility.set_location('previous record',10);
1280 l_old_start_date := i.effective_start_date;
1281 l_old_emp_catg := i.employment_category;
1282 l_old_grd_ldr_pgm_id := i.grade_ladder_pgm_id;
1283 l_old_grd_id := i.grade_id;
1284 END IF;
1285 END LOOP;
1286 IF l_old_start_date IS NULL THEN
1287 hr_utility.set_location('1st exit',10);
1288 RETURN HR_GENERAL.end_of_time; --Returning end of time because we dont want to match it to asg.effective_start_date
1289 ELSE
1290 IF l_emp_catg <> l_old_emp_catg AND (l_grd_ldr_pgm_id = l_old_grd_ldr_pgm_id AND l_grd_id = l_old_grd_id) THEN
1291 hr_utility.set_location('3rd exit',10);
1292 RETURN p_start_date;
1293 ELSE
1294 hr_utility.set_location('2nd exit',10);
1295 RETURN HR_GENERAL.end_of_time; --Returning end of time because we dont want to match it to asg.effective_start_date
1296 END IF;
1297 END IF;
1298 --
1299 elsif p_action = 'EMPLOY' then
1300 for i in csr_asg_employ loop
1301 hr_utility.set_location('inside the employ loop',10);
1302 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1303 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1304 if i.effective_start_date = p_start_date then
1305 hr_utility.set_location('current record',10);
1306 l_normal_hours := i.normal_hours;
1307 l_frequency := i.frequency;
1308 l_establish := i.establishment_id;
1309 l_category := i.category;
1310 l_reason := i.reason;
1311 l_share := i.share_part;
1312 else
1313 hr_utility.set_location('previous record',10);
1314 l_old_start_date := i.effective_start_date;
1315 l_old_normal_hours := i.normal_hours;
1316 l_old_frequency := i.frequency;
1317 l_old_establish := i.establishment_id;
1318 l_old_category := i.category;
1319 l_old_reason := i.reason;
1320 l_old_share := i.share_part;
1321 end if;
1322 end loop;
1323 if l_old_start_date is null then
1324 hr_utility.set_location('1st exit',10);
1325 return p_start_date;
1326 else
1327 if l_normal_hours = l_old_normal_hours
1328 and l_frequency = l_old_frequency
1329 and l_establish = l_old_establish
1330 and l_category = l_old_category
1331 and l_reason = l_old_reason
1332 and l_share = l_old_share then
1333 hr_utility.set_location('2nd exit',10);
1334 return l_old_start_date;
1335 else
1336 hr_utility.set_location('3rd exit',10);
1337 return p_start_date;
1338 end if;
1339 end if;
1340 elsif p_action = 'AFFECT' then
1341 for i in csr_asg_affect loop
1342 hr_utility.set_location('inside the affect loop',10);
1343 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1344 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1345 if i.effective_start_date = p_start_date then
1346 hr_utility.set_location('current record',10);
1347 l_identifier := i.identifier;
1348 l_type := i.type;
1349 l_seg27 := i.seg27;
1350 l_seg26 := i.seg26;
1351 l_position := i.position;
1352 l_percent := i.percentaffected;
1353 l_normal_hours := i.workinghours;
1354 l_frequency := i.frequency;
1355 l_supervisor_id := i.supervisor;
1356 l_assignment_status_type_id := i.assignment_status_type_id;
1357 else
1358 hr_utility.set_location('previous record',10);
1359 l_old_identifier := i.identifier;
1360 l_old_type := i.type;
1361 l_old_seg27 := i.seg27;
1362 l_old_seg26 := i.seg26;
1363 l_old_position := i.position;
1364 l_old_percent := i.percentaffected;
1365 l_old_normal_hours := i.workinghours;
1366 l_old_frequency := i.frequency;
1367 l_old_supervisor_id := i.supervisor;
1368 l_old_start_date := i.effective_start_date;
1369 l_old_assign_status_type_id := i.assignment_status_type_id;
1370 end if;
1371 end loop;
1372 if l_old_start_date is null then
1373 hr_utility.set_location('1st exit',10);
1374 return p_start_date;
1375 else
1376 if l_old_identifier = l_identifier
1377 and l_old_type = l_type
1378 and l_old_seg27 = l_seg27
1379 and l_old_seg26 = l_seg26
1380 and l_old_position = l_position
1381 and l_old_percent = l_percent
1382 and l_old_normal_hours = l_normal_hours
1383 and l_old_frequency = l_frequency
1384 and l_old_supervisor_id = l_supervisor_id
1385 and l_old_assign_status_type_id = l_assignment_status_type_id then
1386 hr_utility.set_location('2nd exit',10);
1387 return l_old_start_date;
1388 else
1389 hr_utility.set_location('3rd exit',10);
1390 return p_start_date;
1391 end if;
1392 end if;
1393 else
1394 hr_utility.set_location('invalid action passed'||p_action,10);
1395 end if;
1396
1397 end view_start_date;
1398 --
1399 ------------------------------------------------------------------------------
1400 --------------------------< get_proposed_end_date >---------------------------
1401 ------------------------------------------------------------------------------
1402 FUNCTION get_proposed_end_date(p_contract_id IN NUMBER,
1403 p_effective_date IN DATE)
1404 RETURN DATE IS
1405 --
1406 --Cursor to fetch duration and extension details for the Contract
1407 CURSOR csr_contract_dtls IS
1408 SELECT status, duration, duration_units,
1409 extension_period, extension_period_units, number_of_extensions
1410 FROM per_contracts_f
1411 WHERE contract_id = NVL(p_contract_id,-1)
1412 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1413
1414 --Variable Declaration.
1415 l_status PER_CONTRACTS_F.status%TYPE;
1416 l_duration PER_CONTRACTS_F.duration%TYPE;
1417 l_duration_units PER_CONTRACTS_F.duration_units%TYPE;
1418 l_extension PER_CONTRACTS_F.extension_period%TYPE;
1419 l_extension_units PER_CONTRACTS_F.extension_period_units%TYPE;
1420 l_no_of_extensions PER_CONTRACTS_F.number_of_extensions%TYPE;
1421 l_start_date DATE;
1422 l_proposed_end_date DATE;
1423 --
1424 BEGIN
1425 --
1426 OPEN csr_contract_dtls;
1427 FETCH csr_contract_dtls INTO l_status,l_duration,l_duration_units,
1428 l_extension,l_extension_units,l_no_of_extensions;
1429 IF csr_contract_dtls%NOTFOUND THEN
1430 CLOSE csr_contract_dtls;
1431 RETURN NULL;
1432 END IF;
1433 IF csr_contract_dtls%ISOPEN THEN
1434 CLOSE csr_contract_dtls;
1435 END IF;
1436 --
1437 l_start_date := HR_CONTRACT_API.get_active_start_date(p_contract_id,p_effective_date,l_status);
1438 --
1439 IF l_duration_units = 'Y' THEN
1440 l_proposed_end_date := ADD_MONTHS(l_start_date,NVL(l_duration,0)*12);
1441 ELSIF l_duration_units = 'M' THEN
1442 l_proposed_end_date := ADD_MONTHS(l_start_date,NVL(l_duration,0));
1443 ELSIF l_duration_units = 'W' THEN
1444 l_proposed_end_date := l_start_date+(NVL(l_duration,0)*7);
1445 ELSIF l_duration_units = 'H' THEN
1446 l_proposed_end_date := l_start_date+(NVL(l_duration,0)/24);
1447 ELSE
1448 l_proposed_end_date := l_start_date;
1449 END IF;
1450 --
1451 FOR i IN 1..NVL(l_no_of_extensions,0)
1452 LOOP
1453 IF l_extension_units = 'Y' THEN
1454 l_proposed_end_date := ADD_MONTHS(l_proposed_end_date,NVL(l_extension,0)*12);
1455 ELSIF l_extension_units = 'M' THEN
1456 l_proposed_end_date := ADD_MONTHS(l_proposed_end_date,NVL(l_extension,0));
1457 ELSIF l_extension_units = 'W' THEN
1458 l_proposed_end_date := l_proposed_end_date+(NVL(l_extension,0)*7);
1459 ELSIF l_extension_units = 'H' THEN
1460 l_proposed_end_date := l_proposed_end_date+(NVL(l_extension,0)/24);
1461 ELSE
1462 l_proposed_end_date := l_proposed_end_date;
1463 END IF;
1464 END LOOP;
1465 --
1466 RETURN TRUNC(l_proposed_end_date);
1467 --
1468 END get_proposed_end_date;
1469 --
1470 ------------------------------------------------------------------------------
1471 --------------------------< diff_corps_attributes >---------------------------
1472 ------------------------------------------------------------------------------
1473 FUNCTION diff_corps_attributes(p_old_ben_pgm_id IN VARCHAR2,
1474 p_new_ben_pgm_id IN VARCHAR2,
1475 p_primary_assign_id IN NUMBER,
1476 p_effective_date IN DATE)
1477 RETURN VARCHAR2 IS
1478 --
1479 --Cursor to check whether different Corp Affectations exist
1480 CURSOR csr_diff_corps_asg IS
1481 SELECT 'Y'
1482 FROM per_all_assignments_f asg,
1483 hr_soft_coding_keyflex scl,
1484 per_assignment_status_types ast
1485 WHERE scl.segment26 = to_char(p_primary_assign_id) --changed for bug 7211180
1486 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1487 AND ast.per_system_status = 'ACTIVE_ASSIGN'
1488 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1489 AND asg.primary_flag = 'N'
1490 AND p_effective_date BETWEEN asg.effective_start_date AND NVL(asg.effective_end_date,HR_GENERAL.end_of_time)
1491 AND asg.position_id NOT IN(SELECT position_id
1492 FROM hr_all_positions_f
1493 WHERE information_category = 'FR_PQH'
1494 AND information10 =(SELECT TO_CHAR(corps_definition_id)
1495 FROM pqh_corps_definitions
1496 WHERE ben_pgm_id = TO_NUMBER(p_new_ben_pgm_id)
1497 AND p_effective_date BETWEEN date_from
1498 AND NVL(date_to,HR_GENERAL.end_of_time))
1499 AND p_effective_date BETWEEN effective_start_date
1500 AND NVL(effective_end_date,HR_GENERAL.end_of_time));
1501 --
1502 --Cursor to fetch working hours.
1503 CURSOR csr_working_hours(p_ben_pgm_id NUMBER) IS
1504 SELECT NVL(normal_hours,0), NVL(normal_hours_frequency,'X')
1505 FROM pqh_corps_definitions
1506 WHERE ben_pgm_id = p_ben_pgm_id
1507 AND p_effective_date BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time);
1508 --
1509 --Variable Declarations.
1510 l_old_hours NUMBER;
1511 l_old_frequency VARCHAR2(01);
1512 l_new_hours NUMBER;
1513 l_new_frequency VARCHAR2(01);
1514 l_diff_corps_affect VARCHAR2(01);
1515 l_diff_work_hours VARCHAR2(01);
1516 l_return_status VARCHAR2(01);
1517 --
1518 BEGIN
1519 --
1520 --Check if Affectations exist outside new corp.
1521 IF p_new_ben_pgm_id IS NOT NULL THEN
1522 OPEN csr_diff_corps_asg;
1523 FETCH csr_diff_corps_asg INTO l_diff_corps_affect;
1524 IF csr_diff_corps_asg%NOTFOUND THEN
1525 l_diff_corps_affect := 'N';
1526 END IF;
1527 IF csr_diff_corps_asg%ISOPEN THEN
1528 CLOSE csr_diff_corps_asg;
1529 END IF;
1530 ELSE
1531 l_diff_corps_affect := 'N';
1532 END IF;
1533 --
1534 --Get Old Corp working hours.
1535 IF p_old_ben_pgm_id IS NOT NULL AND UPPER(p_old_ben_pgm_id) <> 'NULL' THEN
1536 OPEN csr_working_hours(TO_NUMBER(p_old_ben_pgm_id));
1537 FETCH csr_working_hours INTO l_old_hours,l_old_frequency;
1538 IF csr_working_hours%NOTFOUND THEN
1539 l_old_hours := 0;
1540 l_old_frequency := 'X';
1541 END IF;
1542 IF csr_working_hours%ISOPEN THEN
1543 CLOSE csr_working_hours;
1544 END IF;
1545 ELSE
1546 l_old_hours := 0;
1547 l_old_frequency := 'X';
1548 END IF;
1549 --
1550 --Get New Corp working hours.
1551 IF p_new_ben_pgm_id IS NOT NULL THEN
1552 OPEN csr_working_hours(TO_NUMBER(p_new_ben_pgm_id));
1553 FETCH csr_working_hours INTO l_new_hours,l_new_frequency;
1554 IF csr_working_hours%NOTFOUND THEN
1555 l_new_hours := 0;
1556 l_new_frequency := 'X';
1557 END IF;
1558 IF csr_working_hours%ISOPEN THEN
1559 CLOSE csr_working_hours;
1560 END IF;
1561 ELSE
1562 l_new_hours := 0;
1563 l_new_frequency := 'X';
1564 END IF;
1565 --
1566 --Check whether old and new hours are different.
1567 IF p_old_ben_pgm_id IS NOT NULL AND UPPER(p_old_ben_pgm_id) <> 'NULL' THEN
1568 IF ((l_old_hours = l_new_hours) AND (l_old_frequency = l_new_frequency)) THEN
1569 l_diff_work_hours := 'N';
1570 ELSE
1571 l_diff_work_hours := 'Y';
1572 END IF;
1573 ELSE
1574 l_diff_work_hours := 'N';
1575 END IF;
1576
1577 --
1578 --Set Return Status.
1579 IF l_diff_corps_affect = 'Y' AND l_diff_work_hours = 'Y' THEN
1580 l_return_status := 'B'; --Both are different
1581 ELSIF l_diff_corps_affect = 'Y' AND l_diff_work_hours = 'N' THEN
1582 l_return_status := 'A'; --Affectations exist outside new Corps but Working Hours didnt change
1583 ELSIF l_diff_corps_affect = 'N' AND l_diff_work_hours = 'Y' THEN
1584 l_return_status := 'H'; --Affectations dont exist outside new Corps but Working Hours have changed
1585 ELSIF l_diff_corps_affect = 'N' AND l_diff_work_hours = 'N' THEN
1586 l_return_status := 'N'; --No Affectations outside new Corp and no change in Working Hours
1587 ELSE
1588 l_return_status := 'E';
1589 END IF;
1590 --
1591 RETURN l_return_status;
1592 --
1593 END diff_corps_attributes;
1594 --
1595 ------------------------------------------------------------------------------
1596 ------------------------------< check_work_hrs >------------------------------
1597 ------------------------------------------------------------------------------
1598 FUNCTION check_work_hrs(p_old_estab_id IN VARCHAR2,
1599 p_new_estab_id IN VARCHAR2,
1600 p_effective_date IN DATE)
1601 RETURN VARCHAR2 IS
1602 --
1603 CURSOR csr_working_hours(p_estab_id NUMBER) IS
1604 SELECT FND_NUMBER.canonical_to_number(org_information4) hours, 'M' frequency
1605 FROM hr_organization_information_v
1606 WHERE org_information_context = 'FR_ESTAB_INFO'
1607 AND organization_id = p_estab_id;
1608 --
1609 --Variable Declarations.
1610 l_old_hours NUMBER;
1611 l_old_frequency VARCHAR2(01);
1612 l_new_hours NUMBER;
1613 l_new_frequency VARCHAR2(01);
1614 l_diff_work_hours VARCHAR2(01);
1615 --
1616 BEGIN
1617 --
1618 l_diff_work_hours := 'N';
1619 --
1620 --Get Old Estab working hours.
1621 IF p_old_estab_id IS NOT NULL AND UPPER(p_old_estab_id) <> 'NULL' THEN
1622 OPEN csr_working_hours(TO_NUMBER(p_old_estab_id));
1623 FETCH csr_working_hours INTO l_old_hours,l_old_frequency;
1624 IF csr_working_hours%NOTFOUND THEN
1625 l_old_hours := 0;
1626 l_old_frequency := 'X';
1627 END IF;
1628 IF csr_working_hours%ISOPEN THEN
1629 CLOSE csr_working_hours;
1630 END IF;
1631 ELSE
1632 l_old_hours := 0;
1633 l_old_frequency := 'X';
1634 END IF;
1635 --
1636 --Get New Estab working hours.
1637 IF p_new_estab_id IS NOT NULL THEN
1638 OPEN csr_working_hours(TO_NUMBER(p_new_estab_id));
1639 FETCH csr_working_hours INTO l_new_hours,l_new_frequency;
1640 IF csr_working_hours%NOTFOUND THEN
1641 l_new_hours := 0;
1642 l_new_frequency := 'X';
1643 END IF;
1644 IF csr_working_hours%ISOPEN THEN
1645 CLOSE csr_working_hours;
1646 END IF;
1647 ELSE
1648 l_new_hours := 0;
1649 l_new_frequency := 'X';
1650 END IF;
1651 --
1652 --Check whether old and new hours are different.
1653 IF p_old_estab_id IS NOT NULL AND UPPER(p_old_estab_id) <> 'NULL' THEN
1654 IF ((l_old_hours = l_new_hours) AND (l_old_frequency = l_new_frequency)) THEN
1655 l_diff_work_hours := 'N';
1656 ELSE
1657 l_diff_work_hours := 'Y';
1658 END IF;
1659 ELSE
1660 l_diff_work_hours := 'N';
1661 END IF;
1662 --
1663 RETURN l_diff_work_hours;
1664 --
1665 END check_work_hrs;
1666 --
1667
1668 function view_end_date(p_assignment_id in number,
1669 p_person_id in number,
1670 p_start_date in date,
1671 p_action in varchar2) return date is
1672 l_end_date date := null;
1673 l_assignment_id number;
1674
1675 cursor csr_asg_affect is
1676 SELECT nvl(scl.segment23,'-999') Identifier,
1677 scl.segment24 Type,
1678 nvl(scl.segment27,'-999') seg27,
1679 nvl(scl.segment26,'-999') seg26,
1680 nvl(assign.position_id,-999) Position,
1681 nvl(scl.segment25,-999) PercentAffected,
1682 nvl(assign.normal_hours,-999) WorkingHours,
1683 nvl(assign.frequency, '-999') Frequency,
1684 nvl(assign.supervisor_id,-999) Supervisor,
1685 assign.effective_start_date,
1686 assign.assignment_status_type_id,
1687 assign.effective_end_date
1688 from per_all_assignments_f assign, hr_soft_coding_keyflex scl
1689 WHERE assign.person_id = p_person_id
1690 AND assign.assignment_id = l_assignment_id
1691 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1692 AND assign.primary_flag ='N'
1693 AND assign.assignment_status_type_id <> 3
1694 and (assign.effective_start_date = p_start_date
1695 or effective_start_date > l_end_date)
1696 ORDER by assign.effective_start_date;
1697
1698 cursor csr_end_date is
1699 SELECT asg.effective_end_date effective_end_date
1700 FROM per_all_assignments_f asg
1701 WHERE asg.assignment_id = l_assignment_id
1702 and effective_start_date = p_start_date;
1703
1704 cursor csr_asg_employ is
1705 SELECT asg.effective_start_date effective_start_date,
1706 nvl(asg.establishment_id,-999) establishment_id,
1707 nvl(asg.employment_category, '-999') category,
1708 nvl(asg.normal_hours,-999) normal_hours,
1709 nvl(asg.frequency,'-999') frequency,
1710 nvl(scl.segment19,'-999') reason,
1711 nvl(scl.segment9,'-999') share_part,
1712 asg.effective_end_date effective_end_date
1713 FROM per_all_assignments_f asg,
1714 hr_soft_coding_keyflex scl,
1715 per_shared_types pst
1716 WHERE asg.assignment_id = p_assignment_id
1717 AND pst.shared_type_id(+) = scl.segment9
1718 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1719 and (effective_start_date = p_start_date
1720 or effective_start_date > l_end_date )
1721 ORDER by effective_start_date;
1722 --
1723 CURSOR csr_asg_career IS
1724 SELECT asg.effective_start_date effective_start_date
1725 ,scl.segment10 employment_category
1726 ,NVL(asg.grade_ladder_pgm_id,-1) grade_ladder_pgm_id
1727 ,NVL(asg.grade_id,-1) grade_id
1728 ,asg.effective_end_date effective_end_date
1729 FROM per_all_assignments_f asg
1730 ,hr_soft_coding_keyflex scl
1731 WHERE asg.assignment_id = p_assignment_id
1732 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1733 AND(asg.effective_start_date = p_start_date
1734 OR asg.effective_start_date > l_end_date)
1735 ORDER BY asg.effective_start_date;
1736 --
1737 l_fut_start_date date;
1738 l_fut_end_date date;
1739
1740 -- l_end_date date;
1741
1742 l_normal_hours per_all_assignments_f.normal_hours%type;
1743 l_supervisor_id per_all_assignments_f.supervisor_id%type;
1744 l_position per_all_assignments_f.position_id%type;
1745 l_frequency per_all_assignments_f.frequency%type;
1746 l_establish per_all_assignments_f.establishment_id%type;
1747 l_reason hr_soft_coding_keyflex.segment19%type;
1748 l_share hr_soft_coding_keyflex.segment9%type;
1749 l_seg26 hr_soft_coding_keyflex.segment26%type;
1750 l_seg27 hr_soft_coding_keyflex.segment27%type;
1751 l_identifier hr_soft_coding_keyflex.segment9%type;
1752 l_type hr_soft_coding_keyflex.segment9%type;
1753 l_percent number;
1754 l_category per_all_assignments_f.employment_category%type;
1755 l_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1756 l_grd_ldr_pgm_id per_all_assignments_f.grade_ladder_pgm_id%type;
1757 l_grade_id per_all_assignments_f.grade_id%type;
1758 l_emp_cat per_all_assignments_f.employment_category%type;
1759
1760 l_fut_normal_hours per_all_assignments_f.normal_hours%type;
1761 l_fut_supervisor_id per_all_assignments_f.supervisor_id%type;
1762 l_fut_position per_all_assignments_f.position_id%type;
1763 l_fut_frequency per_all_assignments_f.frequency%type;
1764 l_fut_establish per_all_assignments_f.establishment_id%type;
1765 l_fut_reason hr_soft_coding_keyflex.segment19%type;
1766 l_fut_share hr_soft_coding_keyflex.segment9%type;
1767 l_fut_seg26 hr_soft_coding_keyflex.segment26%type;
1768 l_fut_seg27 hr_soft_coding_keyflex.segment27%type;
1769 l_fut_identifier hr_soft_coding_keyflex.segment9%type;
1770 l_fut_type hr_soft_coding_keyflex.segment9%type;
1771 l_fut_percent number;
1772 l_fut_category per_all_assignments_f.employment_category%type;
1773 l_fut_assign_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1774 l_fut_grd_ldr_pgm_id per_all_assignments_f.grade_ladder_pgm_id%type;
1775 l_fut_grade_id per_all_assignments_f.grade_id%type;
1776 l_fut_emp_cat per_all_assignments_f.employment_category%type;
1777
1778 l_return_end_date date;
1779 begin
1780 -- this function is to be called from the view pages query to get the rows which are to be displayed
1781 -- logic of this routine will be check the value change as of earlier date, if there is a change
1782 -- then report true for this row otherwise return false
1783 -- data to be compared is dependent on action code passed
1784 -- cursor will return max 2 rows, we have to compare both the values
1785
1786 if p_action = 'CAREER' then
1787 --
1788 l_assignment_id := p_assignment_id;
1789 FOR i IN csr_end_date LOOP
1790 l_end_date := i.effective_end_date;
1791 END LOOP;
1792 FOR i IN csr_asg_career LOOP
1793 hr_utility.set_location('inside the career loop',10);
1794 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1795 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1796 IF i.effective_start_date = p_start_date THEN
1797 hr_utility.set_location('current record',10);
1798 l_grd_ldr_pgm_id := i.grade_ladder_pgm_id;
1799 l_grade_id := i.grade_id;
1800 l_emp_cat := i.employment_category;
1801 l_end_date := i.effective_end_date;
1802 ELSE
1803 hr_utility.set_location('future record',10);
1804 l_fut_start_date := i.effective_start_date;
1805 l_fut_grd_ldr_pgm_id := i.grade_ladder_pgm_id;
1806 l_fut_grade_id := i.grade_id;
1807 l_fut_emp_cat := i.employment_category;
1808 l_fut_end_date := i.effective_end_date;
1809 END IF;
1810 IF l_fut_start_date IS NULL THEN
1811 hr_utility.set_location('first time in loop',10);
1812 l_return_end_date := l_end_date;
1813 ELSE
1814 IF l_grd_ldr_pgm_id=l_fut_grd_ldr_pgm_id AND l_grade_id=l_fut_grade_id AND l_emp_cat=l_fut_emp_cat THEN
1815 hr_utility.set_location('equality condition satisfied',10);
1816 l_return_end_date := l_fut_end_date;
1817 ELSE
1818 hr_utility.set_location('Equality condition not satisfied',10);
1819 EXIT;
1820 END IF;
1821 END IF;
1822 END LOOP;
1823 hr_utility.set_location('Out of loop',10);
1824 RETURN l_return_end_date;
1825 --
1826 elsif p_action = 'EMPLOY' then
1827
1828 l_assignment_id := p_assignment_id;
1829 for i in csr_end_date loop
1830 l_end_date := i.effective_end_date;
1831 end loop;
1832 for i in csr_asg_employ loop
1833 hr_utility.set_location('inside the employ loop',10);
1834 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1835 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1836 if i.effective_start_date = p_start_date then
1837 hr_utility.set_location('current record',10);
1838 l_normal_hours := i.normal_hours;
1839 l_frequency := i.frequency;
1840 l_establish := i.establishment_id;
1841 l_category := i.category;
1842 l_reason := i.reason;
1843 l_share := i.share_part;
1844 l_end_date := i.effective_end_date;
1845 else
1846 hr_utility.set_location('previous record',10);
1847 l_fut_start_date := i.effective_start_date;
1848 l_fut_normal_hours := i.normal_hours;
1849 l_fut_frequency := i.frequency;
1850 l_fut_establish := i.establishment_id;
1851 l_fut_category := i.category;
1852 l_fut_reason := i.reason;
1853 l_fut_share := i.share_part;
1854 l_fut_end_date := i.effective_end_date;
1855 end if;
1856 if l_fut_start_date is null then
1857 hr_utility.set_location('first time in loop',10);
1858 l_return_end_date := l_end_date;
1859 else
1860 if l_normal_hours = l_fut_normal_hours
1861 and l_frequency = l_fut_frequency
1862 and l_establish = l_fut_establish
1863 and l_category = l_fut_category
1864 and l_reason = l_fut_reason
1865 and l_share = l_fut_share then
1866 hr_utility.set_location('equality condition satisfied',10);
1867 l_return_end_date := l_fut_end_date;
1868 else
1869 hr_utility.set_location('Equality condition not satisfied',10);
1870 exit;
1871 end if;
1872 end if;
1873 end loop;
1874 hr_utility.set_location('Out of loop',10);
1875 return l_return_end_date;
1876 elsif p_action = 'AFFECT' then
1877 l_assignment_id := p_assignment_id;
1878 for i in csr_end_date loop
1879 l_end_date := i.effective_end_date;
1880 end loop;
1881 for i in csr_asg_affect loop
1882 hr_utility.set_location('inside the affect loop',10);
1883 hr_utility.set_location('start date is '||to_char(p_start_date,'ddmmyyyy'),10);
1884 hr_utility.set_location('asg start date is '||to_char(i.effective_start_date,'ddmmyyyy'),10);
1885 if i.effective_start_date = p_start_date then
1886 hr_utility.set_location('current record',10);
1887 l_identifier := i.identifier;
1888 l_type := i.type;
1889 l_seg27 := i.seg27;
1890 l_seg26 := i.seg26;
1891 l_position := i.position;
1892 l_percent := i.percentaffected;
1893 l_normal_hours := i.workinghours;
1894 l_frequency := i.frequency;
1895 l_supervisor_id := i.supervisor;
1896 l_assignment_status_type_id := i.assignment_status_type_id;
1897 l_end_date := i.effective_end_date;
1898 else
1899 hr_utility.set_location('previous record',10);
1900 l_fut_identifier := i.identifier;
1901 l_fut_type := i.type;
1902 l_fut_seg27 := i.seg27;
1903 l_fut_seg26 := i.seg26;
1904 l_fut_position := i.position;
1905 l_fut_percent := i.percentaffected;
1906 l_fut_normal_hours := i.workinghours;
1907 l_fut_frequency := i.frequency;
1908 l_fut_supervisor_id := i.supervisor;
1909 l_fut_start_date := i.effective_start_date;
1910 l_fut_assign_status_type_id := i.assignment_status_type_id;
1911 l_fut_end_date := i.effective_end_date;
1912 end if;
1913 if l_fut_start_date is null then
1914 hr_utility.set_location('first time in loop',10);
1915 l_return_end_date := l_end_date;
1916 else
1917 if l_fut_identifier = l_identifier
1918 and l_fut_type = l_type
1919 and l_fut_seg27 = l_seg27
1920 and l_fut_seg26 = l_seg26
1921 and l_fut_position = l_position
1922 and l_fut_percent = l_percent
1923 and l_fut_normal_hours = l_normal_hours
1924 and l_fut_frequency = l_frequency
1925 and l_fut_supervisor_id = l_supervisor_id
1926 and l_fut_assign_status_type_id = l_assignment_status_type_id then
1927 hr_utility.set_location('equality condition satisfied',10);
1928 l_return_end_date := l_fut_end_date;
1929 else
1930 hr_utility.set_location('Equality condition not satisfied',10);
1931 exit;
1932 end if;
1933 end if;
1934 end loop;
1935 hr_utility.set_location('Out of loop',10);
1936 return l_return_end_date;
1937 else
1938 hr_utility.set_location('invalid action passed'||p_action,10);
1939 return null;
1940 end if;
1941
1942 end view_end_date;
1943
1944 FUNCTION diff_corps_positions(p_pos_id IN VARCHAR2,
1945 p_primary_assign_id IN NUMBER,
1946 p_effective_date IN DATE)
1947 RETURN VARCHAR2 IS
1948 --Cursor to get the Corp id for the current Position
1949 Cursor csr_pos_corp is
1950 SELECT information10 corps_id
1951 FROM hr_positions_f
1952 WHERE p_effective_date between effective_start_date and effective_end_date
1953 AND position_id = p_pos_id;
1954
1955 --Cursor to get the Corp id for the Career Definition for the person
1956 Cursor csr_career_corp is
1957 Select Corps_Definition_Id
1958 From Pqh_Corps_Definitions
1959 where Ben_Pgm_Id in (select grade_ladder_pgm_id
1960 from per_all_assignments_f
1961 where assignment_id = p_primary_assign_id
1962 and p_effective_date between effective_start_date and effective_end_date
1963 and primary_flag = 'Y' );
1964
1965 --Variable Declarations.
1966 l_pos_corps_id NUMBER;
1967 l_career_corps_id NUMBER;
1968 l_return_status VARCHAR2(01);
1969
1970 BEGIN
1971 OPEN csr_pos_corp;
1972 FETCH csr_pos_corp INTO l_pos_corps_id;
1973 CLOSE csr_pos_corp;
1974
1975 OPEN csr_career_corp;
1976 FETCH csr_career_corp INTO l_career_corps_id ;
1977 CLOSE csr_career_corp;
1978
1979 if l_career_corps_id <> l_pos_corps_id then
1980 l_return_status := 'D';
1981 else
1982 l_return_status := 'S';
1983 end if;
1984
1985 RETURN l_return_status;
1986 --
1987 END diff_corps_positions;
1988 --
1989 ------------------------------------------------------------------------------
1990 ---------------------------< get_ps_org_cat_info >----------------------------
1991 ------------------------------------------------------------------------------
1992 FUNCTION get_ps_org_cat_info(p_person_id NUMBER,
1993 p_effective_date DATE) RETURN VARCHAR2 IS
1994 --
1995 --Cursor to fetch Type Of PS Information1 for BG Org.
1996 CURSOR csr_ps_info IS
1997 SELECT information1
1998 FROM per_shared_types
1999 WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
2000 AND shared_type_id =(SELECT org_information1
2001 FROM hr_organization_information
2002 WHERE org_information_context = 'FR_PQH_GROUPING_UNIT_INFO'
2003 AND organization_id =(SELECT business_group_id
2004 FROM per_all_people_f
2005 WHERE person_id = p_person_id
2006 AND p_effective_date BETWEEN effective_start_date
2007 AND effective_end_date));
2008 --
2009 --Variable Declarations.
2010 l_ps_info VARCHAR2(01);
2011 --
2012 BEGIN
2013 --
2014 OPEN csr_ps_info;
2015 FETCH csr_ps_info INTO l_ps_info;
2016 IF csr_ps_info%NOTFOUND THEN
2017 l_ps_info := 'N';
2018 END IF;
2019 IF csr_ps_info%ISOPEN THEN
2020 CLOSE csr_ps_info;
2021 END IF;
2022 --
2023 RETURN l_ps_info;
2024 --
2025 END get_ps_org_cat_info;
2026 --
2027 ------------------------------------------------------------------------------
2028 ---------------------------------< get_ps >-----------------------------------
2029 ------------------------------------------------------------------------------
2030 --This function is same as get_lookup_shared_types but because of Web ADI
2031 --Integrator Col limitations created this function so as to reduce size of
2032 --fn call in query to fit Val_Object_Name Column size for Type_Of_PS LOV.
2033 ------------------------------------------------------------------------------
2034 FUNCTION get_ps(p_lookup_code VARCHAR2
2035 ,p_return_value VARCHAR2) RETURN VARCHAR2 IS
2036 --
2037 CURSOR csr_glb_shared_types IS
2038 SELECT shared_type_id, shared_type_name
2039 FROM per_shared_types_vl
2040 WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
2041 AND system_type_cd = p_lookup_code
2042 AND business_group_id IS NULL;
2043 --
2044 CURSOR csr_bg_shared_types IS
2045 SELECT shared_type_id, shared_type_name
2046 FROM per_shared_types_vl
2047 WHERE lookup_type = 'FR_PQH_ORG_CATEGORY'
2048 AND system_type_cd = p_lookup_code
2049 AND business_group_id = HR_GENERAL.get_business_group_id;
2050 --
2051 l_bg_return csr_bg_shared_types%ROWTYPE;
2052 l_glb_return csr_glb_shared_types%ROWTYPE;
2053 --
2054 BEGIN
2055 --
2056 OPEN csr_bg_shared_types;
2057 FETCH csr_bg_shared_types INTO l_bg_return;
2058 IF csr_bg_shared_types%FOUND THEN
2059 CLOSE csr_bg_shared_types;
2060 IF p_return_value = 'ID' THEN
2061 RETURN l_bg_return.shared_type_id;
2062 ELSE
2063 RETURN l_bg_return.shared_type_name;
2064 END IF;
2065 ELSE
2066 CLOSE csr_bg_shared_types;
2067 END IF;
2068 --
2069 OPEN csr_glb_shared_types;
2070 FETCH csr_glb_shared_types INTO l_glb_return;
2071 CLOSE csr_glb_shared_types;
2072 IF p_return_value = 'ID' THEN
2073 RETURN l_glb_return.shared_type_id;
2074 ELSE
2075 RETURN l_glb_return.shared_type_name;
2076 END IF;
2077 --
2078 END get_ps;
2079 --
2080 FUNCTION get_currency_desc(p_currency_code IN VARCHAR2) RETURN VARCHAR2
2081 IS
2082 CURSOR c_currency_cur IS
2083 SELECT t.description
2084 FROM fnd_currencies a
2085 ,fnd_currencies_tl t
2086 WHERE a.currency_code = p_currency_code
2087 AND t.currency_code = a.currency_code
2088 AND t.language = USERENV('LANG');
2089 v_currency_desc VARCHAR2(240);
2090 BEGIN
2091 IF p_currency_code IS NOT NULL THEN
2092 OPEN c_currency_cur;
2093 FETCH c_currency_cur INTO v_currency_desc;
2094 CLOSE c_currency_cur;
2095 END IF;
2096 RETURN v_currency_desc;
2097 EXCEPTION
2098 WHEN OTHERS THEN
2099 IF c_currency_cur%ISOPEN THEN
2100 CLOSE c_currency_cur;
2101 END IF;
2102 RETURN NULL;
2103 END get_currency_desc;
2104 --
2105 FUNCTION get_owner_desc(p_org_id IN NUMBER
2106 ,p_effective_date IN DATE) RETURN VARCHAR2
2107 IS
2108 CURSOR c_owner_cur IS
2109 SELECT hru.name
2110 FROM hr_all_organization_units hru
2111 ,hr_organization_information hri
2112 WHERE hru.organization_id = p_org_id
2113 AND hru.organization_id = hri.organization_id
2114 AND hri.org_information_context = 'CLASS'
2115 AND hri.org_information1 IN ('FR_ETABLISSEMENT','FR_SOCIETE','HR_BG')
2116 AND p_effective_date BETWEEN hru.date_from AND NVL(hru.date_to,p_effective_date);
2117 v_owner_desc VARCHAR2(240);
2118 BEGIN
2119 IF p_org_id IS NOT NULL THEN
2120 OPEN c_owner_cur;
2121 FETCH c_owner_cur INTO v_owner_desc;
2122 CLOSE c_owner_cur;
2123 END IF;
2124 RETURN v_owner_desc;
2125 EXCEPTION
2126 WHEN OTHERS THEN
2127 IF c_owner_cur%ISOPEN THEN
2128 CLOSE c_owner_cur;
2129 END IF;
2130 RETURN NULL;
2131 END get_owner_desc;
2132 --
2133 FUNCTION get_payment_name(p_business_group_id IN NUMBER
2134 ,p_payment_code IN NUMBER) RETURN VARCHAR2
2135 IS
2136 CURSOR c_payment_cur IS
2137 SELECT pettl.element_name
2138 FROM pay_element_classifications pec
2139 ,pay_element_types_f pet
2140 ,pay_element_types_f_tl pettl
2141 WHERE(pet.business_group_id = p_business_group_id OR
2142 (pet.business_group_id IS NULL AND pet.legislation_code = 'FR'))
2143 AND pet.element_type_id = p_payment_code
2144 AND pet.element_type_id = pettl.element_type_id
2145 AND pettl.language = USERENV('LANG')
2146 AND pec.classification_id = pet.classification_id;
2147 v_payment_desc VARCHAR2(240);
2148 BEGIN
2149 IF p_payment_code IS NOT NULL THEN
2150 OPEN c_payment_cur;
2151 FETCH c_payment_cur INTO v_payment_desc;
2152 CLOSE c_payment_cur;
2153 END IF;
2154 RETURN v_payment_desc;
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 IF c_payment_cur%ISOPEN THEN
2158 CLOSE c_payment_cur;
2159 END IF;
2160 RETURN NULL;
2161 END get_payment_name;
2162 --
2163 END PQH_FR_UTILITY;
[Home] [Help]