1 PACKAGE BODY hri_edw_fct_wrk_actvty AS
2 /* $Header: hriefwac.pkb 120.0 2005/05/29 07:10:23 appldev noship $ */
3
4
5 /*****************************************************************************/
6 /* Checks that assignment change reason is an employee change reason. When */
7 /* new applicants with applicant assignment change reasons are hired the */
8 /* applicant change reason gets updated into the employee assignment and */
9 /* causes errors when it tries to be resolved as an employee change reason */
10 /*****************************************************************************/
11 FUNCTION check_reason( p_change_reason IN VARCHAR2,
12 p_instance IN VARCHAR2 )
13 RETURN VARCHAR2 IS
14
15 l_reason_fk VARCHAR2(80); -- Reason foreign key
16 l_reason_code VARCHAR2(30); -- Reason code
17
18 CURSOR reason_cur IS
19 SELECT lookup_code
20 FROM hr_lookups
21 WHERE lookup_code = p_change_reason
22 AND lookup_type = 'EMP_ASSIGN_REASON';
23
24 BEGIN
25
26 OPEN reason_cur;
27 FETCH reason_cur INTO l_reason_code;
28 CLOSE reason_cur;
29
30 IF (l_reason_code IS NULL) THEN
31 RETURN 'NA_EDW';
32 ELSE
33 l_reason_fk := 'EMP_ASSIGN_REASON-' || l_reason_code || '-' || p_instance;
34 RETURN l_reason_fk;
35 END IF;
36
37 RETURN 'NA_EDW';
38
39 END check_reason;
40
41 /*****************************************************************************/
42 /* Returns the start date of the period of service current on the date given */
43 /*****************************************************************************/
44 FUNCTION get_hire_days( p_person_id IN NUMBER,
45 p_effective_date IN DATE )
46 RETURN DATE
47 IS
48
49 l_hire_date DATE; -- Holds most recent start date
50
51 /* Selects the start date for the effective period of service */
52 CURSOR hire_cur IS
53 SELECT MAX(date_start)
54 FROM per_periods_of_service
55 WHERE person_id = p_person_id
56 AND date_start <= p_effective_date;
57
58 BEGIN
59
60 /* Retrieve the result from the cursor */
61 OPEN hire_cur;
62 FETCH hire_cur INTO l_hire_date;
63 CLOSE hire_cur;
64
65 RETURN l_hire_date;
66
67 END get_hire_days;
68
69 /******************************************************************************/
70 /* Returns the number of days since the last organization change from the */
71 /* date given. If no such change previously occurred then a null value is */
72 /* returned */
73 /******************************************************************************/
74 FUNCTION get_days_to_last_org_x( p_assignment_id IN NUMBER,
75 p_person_id IN NUMBER,
76 p_change_date IN DATE )
77 RETURN NUMBER
78 IS
79
80 l_last_change_date DATE; -- Holds last change date
81 l_days_to_last_change NUMBER; -- Converts to number of days
82
83 /* Gets the change date prior to the given change date when an */
84 /* organization change occurred */
85 CURSOR last_org_cur IS
86 SELECT MAX(asg2.effective_start_date)
87 FROM
88 per_all_assignments_f asg1
89 ,per_all_assignments_f asg2
90 WHERE
91 asg1.assignment_id = p_assignment_id
92 AND asg2.assignment_id = p_assignment_id
93 AND asg1.effective_end_date + 1 = asg2.effective_start_date
94 AND asg1.organization_id <> asg2.organization_id
95 AND asg2.effective_start_date < p_change_date;
96
97 /* Get assignment creation date */
98 CURSOR asg_start_cur IS
99 SELECT MIN(asg.effective_start_date)
100 FROM per_all_assignments_f asg
101 WHERE asg.assignment_id = p_assignment_id
102 AND asg.assignment_type = 'E';
103
104 BEGIN
105
106 /* Gets result from cursor */
107 OPEN last_org_cur;
108 FETCH last_org_cur INTO l_last_change_date;
109 CLOSE last_org_cur;
110 /* If no change previously occurred use assignment start date */
111 IF (l_last_change_date IS NULL) THEN
112 OPEN asg_start_cur;
113 FETCH asg_start_cur INTO l_last_change_date;
114 CLOSE asg_start_cur;
115 /* If change is assignment start, then return null */
116 IF (l_last_change_date = p_change_date) THEN
117 RETURN to_number(null);
118 END IF;
119 END IF;
120
121 /* Convert to days */
122 l_days_to_last_change := p_change_date - l_last_change_date;
123
124 RETURN l_days_to_last_change;
125
126 END get_days_to_last_org_x;
127
128 /******************************************************************************/
129 /* Returns the number of days since the last job change from the given date. */
130 /* If no such change previously occurred then a null value is returned */
131 /******************************************************************************/
132 FUNCTION get_days_to_last_job_x( p_assignment_id IN NUMBER,
133 p_person_id IN NUMBER,
134 p_change_date IN DATE )
135 RETURN NUMBER
136 IS
137
138 l_last_change_date DATE; -- Holds last change date
139 l_days_to_last_change NUMBER; -- Converts to number of days
140
141 /* Gets the change date prior to the given change date when an */
142 /* job change occurred */
143 CURSOR last_job_cur IS
144 SELECT MAX(asg2.effective_start_date)
145 FROM
146 per_all_assignments_f asg1
147 ,per_all_assignments_f asg2
148 WHERE
149 asg1.assignment_id = p_assignment_id
150 AND asg2.assignment_id = p_assignment_id
151 AND asg1.effective_end_date + 1 = asg2.effective_start_date
152 AND NVL(asg1.job_id, -1) <> NVL(asg2.job_id, -1)
153 AND asg2.effective_start_date < p_change_date;
154
155 /* Get assignment creation date */
156 CURSOR asg_start_cur IS
157 SELECT MIN(asg.effective_start_date)
158 FROM per_all_assignments_f asg
159 WHERE asg.assignment_id = p_assignment_id
160 AND asg.assignment_type = 'E';
161
162 BEGIN
163
164 /* Gets result from cursor */
165 OPEN last_job_cur;
166 FETCH last_job_cur INTO l_last_change_date;
167 CLOSE last_job_cur;
168 /* If no change previously occurred use assignment start date */
169 IF (l_last_change_date IS NULL) THEN
170 OPEN asg_start_cur;
171 FETCH asg_start_cur INTO l_last_change_date;
172 CLOSE asg_start_cur;
173 /* If change is assignment start, then return null */
174 IF (l_last_change_date = p_change_date) THEN
175 RETURN to_number(null);
176 END IF;
177 END IF;
178
179 /* Convert to days */
180 l_days_to_last_change := p_change_date - l_last_change_date;
181
182 RETURN l_days_to_last_change;
183
184 END get_days_to_last_job_x;
185
186 /******************************************************************************/
187 /* Returns the number of days since the last position change from the date */
188 /* given. If no such change previously occurred then a null value is returned */
189 /******************************************************************************/
190 FUNCTION get_days_to_last_pos_x( p_assignment_id IN NUMBER,
191 p_person_id IN NUMBER,
192 p_change_date IN DATE )
193 RETURN NUMBER
194 IS
195
196 l_last_change_date DATE; -- Holds last change date
197 l_days_to_last_change NUMBER; -- Converts to number of days
198
199 /* Gets the change date prior to the given change date when an */
200 /* position change occurred */
201 CURSOR last_pos_cur IS
202 SELECT MAX(asg2.effective_start_date)
203 FROM
204 per_all_assignments_f asg1
205 ,per_all_assignments_f asg2
206 WHERE
207 asg1.assignment_id = p_assignment_id
208 AND asg2.assignment_id = p_assignment_id
209 AND asg1.effective_end_date + 1 = asg2.effective_start_date
210 AND NVL(asg1.position_id, -1) <> NVL(asg2.position_id, -1)
211 AND asg2.effective_start_date < p_change_date;
212
213 /* Get assignment creation date */
214 CURSOR asg_start_cur IS
215 SELECT MIN(asg.effective_start_date)
216 FROM per_all_assignments_f asg
217 WHERE asg.assignment_id = p_assignment_id
218 AND asg.assignment_type = 'E';
219
220 BEGIN
221
222 /* Gets result from cursor */
223 OPEN last_pos_cur;
224 FETCH last_pos_cur INTO l_last_change_date;
225 CLOSE last_pos_cur;
226 /* If no change previously occurred use assignment start date */
227 IF (l_last_change_date IS NULL) THEN
228 OPEN asg_start_cur;
229 FETCH asg_start_cur INTO l_last_change_date;
230 CLOSE asg_start_cur;
231 /* If change is assignment start, then return null */
232 IF (l_last_change_date = p_change_date) THEN
233 RETURN to_number(null);
234 END IF;
235 END IF;
236
237 /* Convert to days */
238 l_days_to_last_change := p_change_date - l_last_change_date;
239
240 RETURN l_days_to_last_change;
241
242 END get_days_to_last_pos_x;
243
244 /******************************************************************************/
245 /* Returns the number of days since the last grade change from the date given */
246 /* If no such change previously occurred then a null value is returned. */
247 /******************************************************************************/
248 FUNCTION get_days_to_last_grd_x( p_assignment_id IN NUMBER,
249 p_person_id IN NUMBER,
250 p_change_date IN DATE )
251 RETURN NUMBER
252 IS
253
254 l_last_change_date DATE; -- Holds last change date
255 l_days_to_last_change NUMBER; -- Converts to number of days
256
257 /* Gets the change date prior to the given change date when an */
258 /* grade change occurred */
259 CURSOR last_grd_cur IS
260 SELECT MAX(asg2.effective_start_date)
261 FROM
262 per_all_assignments_f asg1
263 ,per_all_assignments_f asg2
264 WHERE
265 asg1.assignment_id = p_assignment_id
266 AND asg2.assignment_id = p_assignment_id
267 AND asg1.effective_end_date + 1 = asg2.effective_start_date
268 AND NVL(asg1.grade_id, -1) <> NVL(asg2.grade_id, -1)
269 AND asg2.effective_start_date < p_change_date;
270
271 /* Get assignment creation date */
272 CURSOR asg_start_cur IS
273 SELECT MIN(asg.effective_start_date)
274 FROM per_all_assignments_f asg
275 WHERE asg.assignment_id = p_assignment_id
276 AND asg.assignment_type = 'E';
277
278 BEGIN
279
280 /* Gets result from cursor */
281 OPEN last_grd_cur;
282 FETCH last_grd_cur INTO l_last_change_date;
283 CLOSE last_grd_cur;
284 /* If no change previously occurred use assignment start date */
285 IF (l_last_change_date IS NULL) THEN
286 OPEN asg_start_cur;
287 FETCH asg_start_cur INTO l_last_change_date;
288 CLOSE asg_start_cur;
289 /* If change is assignment start, then return null */
290 IF (l_last_change_date = p_change_date) THEN
291 RETURN to_number(null);
292 END IF;
293 END IF;
294
295 /* Convert to days */
296 l_days_to_last_change := p_change_date - l_last_change_date;
297
298 RETURN l_days_to_last_change;
299
300 END get_days_to_last_grd_x;
301
302 /******************************************************************************/
303 /* Returns the number of days since the last geography change from the date */
304 /* given. If no such change previously occurred then a null value is returned */
305 /******************************************************************************/
306 FUNCTION get_days_to_last_geog_x( p_assignment_id IN NUMBER,
307 p_person_id IN NUMBER,
308 p_change_date IN DATE )
309 RETURN NUMBER
310 IS
311
312 l_last_change_date DATE; -- Holds last change date
313 l_days_to_last_change NUMBER; -- Converts to number of days
314
315 /* Gets the change date prior to the given change date when an */
316 /* geography change occurred */
317 CURSOR last_geog_cur IS
318 SELECT MAX(asg2.effective_start_date)
319 FROM
320 per_all_assignments_f asg1
321 ,per_all_assignments_f asg2
322 WHERE
323 asg1.assignment_id = p_assignment_id
324 AND asg2.assignment_id = p_assignment_id
325 AND asg1.effective_end_date + 1 = asg2.effective_start_date
326 AND NVL(asg1.location_id, -1) <> NVL(asg2.location_id, -1)
327 AND asg2.effective_start_date < p_change_date;
328
329 /* Get assignment creation date */
330 CURSOR asg_start_cur IS
331 SELECT MIN(asg.effective_start_date)
332 FROM per_all_assignments_f asg
333 WHERE asg.assignment_id = p_assignment_id
334 AND asg.assignment_type = 'E';
335
336 BEGIN
337
338 /* Gets result from cursor */
339 OPEN last_geog_cur;
340 FETCH last_geog_cur INTO l_last_change_date;
341 CLOSE last_geog_cur;
342 /* If no change previously occurred use assignment start date */
343 IF (l_last_change_date IS NULL) THEN
344 OPEN asg_start_cur;
345 FETCH asg_start_cur INTO l_last_change_date;
346 CLOSE asg_start_cur;
347 /* If change is assignment start, then return null */
348 IF (l_last_change_date = p_change_date) THEN
349 RETURN to_number(null);
350 END IF;
351 END IF;
352
353 /* Convert to days */
354 l_days_to_last_change := p_change_date - l_last_change_date;
355
356 RETURN l_days_to_last_change;
357
358 END get_days_to_last_geog_x;
359
360 END hri_edw_fct_wrk_actvty;
[Home] [Help]