1 package body ghr_validate_perwsdpo AS
2 /* $Header: ghrwsdpo.pkb 120.4.12020000.2 2012/07/05 15:02:31 amnaraya ship $ */
3
4 -- Created g_new_line to use instead of CHR(10)
5 g_new_line varchar2(1) := substr('
6 ',1,1);
7
8
9 procedure update_posn_status (p_position_id in number,
10 p_effective_date in date ) IS
11 l_ovn number;
12 l_esd date;
13 l_eed date;
14 l_name varchar2(240);
15 l_warning boolean;
16 l_pos_def_id number;
17 BEGIN
18 update hr_all_positions_f pos
19 set status = 'VALID'
20 where pos.position_id = p_position_id
21 and p_effective_date between pos.effective_STart_date and pos.effective_end_date
22 and pos.status <> 'VALID';
23
24 END update_posn_status ;
25
26 procedure validate_perwsdpo (p_position_id in number,
27 p_effective_date in date ) IS
28
29 l_pos_desc_id per_position_extra_info.poei_information5%type :=null;
30 l_date_from per_position_extra_info.poei_information3%type :=null;
31 l_date_to per_position_extra_info.poei_information4%type :=null;
32 l_FLSA_DDF per_position_extra_info.poei_information7%type :=null;
33 l_position_occ_DDF per_position_extra_info.poei_information3%type :=null;
34 l_super_status_DDF per_position_extra_info.poei_information16%type :=null;
35 l_position_sens_DDF per_position_extra_info.poei_information13%type :=null;
36 l_compet_level_DDF per_position_extra_info.poei_information9%type :=null;
37 l_working_title_DDF per_position_extra_info.poei_information12%type :=null;
38 l_valide_grade_DDF per_position_extra_info.poei_information3%type :=null;
39 l_job_id per_jobs.job_id%type:=null;
40 l_business_group_id per_positions.business_group_id%type :=null;
41 l_occ_series_KF varchar2(150) :=null;
42
43 l_pay_table_id_DDF per_position_extra_info.poei_information5%type :=null;
44 l_pay_basis_DDF per_position_extra_info.poei_information6%type :=null;
45 l_Per_office_id_DDF per_position_extra_info.poei_information3%type :=null;
46 l_BU_Status_DDF per_position_extra_info.poei_information8%type :=null;
47 l_Work_Schedule_DDF per_position_extra_info.poei_information10%type :=null;
48
49 l_FLSA_table ghr_position_descriptions.FLSA%type :=null;
50 l_position_occ_table ghr_position_descriptions.position_status%type :=null;
51 l_super_status_table ghr_position_descriptions.position_is%type :=null;
52 l_position_sens_table ghr_position_descriptions.position_sensitivity%type :=null;
53 l_compet_level_table ghr_position_descriptions.competitive_level%type :=null;
54 l_working_title_table ghr_pd_classifications.official_title%type :=null;
55 l_pay_plan_table ghr_pd_classifications.pay_plan%type :=null;
56 l_valide_grade_table ghr_pd_classifications.grade_level%type :=null;
57 l_payplan_grade_table varchar2(10) :=null;
58 l_occ_series_table ghr_pd_classifications.occupational_code%type :=null;
59
60 cursor c_pos_extra_info is
61 select poei.poei_information3,poei.poei_information4,poei.poei_information5
62 from per_position_extra_info poei
63 where poei.position_id=p_position_id and poei.information_type='GHR_US_POSITION_DESCRIPTION';
64
65 cursor c_pos_extra_info_DDF1 is
66 select poei.poei_information3,poei.poei_information7
67 ,poei.poei_information8,poei.poei_information10
68 ,poei.poei_information16,poei.poei_information13
69 ,poei.poei_information9,poei.poei_information12
70 from per_position_extra_info poei
71 where poei.position_id=p_position_id
72 and poei.information_type='GHR_US_POS_GRP1';
73
74 cursor c_pos_extra_info_DDF2 is
75 select poei.poei_information3
76 from per_position_extra_info poei
77 where poei.position_id=p_position_id and poei.information_type='GHR_US_POS_GRP2';
78
79 cursor c_pos_extra_info_DDF3 is
80 select poei.poei_information3,poei.poei_information5,poei.poei_information6
81 from per_position_extra_info poei
82 where poei.position_id=p_position_id and poei.information_type='GHR_US_POS_VALID_GRADE';
83
84 cursor c_pos_table is
85 select pos.job_id, pos.business_group_id
86 from hr_all_positions_f pos
87 where pos.position_id= p_position_id
88 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
89
90 cursor c_pos_desc_table is
91 select pd.flsa, pd.position_status, pd.position_is,pd.position_sensitivity,pd.competitive_level,
92 cl.official_title, cl.pay_plan, cl.grade_level, cl.occupational_code
93 from ghr_position_descriptions pd, ghr_pd_classifications cl
94 where pd.position_description_id = l_pos_desc_id
95 and pd.position_description_id=cl.position_description_id;
96
97
98
99
100 BEGIN
101 FOR c_pos_extra_info_rec IN c_pos_extra_info LOOP
102 l_date_from := c_pos_extra_info_rec.poei_information3;
103 l_date_to := c_pos_extra_info_rec.poei_information4;
104 l_pos_desc_id := c_pos_extra_info_rec.poei_information5;
105 exit;
106 END LOOP;
107
108 if l_pos_desc_id is not null then
109
110 if nvl(fnd_date.canonical_to_date(l_date_from),sysdate) > sysdate
111 and nvl(fnd_date.canonical_to_date(l_date_to),sysdate) <= sysdate then
112
113 /* get values from DDFs */
114
115 FOR c_pos_extra_info_DDF1_rec IN c_pos_extra_info_DDF1 LOOP
116 l_Per_office_id_DDF:= c_pos_extra_info_DDF1_rec.poei_information3;
117 l_FLSA_DDF:= c_pos_extra_info_DDF1_rec.poei_information7;
118 l_BU_Status_DDF:= c_pos_extra_info_DDF1_rec.poei_information8;
119 l_Work_Schedule_DDF:= c_pos_extra_info_DDF1_rec.poei_information10;
120 l_super_status_DDF:= c_pos_extra_info_DDF1_rec.poei_information16;
121 l_position_sens_DDF:= c_pos_extra_info_DDF1_rec.poei_information13;
122 l_compet_level_DDF:= c_pos_extra_info_DDF1_rec.poei_information9;
123 l_working_title_DDF:= c_pos_extra_info_DDF1_rec.poei_information12;
124 exit;
125 END LOOP;
126
127 FOR c_pos_extra_info_DDF2_rec IN c_pos_extra_info_DDF2 LOOP
128 l_position_occ_DDF := c_pos_extra_info_DDF2_rec.poei_information3;
129 exit;
130 END LOOP;
131
132
133 FOR c_pos_extra_info_DDF3_rec IN c_pos_extra_info_DDF3 LOOP
134 l_valide_grade_DDF:= c_pos_extra_info_DDF3_rec.poei_information3;
135 l_pay_table_id_DDF:= c_pos_extra_info_DDF3_rec.poei_information5;
136 l_pay_basis_DDF := c_pos_extra_info_DDF3_rec.poei_information6;
137 exit;
138 END LOOP;
139
140
141 FOR c_pos_table_rec IN c_pos_table LOOP
142 l_job_id := c_pos_table_rec.job_id;
143 l_business_group_id := c_pos_table_rec.business_group_id;
144 exit;
145 END LOOP;
146
147 if l_job_id is not null and l_business_group_id is not null then
148 l_occ_series_KF := ghr_api.get_job_occ_series_job(l_job_id ,l_business_group_id);
149 end if;
150
151
152 /* get values from ghr_position_descriptions */
153
154 FOR c_pos_desc_table_rec IN c_pos_desc_table LOOP
155 l_FLSA_table:= c_pos_desc_table_rec.flsa;
156 l_position_occ_table := c_pos_desc_table_rec.position_status;
157 l_super_status_table := c_pos_desc_table_rec.position_is;
158 l_position_sens_table:= c_pos_desc_table_rec.position_sensitivity;
159 l_compet_level_table:= c_pos_desc_table_rec.competitive_level;
160 l_working_title_table:= c_pos_desc_table_rec.official_title;
161 l_pay_plan_table:= c_pos_desc_table_rec.pay_plan;
162 l_valide_grade_table:= c_pos_desc_table_rec.grade_level;
163 l_occ_series_table:= c_pos_desc_table_rec.occupational_code;
164 exit;
165 END LOOP;
166 l_payplan_grade_table := l_pay_plan_table ||'-'||l_valide_grade_table;
167
168
169 /* check the null values for some required DDFs */
170 if l_valide_grade_DDF is null or l_pay_table_id_DDF is null or l_pay_basis_DDF is null
171 or l_Per_office_id_DDF is null or l_FLSA_DDF is null or l_BU_Status_DDF is null
172 or l_Work_Schedule_DDF is null or l_super_status_DDF is null or l_position_occ_DDF is null then
173 hr_utility.set_message(8301, 'GHR_37910_POS_VALIDATE_FAIL');
174 hr_utility.raise_error;
175 end if;
176
177 /* compare values */
178 if l_FLSA_DDF is not null and l_FLSA_table is not null then
179 if l_FLSA_DDF <> l_FLSA_table then
180 hr_utility.set_message(8301, 'GHR_37911_POS_VALIDATE_FAIL');
181 hr_utility.raise_error;
182 end if;
183 end if;
184 if l_position_occ_DDF is not null and l_position_occ_table is not null then
185 if l_position_occ_DDF <> l_position_occ_table then
186 hr_utility.set_message(8301, 'GHR_37912_POS_VALIDATE_FAIL');
187 hr_utility.raise_error;
188 end if;
189 end if;
190 if l_super_status_DDF is not null and l_super_status_table is not null then
191 if l_super_status_DDF <> l_super_status_table then
192 hr_utility.set_message(8301, 'GHR_37913_POS_VALIDATE_FAIL');
193 hr_utility.raise_error;
194 end if;
195 end if;
196 if l_position_sens_DDF is not null and l_position_sens_table is not null then
197 if l_position_sens_DDF <> l_position_sens_table then
198 hr_utility.set_message(8301, 'GHR_37914_POS_VALIDATE_FAIL');
199 hr_utility.raise_error;
200 end if;
201 end if;
202 if l_compet_level_DDF is not null and l_compet_level_table is not null then
203 if l_compet_level_DDF <> l_compet_level_table then
204 hr_utility.set_message(8301, 'GHR_37915_POS_VALIDATE_FAIL');
205 hr_utility.raise_error;
206 end if;
207 end if;
208 if l_working_title_DDF is not null and l_working_title_table is not null then
209 if l_working_title_DDF <> l_working_title_table then
210 hr_utility.set_message(8301, 'GHR_37918_POS_VALIDATE_FAIL');
211 hr_utility.raise_error;
212 end if;
213 end if;
214 if l_valide_grade_DDF is not null and l_pay_plan_table is not null and l_valide_grade_table is not null then
215 if l_valide_grade_DDF <> l_valide_grade_table then
216 hr_utility.set_message(8301, 'GHR_37916_POS_VALIDATE_FAIL');
217 hr_utility.raise_error;
218 end if;
219 end if;
220 if l_occ_series_KF is not null and l_occ_series_table is not null then
221 if l_occ_series_KF <> l_occ_series_table then
222 hr_utility.set_message(8301, 'GHR_37917_POS_VALIDATE_FAIL');
223 hr_utility.raise_error;
224 end if;
225 end if;
226
227 end if;
228 end if;
229
230 -- call GHRWSDPO_AGENCY.AGENCY_CHECK
231 GHR_WSDPO_AGENCY.AGENCY_CHECK (p_position_id);
232
233 exception
234 when no_data_found then
235 null;
236
237 END validate_perwsdpo;
238
239
240
241 -- ---------------------------------------------------------------------------
242 -- |--------------------------< chk_position_obligated >----------------|
243 -- --------------------------------------------------------------------------
244 FUNCTION chk_position_obligated (p_position_id in number
245 ,p_date in date)
246 RETURN BOOLEAN IS
247 --
248 l_chk_position_obligated boolean :=false;
249 l_expire_date PER_POSITION_EXTRA_INFO.POEI_INFORMATION3%type;
250 l_obligate_type PER_POSITION_EXTRA_INFO.POEI_INFORMATION4%type;
251 l_pos_ei_data PER_POSITION_EXTRA_INFO%ROWTYPE;
252
253 BEGIN
254
255 ghr_history_fetch.fetch_positionei (p_position_id => p_position_id
256 ,p_information_type => 'GHR_US_POS_OBLIG'
257 ,p_date_effective => p_date
258 ,p_pos_ei_data => l_pos_ei_data);
259
260 l_expire_date := l_pos_ei_data.POEI_INFORMATION3;
261 l_obligate_type := l_pos_ei_data.POEI_INFORMATION4;
262 if (l_expire_date IS NULL
263 OR fnd_date.canonical_to_date(l_expire_date) >= p_date )
264 and NVL(l_obligate_type,'U') <> 'U' then
265 l_chk_position_obligated :=true;
266 else
267 l_chk_position_obligated :=false;
268 end if;
269
270 return l_chk_position_obligated;
271
272 end chk_position_obligated;
273
274 -- ---------------------------------------------------------------------------
275 -- |--------------------------< chk_PAR_Exists >----------------|
276 -- --------------------------------------------------------------------------
277 FUNCTION chk_par_exists (p_position_id in number) RETURN BOOLEAN IS
278 l_chk_par_exists boolean :=false;
279 l_par_found boolean :=false;
280 l_chk number;
281
282 -- Modified the below cursor to consider only position
283 -- which are waiting for approval in inbox and
284 -- the positions which are already assigned will be
285 -- core validation on PER_ALL_ASSIGNMENTS
286 cursor c_par_exists is
287 select gpr.pa_request_id
288 from GHR_PA_REQUESTS gpr
289 where gpr.to_position_id = p_position_id
290 and pa_notification_id is null
291 and status <> 'CANCELED';
292
293 begin
294
295 FOR c_par_exists_rec IN c_par_exists LOOP
296 l_chk := c_par_exists_rec.pa_request_id;
297 l_par_found := TRUE;
298 exit;
299 END LOOP;
300
301 If l_par_found then
302 l_chk_par_exists := TRUE;
303 End If;
304
305 return l_chk_par_exists;
306
307 end chk_par_exists;
308
309 --Begin bug# 12706943
310 -- ---------------------------------------------------------------------------
311 -- |--------------------------< chk_det_pos_exists >----------------|
312 -- This function is built similar to chk_PAR_Exists. ------------
313 -- It returns true if a given position is on detail ------------
314 -- --------------------------------------------------------------------------
315 FUNCTION chk_det_pos_exists (p_position_id in number) RETURN BOOLEAN IS
316
317 cursor c_det_pos_exists is
318 select assignment_id from per_assignment_extra_info
319 where information_type = 'GHR_US_ASG_DET_INFO'
320 and to_number(aei_information5) = p_position_id;
321
322 begin
323 FOR c_det_pos_exists_rec IN c_det_pos_exists LOOP
324 RETURN(TRUE);
325 END LOOP;
326
327 RETURN(FALSE);
328
329 end chk_det_pos_exists;
330 -- ---------------------------------------------------------------------------
331 -- |--------------------------< chk_detail_next >----------------|
332 -- This function is built similar to chk_det_pos_exists. ------------
333 -- It returns true if a given position is on detail in future ------------
334 -- --------------------------------------------------------------------------
335
336 FUNCTION chk_detail_next (p_position_id in number,
337 p_effective_date in date) RETURN BOOLEAN IS
338
339 cursor c_det_pos_next is
340 select assignment_id from per_assignment_extra_info
341 where information_type = 'GHR_US_ASG_DET_INFO'
342 and to_number(aei_information5) = p_position_id
343 and p_effective_date <= fnd_date.canonical_to_date(aei_information2);
344
345 begin
346 FOR c_det_pos_next_rec IN c_det_pos_next LOOP
347 RETURN(TRUE);
348 END LOOP;
349
350 RETURN(FALSE);
351
352 end chk_detail_next;
353 --Begin bug 13261734
354 FUNCTION chk_det_parei_exists (p_position_id in number) RETURN BOOLEAN IS
355
356 cursor c_parei_exists is
357 select * from ghr_pa_request_extra_info
358 where information_type = 'GHR_US_PAR_DET_INFO'
359 and to_number(rei_information5) = p_position_id;
360
361 begin
362 FOR c_parei_exists_rec IN c_parei_exists LOOP
363 RETURN(TRUE);
364 exit;
365 END LOOP;
366 RETURN(FALSE);
367
368 end chk_det_parei_exists;
369 --End bug 13261734
370 --End bug# 12706943
371
372 -- Start of Bug 3501968
373 -- ---------------------------------------------------------------------------
374 -- |--------------------------< chk_PAR_Exists_f_per >----------------|
375 -- This function is built similar to chk_PAR_Exists. ------------
376 -- It returns true if a given person has atleast one PA request. ------------
377 -- --------------------------------------------------------------------------
378 FUNCTION chk_par_exists_f_per (p_person_id in number) RETURN BOOLEAN IS
379 l_chk_par_exists boolean :=false;
380 l_par_found boolean :=false;
381 l_chk number;
382
383 cursor c_par_exists is
384 select gpr.pa_request_id
385 from GHR_PA_REQUESTS gpr
386 where gpr.person_id = p_person_id;
387
388 begin
389
390 FOR c_par_exists_rec IN c_par_exists LOOP
391 l_chk := c_par_exists_rec.pa_request_id;
392 l_par_found := TRUE;
393 exit;
394 END LOOP;
395
396 If l_par_found then
397 l_chk_par_exists := TRUE;
398 End If;
399
400 return l_chk_par_exists;
401
402 end chk_par_exists_f_per;
403
404 -- End of Bug 3501968.
405
406 -- ---------------------------------------------------------------------------
407 -- |--------------------------< chk_location_assigned >----------------|
408 -- --------------------------------------------------------------------------
409 FUNCTION chk_location_assigned (p_location_id in number) RETURN BOOLEAN IS
410 l_chk_location_assigned boolean :=false;
411 l_count number;
412 begin
413 select count(*) into l_count from PER_ASSIGNMENTS_F assign
414 where assign.location_id=p_location_id and assign.primary_flag='Y'
415 and assign.assignment_type <> 'B';
416 if l_count>0 then
417 l_chk_location_assigned :=true;
418 else
419 l_chk_location_assigned :=false;
420 end if;
421 return l_chk_location_assigned;
422
423 end chk_location_assigned;
424
425 -- ---------------------------------------------------------------------------
426 -- |--------------------------< chk_position_assigned >----------------|
427 -- --------------------------------------------------------------------------
428 FUNCTION chk_position_assigned (p_position_id in number) RETURN BOOLEAN IS
429 l_chk_position_assigned boolean :=false;
430 l_count number;
431 begin
432 select count(*) into l_count from PER_ASSIGNMENTS_F assign
433 where assign.position_id=p_position_id and assign.assignment_type <> 'B';
434 if l_count>0 then
435 l_chk_position_assigned :=true;
436 else
437 l_chk_position_assigned :=false;
438 end if;
439 return l_chk_position_assigned;
440
441 end chk_position_assigned;
442
443 -- ---------------------------------------------------------------------------
444 -- |--------------------------< chk_position_assigned_date >----------------|
445 -- --------------------------------------------------------------------------
446 FUNCTION chk_position_assigned_date (p_position_id in number
447 ,p_date in date)
448 RETURN BOOLEAN IS
449 CURSOR c_asg IS
450 select 1
451 from PER_ASSIGNMENTS_F asg
452 where asg.position_id = p_position_id
453 and asg.assignment_type <> 'B'
454 and NVL(p_date,trunc(sysdate))
455 between asg.effective_start_date and asg.effective_end_date;
456 BEGIN
457 FOR c_asg_rec IN c_asg LOOP
458 RETURN(TRUE);
459 END LOOP;
460
461 RETURN(FALSE);
462
463 END chk_position_assigned_date;
464
465 -- ---------------------------------------------------------------------------
466 -- |--------------------------< chk_position_assigned_other >----------------|
467 -- --------------------------------------------------------------------------
468 FUNCTION chk_position_assigned_other (p_position_id in number
469 ,p_assignment_id in number
470 ,p_date in date)
471 RETURN BOOLEAN IS
472 CURSOR c_asg IS
473 select 1
474 from PER_ASSIGNMENTS_F asg
475 where asg.position_id = p_position_id
476 and asg.assignment_id <> p_assignment_id -- Exclude Current Assignment
477 and asg.assignment_type <> 'B'
478 and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
479 and asg.effective_end_date;
480
481 BEGIN
482 FOR c_asg_rec IN c_asg LOOP
483 RETURN(TRUE);
484 END LOOP;
485
486 RETURN(FALSE);
487
488 END chk_position_assigned_other;
489
490 -- ---------------------------------------------------------------------------
491 -- |--------------------------< chk_position_assigned_cwk >----------------|
492 -- --------------------------------------------------------------------------
493 FUNCTION chk_position_assigned_cwk (p_position_id in number
494 ,p_date in date)
495 RETURN BOOLEAN IS
496 CURSOR c_asg IS
497 select 1
498 from PER_ASSIGNMENTS_F asg
499 where asg.position_id = p_position_id
500 and asg.assignment_type = 'C'
501 and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
502 and asg.effective_end_date;
503
504 BEGIN
505 FOR c_asg_rec IN c_asg LOOP
506 RETURN(TRUE);
507 END LOOP;
508
509 RETURN(FALSE);
510
511 END chk_position_assigned_cwk;
512
513
514
515 -- ---------------------------------------------------------------------------
516 -- |--------------------------< return_upd_hr_vert_status >----------------|
517 -- --------------------------------------------------------------------------
518 FUNCTION return_upd_hr_vert_status RETURN BOOLEAN IS
519 l_proc varchar2(72) := g_package||'return_upd_hr_vert_status' ;
520 begin
521 hr_utility.set_location('Entering:'||l_proc,5);
522 return (nvl(g_bypass_vert, false));
523 hr_utility.set_location(' Leaving:'||l_proc,10);
524 end return_upd_hr_vert_status;
525
526 -- This function checks if there are any future PA Request actions for a given position
527 -- that have been completed.
528 FUNCTION check_pend_future_pars (p_position_id IN NUMBER
529 ,p_effective_date IN DATE)
530 RETURN VARCHAR2 IS
531
532 l_pend_future_list VARCHAR2(2000) := NULL;
533 --
534 CURSOR c_par IS
535 SELECT 'Request Number:'||par.request_number||
536 ', 1st NOA Code:'||par.first_noa_code||
537 DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
538 ', Effective Date:'||par.effective_date||
539 ', Employee_Name:'||per.full_name||
540 ', SSN:'||per.national_identifier||
541 ', Updater:'||prh.user_name list_info
542 FROM per_people_f per
543 ,ghr_pa_routing_history prh
544 ,ghr_pa_requests par
545 WHERE par.to_position_id = p_position_id
546 AND par.effective_date >= p_effective_date
547 AND prh.pa_request_id = par.pa_request_id
548 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
549 FROM ghr_pa_routing_history prh2
550 WHERE prh2.pa_request_id = par.pa_request_id)
551 AND prh.action_taken IN ('FUTURE_ACTION')
552 AND par.person_id = per.person_id
553 AND par.effective_date between per.effective_start_date and per.effective_end_date
554 ORDER BY par.effective_date, par.pa_request_id;
555
556
557 BEGIN
558 -- loop around them all to build up a list
559 FOR c_par_rec IN c_par LOOP
560 l_pend_future_list := SUBSTR(l_pend_future_list||g_new_line||g_new_line||c_par%ROWCOUNT||'.'||c_par_rec.list_info,1,2000);
561 END LOOP;
562
563 RETURN(l_pend_future_list);
564
565 END check_pend_future_pars ;
566
567
568 --
569 -- ---------------------------------------------------------------------------
570 -- |---------------------------< IS_RPA_ELEMENT >----------------------------|
571 -- ---------------------------------------------------------------------------
572 -- {Start of Comments}
573 --
574 -- Description:
575 -- Checks whether element is created through RPA process or
576 -- entered through element entries screen on person form. If element is
577 -- created through RPA, function returns TRUE; Otherwise function returns
578 -- FALSE.
579 --
580 -- Prerequisites:
581 -- p_element_entry_value_id must be provided.
582 --
583 -- In Parameters:
584 -- p_element_entry_value_id
585 --
586 -- Post Success:
587 -- Processing continues.
588 --
589 -- Post Failure:
590 -- None.
591 --
592 -- Developer Implementation Notes:
593 -- None.
594 --
595 -- Access Status:
596 -- All.
597 --
598 -- {End of Comments}
599 -- ---------------------------------------------------------------------------
600 FUNCTION is_rpa_element(p_element_entry_id IN NUMBER)
601 RETURN BOOLEAN AS
602
603 Cursor c_history_element is
604 SELECT 'X' value
605 FROM ghr_pa_history
606 WHERE table_name = ghr_history_api.g_eleent_table
607 -- AND information1 = p_element_entry_id
608 -- Bug #5746242 vmididho modified the above statement for performance
609 AND information1 = to_char(p_element_entry_id)
610 AND pa_request_id is not null;
611
612
613 l_proc VARCHAR2(80) := 'is_rpa_element';
614 BEGIN
615 hr_utility.set_location('Entering '||l_proc,0);
616 hr_utility.set_location('Element entry id = '||to_char(p_element_entry_id),1);
617 FOR C_rec IN c_history_element
618 LOOP
619 IF c_rec.value = 'X' THEN
620 hr_utility.set_location('Leaving '||l_proc,5);
621 Return True;
622 exit;
623 END IF;
624 END LOOP;
625 hr_utility.set_location('Leaving '||l_proc,10);
626 RETURN FALSE;
627
628 END is_rpa_element;
629
630 -- --------------------------------------------------------------------------
631 -- |--------------------------< chk_future_assigned >----------------|
632 -- --------------------------------------------------------------------------
633 FUNCTION chk_future_assigned (p_position_id in number
634 ,p_date in date)
635 RETURN BOOLEAN IS
636 CURSOR c_asg IS
637 select 1
638 from PER_ASSIGNMENTS_F asg
639 where asg.position_id = p_position_id
640 and asg.assignment_type <> 'B'
641 and asg.effective_start_date >= NVL(p_date,trunc(sysdate));
642
643 BEGIN
644 FOR c_asg_rec IN c_asg LOOP
645 RETURN(TRUE);
646 END LOOP;
647
648 RETURN(FALSE);
649
650 END chk_future_assigned;
651
652 -- --------------------------------------------------------------------------
653 -- |--------------------------< chk_rpa_sourced_next>----------------|
654 -- --------------------------------------------------------------------------
655 FUNCTION chk_rpa_sourced_next(p_position_id in number
656 ,p_effective_end_date in date)
657 RETURN BOOLEAN IS
658
659 CURSOR c_phv IS
660 select 1
661 from GHR_POSITIONS_H_V phv
662 where phv.position_id = p_position_id
663 and phv.pa_request_id IS NOT NULL
664 and phv.availability_status_id = 1
665 and phv.effective_start_date = p_effective_end_date+1;
666
667 BEGIN
668
669 FOR c_phv_rec IN c_phv LOOP
670 -- If any rows returned were created by an RPA and the hiring/avail status is 'Active
671 -- then prevent delete from occuring to avoid losing data and getting asg/posn out of sync.
672 RETURN(TRUE);
673 END LOOP;
674
675 RETURN(FALSE);
676
677 END chk_rpa_sourced_next;
678
679 -- --------------------------------------------------------------------------
680 -- |--------------------------< chk_rpa_sourced_all>----------------|
681 -- --------------------------------------------------------------------------
682 FUNCTION chk_rpa_sourced_all(p_position_id in number
683 ,p_effective_end_date in date)
684 RETURN BOOLEAN IS
685
686 CURSOR c_phv IS
687 select 1
688 from GHR_POSITIONS_H_V phv
689 where phv.position_id = p_position_id
690 and phv.pa_request_id IS NOT NULL
691 and phv.availability_status_id = 1
692 and phv.effective_start_date >= p_effective_end_date+1;
693
694 BEGIN
695
696 FOR c_phv_rec IN c_phv LOOP
697 -- If any rows returned were created by an RPA and the hiring/avail status is 'Active
698 -- then prevent delete from occuring to avoid losing data and getting asg/posn out of sync.
699 RETURN(TRUE);
700 END LOOP;
701
702 RETURN(FALSE);
703
704 END chk_rpa_sourced_all;
705
706 -- --------------------------------------------------------------------------
707 -- |-----------------------------< get_position_eff_date>-------------------|
708 -- --------------------------------------------------------------------------
709
710 FUNCTION get_position_eff_date(p_position_id in number)
711 RETURN DATE IS
712
713 CURSOR c_pos IS
714 SELECT date_effective
715 FROM per_all_positions pap
716 WHERE pap.position_id = p_position_id;
717
718 BEGIN
719
720 FOR c_pos_rec IN c_pos LOOP
721 RETURN(c_pos_rec.date_effective);
722 END LOOP;
723
724 END get_position_eff_date;
725
726
727 END ghr_validate_perwsdpo;
[Home] [Help]