1 package body ghr_validate_perwsdpo AS
2 /* $Header: ghrwsdpo.pkb 120.0.12000000.3 2007/10/15 13:11:48 utokachi noship $ */
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 cursor c_par_exists is
283 select gpr.pa_request_id
284 from GHR_PA_REQUESTS gpr
285 where gpr.to_position_id = p_position_id;
286
287 begin
288
289 FOR c_par_exists_rec IN c_par_exists LOOP
290 l_chk := c_par_exists_rec.pa_request_id;
291 l_par_found := TRUE;
292 exit;
293 END LOOP;
294
295 If l_par_found then
296 l_chk_par_exists := TRUE;
297 End If;
298
299 return l_chk_par_exists;
300
301 end chk_par_exists;
302
303 -- Start of Bug 3501968
304 -- ---------------------------------------------------------------------------
305 -- |--------------------------< chk_PAR_Exists_f_per >----------------|
306 -- This function is built similar to chk_PAR_Exists. ------------
307 -- It returns true if a given person has atleast one PA request. ------------
308 -- --------------------------------------------------------------------------
309 FUNCTION chk_par_exists_f_per (p_person_id in number) RETURN BOOLEAN IS
310 l_chk_par_exists boolean :=false;
311 l_par_found boolean :=false;
312 l_chk number;
313
314 cursor c_par_exists is
315 select gpr.pa_request_id
316 from GHR_PA_REQUESTS gpr
317 where gpr.person_id = p_person_id;
318
319 begin
320
321 FOR c_par_exists_rec IN c_par_exists LOOP
322 l_chk := c_par_exists_rec.pa_request_id;
323 l_par_found := TRUE;
324 exit;
325 END LOOP;
326
327 If l_par_found then
328 l_chk_par_exists := TRUE;
329 End If;
330
331 return l_chk_par_exists;
332
333 end chk_par_exists_f_per;
334
335 -- End of Bug 3501968.
336
337 -- ---------------------------------------------------------------------------
338 -- |--------------------------< chk_location_assigned >----------------|
339 -- --------------------------------------------------------------------------
340 FUNCTION chk_location_assigned (p_location_id in number) RETURN BOOLEAN IS
341 l_chk_location_assigned boolean :=false;
342 l_count number;
343 begin
344 select count(*) into l_count from PER_ASSIGNMENTS_F assign
345 where assign.location_id=p_location_id and assign.primary_flag='Y'
346 and assign.assignment_type <> 'B';
347 if l_count>0 then
348 l_chk_location_assigned :=true;
349 else
350 l_chk_location_assigned :=false;
351 end if;
352 return l_chk_location_assigned;
353
354 end chk_location_assigned;
355
356 -- ---------------------------------------------------------------------------
357 -- |--------------------------< chk_position_assigned >----------------|
358 -- --------------------------------------------------------------------------
359 FUNCTION chk_position_assigned (p_position_id in number) RETURN BOOLEAN IS
360 l_chk_position_assigned boolean :=false;
361 l_count number;
362 begin
363 select count(*) into l_count from PER_ASSIGNMENTS_F assign
364 where assign.position_id=p_position_id and assign.assignment_type <> 'B';
365 if l_count>0 then
366 l_chk_position_assigned :=true;
367 else
368 l_chk_position_assigned :=false;
369 end if;
370 return l_chk_position_assigned;
371
372 end chk_position_assigned;
373
374 -- ---------------------------------------------------------------------------
375 -- |--------------------------< chk_position_assigned_date >----------------|
376 -- --------------------------------------------------------------------------
377 FUNCTION chk_position_assigned_date (p_position_id in number
378 ,p_date in date)
379 RETURN BOOLEAN IS
380 CURSOR c_asg IS
381 select 1
382 from PER_ASSIGNMENTS_F asg
383 where asg.position_id = p_position_id
384 and asg.assignment_type <> 'B'
385 and NVL(p_date,trunc(sysdate))
386 between asg.effective_start_date and asg.effective_end_date;
387 BEGIN
388 FOR c_asg_rec IN c_asg LOOP
389 RETURN(TRUE);
390 END LOOP;
391
392 RETURN(FALSE);
393
394 END chk_position_assigned_date;
395
396 -- ---------------------------------------------------------------------------
397 -- |--------------------------< chk_position_assigned_other >----------------|
398 -- --------------------------------------------------------------------------
399 FUNCTION chk_position_assigned_other (p_position_id in number
400 ,p_assignment_id in number
401 ,p_date in date)
402 RETURN BOOLEAN IS
403 CURSOR c_asg IS
404 select 1
405 from PER_ASSIGNMENTS_F asg
406 where asg.position_id = p_position_id
407 and asg.assignment_id <> p_assignment_id -- Exclude Current Assignment
408 and asg.assignment_type <> 'B'
409 and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
410 and asg.effective_end_date;
411
412 BEGIN
413 FOR c_asg_rec IN c_asg LOOP
414 RETURN(TRUE);
415 END LOOP;
416
417 RETURN(FALSE);
418
419 END chk_position_assigned_other;
420
421 -- ---------------------------------------------------------------------------
422 -- |--------------------------< chk_position_assigned_cwk >----------------|
423 -- --------------------------------------------------------------------------
424 FUNCTION chk_position_assigned_cwk (p_position_id in number
425 ,p_date in date)
426 RETURN BOOLEAN IS
427 CURSOR c_asg IS
428 select 1
429 from PER_ASSIGNMENTS_F asg
430 where asg.position_id = p_position_id
431 and asg.assignment_type = 'C'
432 and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
433 and asg.effective_end_date;
434
435 BEGIN
436 FOR c_asg_rec IN c_asg LOOP
437 RETURN(TRUE);
438 END LOOP;
439
440 RETURN(FALSE);
441
442 END chk_position_assigned_cwk;
443
444
445
446 -- ---------------------------------------------------------------------------
447 -- |--------------------------< return_upd_hr_vert_status >----------------|
448 -- --------------------------------------------------------------------------
449 FUNCTION return_upd_hr_vert_status RETURN BOOLEAN IS
450 l_proc varchar2(72) := g_package||'return_upd_hr_vert_status' ;
451 begin
452 hr_utility.set_location('Entering:'||l_proc,5);
453 return (nvl(g_bypass_vert, false));
454 hr_utility.set_location(' Leaving:'||l_proc,10);
455 end return_upd_hr_vert_status;
456
457 -- This function checks if there are any future PA Request actions for a given position
458 -- that have been completed.
459 FUNCTION check_pend_future_pars (p_position_id IN NUMBER
460 ,p_effective_date IN DATE)
461 RETURN VARCHAR2 IS
462
463 l_pend_future_list VARCHAR2(2000) := NULL;
464 --
465 CURSOR c_par IS
466 SELECT 'Request Number:'||par.request_number||
467 ', 1st NOA Code:'||par.first_noa_code||
468 DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
469 ', Effective Date:'||par.effective_date||
470 ', Employee_Name:'||per.full_name||
471 ', SSN:'||per.national_identifier||
472 ', Updater:'||prh.user_name list_info
473 FROM per_people_f per
474 ,ghr_pa_routing_history prh
475 ,ghr_pa_requests par
476 WHERE par.to_position_id = p_position_id
477 AND par.effective_date >= p_effective_date
478 AND prh.pa_request_id = par.pa_request_id
479 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
480 FROM ghr_pa_routing_history prh2
481 WHERE prh2.pa_request_id = par.pa_request_id)
482 AND prh.action_taken IN ('FUTURE_ACTION')
483 AND par.person_id = per.person_id
484 AND par.effective_date between per.effective_start_date and per.effective_end_date
485 ORDER BY par.effective_date, par.pa_request_id;
486
487
488 BEGIN
489 -- loop around them all to build up a list
490 FOR c_par_rec IN c_par LOOP
491 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);
492 END LOOP;
493
494 RETURN(l_pend_future_list);
495
496 END check_pend_future_pars ;
497
498
499 --
500 -- ---------------------------------------------------------------------------
501 -- |---------------------------< IS_RPA_ELEMENT >----------------------------|
502 -- ---------------------------------------------------------------------------
503 -- {Start of Comments}
504 --
505 -- Description:
506 -- Checks whether element is created through RPA process or
507 -- entered through element entries screen on person form. If element is
508 -- created through RPA, function returns TRUE; Otherwise function returns
509 -- FALSE.
510 --
511 -- Prerequisites:
512 -- p_element_entry_value_id must be provided.
513 --
514 -- In Parameters:
515 -- p_element_entry_value_id
516 --
517 -- Post Success:
518 -- Processing continues.
519 --
520 -- Post Failure:
521 -- None.
522 --
523 -- Developer Implementation Notes:
524 -- None.
525 --
526 -- Access Status:
527 -- All.
528 --
529 -- {End of Comments}
530 -- ---------------------------------------------------------------------------
531 FUNCTION is_rpa_element(p_element_entry_id IN NUMBER)
532 RETURN BOOLEAN AS
533
534 Cursor c_history_element is
535 SELECT 'X' value
536 FROM ghr_pa_history
537 WHERE table_name = ghr_history_api.g_eleent_table
538 -- AND information1 = p_element_entry_id
539 -- Bug #5746242 vmididho modified the above statement for performance
540 AND information1 = to_char(p_element_entry_id)
541 AND pa_request_id is not null;
542
543
544 l_proc VARCHAR2(80) := 'is_rpa_element';
545 BEGIN
546 hr_utility.set_location('Entering '||l_proc,0);
547 hr_utility.set_location('Element entry id = '||to_char(p_element_entry_id),1);
548 FOR C_rec IN c_history_element
549 LOOP
550 IF c_rec.value = 'X' THEN
551 hr_utility.set_location('Leaving '||l_proc,5);
552 Return True;
553 exit;
554 END IF;
555 END LOOP;
556 hr_utility.set_location('Leaving '||l_proc,10);
557 RETURN FALSE;
558
559 END is_rpa_element;
560
561 -- --------------------------------------------------------------------------
562 -- |--------------------------< chk_future_assigned >----------------|
563 -- --------------------------------------------------------------------------
564 FUNCTION chk_future_assigned (p_position_id in number
565 ,p_date in date)
566 RETURN BOOLEAN IS
567 CURSOR c_asg IS
568 select 1
569 from PER_ASSIGNMENTS_F asg
570 where asg.position_id = p_position_id
571 and asg.assignment_type <> 'B'
572 and asg.effective_start_date >= NVL(p_date,trunc(sysdate));
573
574 BEGIN
575 FOR c_asg_rec IN c_asg LOOP
576 RETURN(TRUE);
577 END LOOP;
578
579 RETURN(FALSE);
580
581 END chk_future_assigned;
582
583 -- --------------------------------------------------------------------------
584 -- |--------------------------< chk_rpa_sourced_next>----------------|
585 -- --------------------------------------------------------------------------
586 FUNCTION chk_rpa_sourced_next(p_position_id in number
587 ,p_effective_end_date in date)
588 RETURN BOOLEAN IS
589
590 CURSOR c_phv IS
591 select 1
592 from GHR_POSITIONS_H_V phv
593 where phv.position_id = p_position_id
594 and phv.pa_request_id IS NOT NULL
595 and phv.availability_status_id = 1
596 and phv.effective_start_date = p_effective_end_date+1;
597
598 BEGIN
599
600 FOR c_phv_rec IN c_phv LOOP
601 -- If any rows returned were created by an RPA and the hiring/avail status is 'Active
602 -- then prevent delete from occuring to avoid losing data and getting asg/posn out of sync.
603 RETURN(TRUE);
604 END LOOP;
605
606 RETURN(FALSE);
607
608 END chk_rpa_sourced_next;
609
610 -- --------------------------------------------------------------------------
611 -- |--------------------------< chk_rpa_sourced_all>----------------|
612 -- --------------------------------------------------------------------------
613 FUNCTION chk_rpa_sourced_all(p_position_id in number
614 ,p_effective_end_date in date)
615 RETURN BOOLEAN IS
616
617 CURSOR c_phv IS
618 select 1
619 from GHR_POSITIONS_H_V phv
620 where phv.position_id = p_position_id
621 and phv.pa_request_id IS NOT NULL
622 and phv.availability_status_id = 1
623 and phv.effective_start_date >= p_effective_end_date+1;
624
625 BEGIN
626
627 FOR c_phv_rec IN c_phv LOOP
628 -- If any rows returned were created by an RPA and the hiring/avail status is 'Active
629 -- then prevent delete from occuring to avoid losing data and getting asg/posn out of sync.
630 RETURN(TRUE);
631 END LOOP;
632
633 RETURN(FALSE);
634
635 END chk_rpa_sourced_all;
636
637 -- --------------------------------------------------------------------------
638 -- |-----------------------------< get_position_eff_date>-------------------|
639 -- --------------------------------------------------------------------------
640
641 FUNCTION get_position_eff_date(p_position_id in number)
642 RETURN DATE IS
643
644 CURSOR c_pos IS
645 SELECT date_effective
646 FROM per_all_positions pap
647 WHERE pap.position_id = p_position_id;
648
649 BEGIN
650
651 FOR c_pos_rec IN c_pos LOOP
652 RETURN(c_pos_rec.date_effective);
653 END LOOP;
654
655 END get_position_eff_date;
656
657
658 END ghr_validate_perwsdpo;
[Home] [Help]