1 PACKAGE BODY PER_RU_PREVIOUS_EMPLOYER as
2 /* $Header: perupemp.pkb 120.1 2006/09/20 14:34:33 mgettins noship $ */
3 PROCEDURE CREATE_RU_PREVIOUS_EMPLOYER(P_BUSINESS_GROUP_ID NUMBER
4 ,P_PERSON_ID NUMBER
5 ,P_START_DATE DATE
6 ,P_END_DATE DATE
7 ,P_PEM_INFORMATION_CATEGORY VARCHAR2) is
8 CURSOR c_prev_employer_list is
9 select 'x' from PER_PREVIOUS_EMPLOYERS
10 WHERE business_group_id = p_business_group_id
11 AND person_id = p_person_id
12 AND (start_date BETWEEN P_START_DATE AND P_END_DATE
13 OR end_date BETWEEN P_START_DATE AND P_END_DATE
14 OR P_START_DATE BETWEEN start_date and end_date
15 OR P_END_DATE BETWEEN start_date and end_date);
16
17 CURSOR c_prev_job_list is
18 select 'x' from per_previous_jobs ppj, per_previous_employers ppe
19 where ppe.business_group_id=p_business_group_id
20 AND ppe.person_id=p_person_id
21 AND ppe.PREVIOUS_EMPLOYER_ID=ppj.PREVIOUS_EMPLOYER_ID
22 AND (ppj.START_DATE between p_start_date and p_end_date
23 OR ppj.end_date between p_start_date and p_end_date
24 OR P_START_DATE BETWEEN ppj.start_date and ppj.end_date
25 OR P_END_DATE BETWEEN ppj.start_date and ppj.end_date);
26
27 CURSOR c_current_employer IS
28 select 'x' from per_all_assignments_f a, hr_soft_coding_keyflex s
29 where a.business_group_id=p_business_group_id
30 and a.person_id=p_person_id
31 and a.assignment_status_type_id <> '3'
32 and a.SOFT_CODING_KEYFLEX_ID = s.SOFT_CODING_KEYFLEX_ID(+)
33 and (nvl(segment2, 'N') = 'N' OR a.SOFT_CODING_KEYFLEX_ID IS NULL)
34 and (effective_start_date between p_start_date and p_end_date
35 OR effective_end_date between p_start_date and p_end_date
36 OR p_start_date between effective_start_date and effective_end_date
37 OR p_end_date between effective_start_date and effective_end_date);
38
39 v_dummy VARCHAR2(1);
40 BEGIN
41 --
42 -- Added for GSI Bug 5472781
43 --
44 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
45 --
46 --IF P_PEM_INFORMATION_CATEGORY='RU' THEN
47 OPEN c_prev_employer_list;
48 FETCH c_prev_employer_list INTO v_dummy;
49 IF c_prev_employer_list%FOUND THEN
50 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
51 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
52 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
53 hr_utility.raise_error;
54 END IF;
55 CLOSE c_prev_employer_list;
56
57 OPEN c_prev_job_list;
58 FETCH c_prev_job_list INTO v_dummy;
59 IF c_prev_job_list%FOUND THEN
60 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
61 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
62 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
63 hr_utility.raise_error;
64 END IF;
65 CLOSE c_prev_job_list;
66
67 OPEN c_current_employer;
68 FETCH c_current_employer INTO v_dummy;
69 IF c_current_employer%FOUND THEN
70 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
71 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
72 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
73 hr_utility.raise_error;
74 END IF;
75 CLOSE c_current_employer;
76
77 END IF;
78 END CREATE_RU_PREVIOUS_EMPLOYER;
79
80 PROCEDURE UPDATE_RU_PREVIOUS_EMPLOYER(P_PREVIOUS_EMPLOYER_ID NUMBER
81 ,P_START_DATE DATE
82 ,P_END_DATE DATE
83 ,P_PEM_INFORMATION_CATEGORY VARCHAR2) is
84 CURSOR c_prev_employer_details is
85 SELECT 'x' FROM PER_PREVIOUS_EMPLOYERS WHERE
86 (person_id,business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers WHERE previous_employer_id=p_previous_employer_id)
87 AND previous_employer_id <> P_PREVIOUS_EMPLOYER_ID
88 AND (start_date BETWEEN P_START_DATE AND P_END_DATE
89 OR end_date BETWEEN P_START_DATE AND P_END_DATE
90 OR P_START_DATE BETWEEN start_date and end_date
91 OR P_END_DATE BETWEEN start_date and end_date);
92
93 CURSOR c_prev_job_details is
94 SELECT 'x' FROM PER_PREVIOUS_EMPLOYERS ppe, per_previous_jobs ppj WHERE
95 (ppe.person_id,ppe.business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers WHERE previous_employer_id=p_previous_employer_id)
96 AND ppe.previous_employer_id=ppj.previous_employer_id
97 AND (ppj.start_date BETWEEN P_START_DATE AND P_END_DATE
98 OR ppj.end_date BETWEEN P_START_DATE AND P_END_DATE
99 OR P_START_DATE BETWEEN ppj.start_date and ppj.end_date
100 OR P_END_DATE BETWEEN ppj.start_date and ppj.end_date);
101
102 CURSOR c_current_employer IS
103 select 'x' from per_all_assignments_f a, hr_soft_coding_keyflex s
104 where (person_id,business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers WHERE previous_employer_id=p_previous_employer_id)
105 and assignment_status_type_id <> '3'
106 and a.SOFT_CODING_KEYFLEX_ID = s.SOFT_CODING_KEYFLEX_ID(+)
107 and (nvl(segment2, 'N') = 'N' OR a.SOFT_CODING_KEYFLEX_ID IS NULL)
108 and (effective_start_date between p_start_date and p_end_date
109 OR effective_end_date between p_start_date and p_end_date
110 OR p_start_date between effective_start_date and effective_end_date
111 OR p_end_date between effective_start_date and effective_end_date);
112
113
114 v_dummy VARCHAR2(1);
115 BEGIN
116 --
117 -- Added for GSI Bug 5472781
118 --
119 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
120 --
121 OPEN c_prev_employer_details;
122 FETCH c_prev_employer_details INTO v_dummy;
123 IF c_prev_employer_details%FOUND THEN
124 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
125 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
126 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
127 hr_utility.raise_error;
128 END IF;
129 CLOSE c_prev_employer_details;
130
131 OPEN c_prev_job_details;
132 FETCH c_prev_job_details INTO v_dummy;
133 IF c_prev_job_details%FOUND THEN
134 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
135 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
136 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
137 hr_utility.raise_error;
138 END IF;
139 CLOSE c_prev_job_details;
140
141 OPEN c_current_employer;
142 FETCH c_current_employer INTO v_dummy;
143 IF c_current_employer%FOUND THEN
144 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
145 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
146 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
147 hr_utility.raise_error;
148 END IF;
149 CLOSE c_current_employer;
150 --
151 END IF;
152 END UPDATE_RU_PREVIOUS_EMPLOYER;
153
154 PROCEDURE CREATE_RU_PREVIOUS_JOB(P_PREVIOUS_EMPLOYER_ID NUMBER
155 ,P_START_DATE DATE
156 ,P_END_DATE DATE) is
157
158
159 CURSOR c_get_job is
160 select 'x' from per_previous_jobs ppj, per_previous_employers ppe
161 where (ppe.person_id,ppe.business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers WHERE previous_employer_id=p_previous_employer_id)
162 AND ppe.previous_employer_id = ppj.previous_employer_id (+)
163
164 AND (ppj.START_DATE between p_start_date and p_end_date
165 OR ppj.end_date between p_start_date and p_end_date
166 OR ppe.START_DATE between p_start_date and p_end_date
167 OR ppe.end_date between p_start_date and p_end_date
168 OR p_start_date between ppj.START_DATE and ppj.END_DATE
169 OR p_end_date between ppj.START_DATE and ppj.END_DATE
170 OR p_start_date between ppe.START_DATE and ppe.END_DATE
171 OR p_end_date between ppe.START_DATE and ppe.END_DATE );
172
173 CURSOR c_current_employer IS
174 select 'x' from per_all_assignments_f a, hr_soft_coding_keyflex s
175 where (person_id,business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers WHERE previous_employer_id=p_previous_employer_id)
176 and assignment_status_type_id <> '3'
177 and a.SOFT_CODING_KEYFLEX_ID = s.SOFT_CODING_KEYFLEX_ID(+)
178 and (nvl(segment2, 'N') = 'N' OR a.SOFT_CODING_KEYFLEX_ID IS NULL)
179 and (effective_start_date between p_start_date and p_end_date
180 OR effective_end_date between p_start_date and p_end_date
181 OR p_start_date between effective_start_date and effective_end_date
182 OR p_end_date between effective_start_date and effective_end_date);
183
184 v_dummy varchar2(1);
185 BEGIN
186 --
187 -- Added for GSI Bug 5472781
188 --
189 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
190 --
191 -- hr_utility.trace_on('Y','Russia');
192
193 -- hr_utility.trace('St Date : ' || p_start_date);
194 -- hr_utility.trace('End Date : ' || p_end_date);
195
196 open c_get_job;
197 FETCH c_get_job into v_dummy;
198 -- hr_utility.trace('v_dummy : ' || v_dummy);
199 IF c_get_job%FOUND then
200 -- hr_utility.trace('Inside IF');
201 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
202 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
203 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
204 hr_utility.raise_error;
205 END IF;
206 CLOSE c_get_job;
207
208 OPEN c_current_employer;
209 FETCH c_current_employer INTO v_dummy;
210 IF c_current_employer%FOUND THEN
211 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
212 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
213 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
214 hr_utility.raise_error;
215 END IF;
216 CLOSE c_current_employer;
217 END IF;
218 END CREATE_RU_PREVIOUS_JOB;
219
220 PROCEDURE UPDATE_RU_PREVIOUS_JOB(P_PREVIOUS_JOB_ID NUMBER
221 ,P_START_DATE DATE
222 ,P_END_DATE DATE) is
223
224 CURSOR c_job_details is
225 SELECT 'x' FROM PER_PREVIOUS_EMPLOYERS ppe, per_previous_jobs ppj WHERE
226 (ppe.person_id,ppe.business_group_id) =(SELECT person_id,business_group_id FROM per_previous_employers pee,per_previous_jobs ppj
227 WHERE ppj.previous_job_id=p_previous_job_id and ppj.previous_employer_id = pee.previous_employer_id)
228 AND ppe.previous_employer_id = ppj.previous_employer_id (+)
229 AND ppj.previous_job_id <> p_previous_job_id
230 AND (ppj.start_date BETWEEN P_START_DATE AND P_END_DATE
231 OR ppj.end_date BETWEEN P_START_DATE AND P_END_DATE
232 OR ppe.start_date BETWEEN P_START_DATE AND P_END_DATE
233 OR ppe.end_date BETWEEN P_START_DATE AND P_END_DATE
234 OR P_START_DATE BETWEEN ppj.start_date and ppj.end_date
235 OR P_END_DATE BETWEEN ppj.start_date and ppj.end_date
236 OR P_START_DATE BETWEEN ppe.start_date and ppe.end_date
237 OR P_END_DATE BETWEEN ppe.start_date and ppe.end_date);
238
239 CURSOR c_current_employer IS
240 select 'x' from per_all_assignments_f a, hr_soft_coding_keyflex s
241 where (person_id, business_group_id) = (SELECT person_id,business_group_id FROM per_previous_employers pee,per_previous_jobs ppj
242 WHERE ppj.previous_job_id=p_previous_job_id and ppj.previous_employer_id = pee.previous_employer_id)
243 and assignment_status_type_id <> '3'
244 and a.SOFT_CODING_KEYFLEX_ID = s.SOFT_CODING_KEYFLEX_ID(+)
245 and (nvl(segment2, 'N') = 'N' OR a.SOFT_CODING_KEYFLEX_ID IS NULL)
246 and (effective_start_date between p_start_date and p_end_date
247 OR effective_end_date between p_start_date and p_end_date
248 OR p_start_date between effective_start_date and effective_end_date
249 OR p_end_date between effective_start_date and effective_end_date);
250
251 v_dummy varchar2(1);
252 BEGIN
253 --
254 -- Added for GSI Bug 5472781
255 --
256 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
257 --
258 OPEN c_job_details;
259 FETCH c_job_details into v_dummy;
260 IF c_job_details%FOUND THEN
261 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
262 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
263 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
264 hr_utility.raise_error;
265 END IF;
266 CLOSE c_job_details;
267 OPEN c_current_employer;
268 FETCH c_current_employer INTO v_dummy;
269 IF c_current_employer%FOUND THEN
270 hr_utility.set_message(800,'HR_RU_OVERLAPPING_DATES');
271 hr_utility.set_message_token('STARTDATE', to_char(p_start_date, 'DD-MON-YYYY'));
272 hr_utility.set_message_token('ENDDATE', to_char(p_end_date, 'DD-MON-YYYY'));
273 hr_utility.raise_error;
274 END IF;
275 CLOSE c_current_employer;
276 END IF;
277 END UPDATE_RU_PREVIOUS_JOB;
278
279 END PER_RU_PREVIOUS_EMPLOYER;
[Home] [Help]