[Home] [Help]
PACKAGE BODY: APPS.GHR_POSITION_COPY
Source
1 PACKAGE BODY ghr_position_copy AS
2 /* $Header: ghrwspoc.pkb 120.0.12010000.2 2009/05/26 10:59:16 vmididho noship $ */
3 --
4 FUNCTION get_seq_location (p_org_id IN NUMBER)
5 RETURN VARCHAR2 IS
6 --
7 CURSOR c_ori IS
8 SELECT ori.org_information4 seq_location
9 FROM hr_organization_information ori
10 WHERE ori.organization_id = p_org_id
11 AND ori.org_information_context = 'GHR_US_ORG_INFORMATION';
12 --
13 BEGIN
14 FOR c_ori_rec IN c_ori LOOP
15 RETURN (c_ori_rec.seq_location);
16 END LOOP;
17 RETURN(NULL);
18 END get_seq_location;
19 --
20 --
21 ------------------------------------------------------------------
22 FUNCTION get_max_seq (p_seq_location IN VARCHAR2
23 ,p_segment1 IN VARCHAR2
24 ,p_segment2 IN VARCHAR2
25 ,p_segment3 IN VARCHAR2
26 ,p_segment4 IN VARCHAR2
27 ,p_segment5 IN VARCHAR2
28 ,p_segment6 IN VARCHAR2
29 ,p_segment7 IN VARCHAR2
30 ,p_segment8 IN VARCHAR2
31 ,p_segment9 IN VARCHAR2
32 ,p_segment10 IN VARCHAR2
33 ,p_segment11 IN VARCHAR2
34 ,p_segment12 IN VARCHAR2
35 ,p_segment13 IN VARCHAR2
36 ,p_segment14 IN VARCHAR2
37 ,p_segment15 IN VARCHAR2
38 ,p_segment16 IN VARCHAR2
39 ,p_segment17 IN VARCHAR2
40 ,p_segment18 IN VARCHAR2
41 ,p_segment19 IN VARCHAR2
42 ,p_segment20 IN VARCHAR2
43 ,p_segment21 IN VARCHAR2
44 ,p_segment22 IN VARCHAR2
45 ,p_segment23 IN VARCHAR2
46 ,p_segment24 IN VARCHAR2
47 ,p_segment25 IN VARCHAR2
48 ,p_segment26 IN VARCHAR2
49 ,p_segment27 IN VARCHAR2
50 ,p_segment28 IN VARCHAR2
51 ,p_segment29 IN VARCHAR2
52 ,p_segment30 IN VARCHAR2)
53 RETURN VARCHAR2 IS
54 l_cur INTEGER;
55 l_stmt VARCHAR2(2000);
56 l_fetch_rows INTEGER;
57 l_seq_loc INTEGER;
58 l_max_seq VARCHAR2(150);
59
60 BEGIN
61
62 l_cur := dbms_sql.open_cursor;
63 l_stmt := 'SELECT MAX(TO_NUMBER('||p_seq_location||')) max_seq '||
64 'FROM per_position_definitions pde WHERE 1=1';
65 --
66 l_seq_loc := SUBSTR(p_seq_location,8);
67 --
68 FOR i IN 1..30 LOOP
69 IF l_seq_loc <> i THEN
70 IF i = 1 and p_segment1 IS NOT NULL THEN
71 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment1||'''';
72 ELSIF i = 2 and p_segment2 IS NOT NULL THEN
73 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment2||'''';
74 ELSIF i = 3 and p_segment3 IS NOT NULL THEN
75 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment3||'''';
76 ELSIF i = 4 and p_segment4 IS NOT NULL THEN
77 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment4||'''';
78 ELSIF i = 5 and p_segment5 IS NOT NULL THEN
79 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment5||'''';
80 ELSIF i = 6 and p_segment6 IS NOT NULL THEN
81 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment6||'''';
82 ELSIF i = 7 and p_segment7 IS NOT NULL THEN
83 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment7||'''';
84 ELSIF i = 8 and p_segment8 IS NOT NULL THEN
85 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment8||'''';
86 ELSIF i = 9 and p_segment9 IS NOT NULL THEN
87 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment9||'''';
88 ELSIF i = 10 and p_segment10 IS NOT NULL THEN
89 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment10||'''';
90 ELSIF i = 11 and p_segment11 IS NOT NULL THEN
91 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment11||'''';
92 ELSIF i = 12 and p_segment12 IS NOT NULL THEN
93 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment12||'''';
94 ELSIF i = 13 and p_segment13 IS NOT NULL THEN
95 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment13||'''';
96 ELSIF i = 14 and p_segment14 IS NOT NULL THEN
97 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment14||'''';
98 ELSIF i = 15 and p_segment15 IS NOT NULL THEN
99 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment15||'''';
100 ELSIF i = 16 and p_segment16 IS NOT NULL THEN
101 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment16||'''';
102 ELSIF i = 17 and p_segment17 IS NOT NULL THEN
103 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment17||'''';
104 ELSIF i = 18 and p_segment18 IS NOT NULL THEN
105 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment18||'''';
106 ELSIF i = 19 and p_segment19 IS NOT NULL THEN
107 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment19||'''';
108 ELSIF i = 20 and p_segment20 IS NOT NULL THEN
109 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment20||'''';
110 ELSIF i = 21 and p_segment21 IS NOT NULL THEN
111 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment21||'''';
112 ELSIF i = 22 and p_segment22 IS NOT NULL THEN
113 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment22||'''';
114 ELSIF i = 23 and p_segment23 IS NOT NULL THEN
115 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment23||'''';
116 ELSIF i = 24 and p_segment24 IS NOT NULL THEN
117 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment24||'''';
118 ELSIF i = 25 and p_segment25 IS NOT NULL THEN
119 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment25||'''';
120 ELSIF i = 26 and p_segment26 IS NOT NULL THEN
121 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment26||'''';
122 ELSIF i = 27 and p_segment27 IS NOT NULL THEN
123 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment27||'''';
124 ELSIF i = 28 and p_segment28 IS NOT NULL THEN
125 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment28||'''';
126 ELSIF i = 29 and p_segment29 IS NOT NULL THEN
127 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment29||'''';
128 ELSIF i = 30 and p_segment30 IS NOT NULL THEN
129 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||p_segment30||'''';
130 END IF;
131 END IF;
132 END LOOP;
133 l_stmt := l_stmt||' AND EXISTS (SELECT 1 FROM per_positions pos'
134 ||' WHERE pde.position_definition_id = pos.position_definition_id)';
135 ---
136 --dbms_output.put_line(substr(l_stmt,1,100));
137 --dbms_output.put_line(substr(l_stmt,101,100));
138 dbms_sql.parse(l_cur, l_stmt, dbms_sql.v7);
139 dbms_sql.define_column(l_cur, 1, l_max_seq,150);
140 l_fetch_rows := dbms_sql.execute(l_cur);
141 l_fetch_rows := dbms_sql.fetch_rows(l_cur);
142 dbms_sql.column_value(l_cur, 1, l_max_seq);
143 dbms_sql.close_cursor(l_cur);
144 --
145 RETURN(l_max_seq);
146 --
147 END get_max_seq;
148 --
149
150 PROCEDURE create_posn (p_pos_id IN NUMBER
151 ,p_effective_date_from IN DATE
152 ,p_effective_date_to IN DATE
153 ,p_template_flag IN VARCHAR2
154 ,p_new_pos_id OUT NOCOPY NUMBER
155 ,p_new_pos_name OUT NOCOPY VARCHAR2
156 ,p_ovn OUT NOCOPY NUMBER) IS
157 --
158 l_pos_data hr_all_positions_f%rowtype;
159 l_result_code VARCHAR2(30);
160 l_pde_data per_position_definitions%rowtype;
161 l_dummy_number NUMBER;
162 l_new_pos_id hr_all_positions_f.position_id%TYPE;
163 l_new_pos_name hr_all_positions_f.name%TYPE;
164 l_seq_segment_name VARCHAR2(30);
165 l_new_seq VARCHAR2(150);
166 l_ovn NUMBER;
167 l_seq_len NUMBER;
168 --
169 CURSOR cur_pde (p_position_definition_id IN NUMBER) IS
170 SELECT *
171 FROM per_position_definitions pde
172 WHERE pde.position_definition_id = p_position_definition_id;
173
174 BEGIN
175
176 ghr_history_fetch.fetch_position (p_position_id => p_pos_id
177 ,p_date_effective => p_effective_date_from
178 ,p_position_data => l_pos_data
179 ,p_result_code => l_result_code) ;
180
181 FOR cur_pde_rec IN cur_pde(l_pos_data.position_definition_id) LOOP
182 l_pde_data := cur_pde_rec;
183 END LOOP;
184 --
185 l_seq_segment_name := ghr_position_copy.get_seq_location(l_pos_data.business_group_id);
186 --
187 -- Get Agency Specified Max Seq
188 --
189 l_new_seq := ghr_agency_position_copy.agency_get_max_seq(
190 l_seq_segment_name,
191 l_pde_data.segment1,
192 l_pde_data.segment2,
193 l_pde_data.segment3,
194 l_pde_data.segment4,
195 l_pde_data.segment5,
196 l_pde_data.segment6,
197 l_pde_data.segment7,
198 l_pde_data.segment8,
199 l_pde_data.segment9,
200 l_pde_data.segment10,
201 l_pde_data.segment11,
202 l_pde_data.segment12,
203 l_pde_data.segment13,
204 l_pde_data.segment14,
205 l_pde_data.segment15,
206 l_pde_data.segment16,
207 l_pde_data.segment17,
208 l_pde_data.segment18,
209 l_pde_data.segment19,
210 l_pde_data.segment20,
211 l_pde_data.segment21,
212 l_pde_data.segment22,
213 l_pde_data.segment23,
214 l_pde_data.segment24,
215 l_pde_data.segment25,
216 l_pde_data.segment26,
217 l_pde_data.segment27,
218 l_pde_data.segment28,
219 l_pde_data.segment29,
220 l_pde_data.segment30);
221
222
223 -- If Agency_get_max returns null then call GHR function
224 If l_new_seq is null then
225 l_new_seq := ghr_position_copy.get_max_seq(
226 l_seq_segment_name,
227 l_pde_data.segment1,
228 l_pde_data.segment2,
229 l_pde_data.segment3,
230 l_pde_data.segment4,
231 l_pde_data.segment5,
232 l_pde_data.segment6,
233 l_pde_data.segment7,
234 l_pde_data.segment8,
235 l_pde_data.segment9,
236 l_pde_data.segment10,
237 l_pde_data.segment11,
238 l_pde_data.segment12,
239 l_pde_data.segment13,
240 l_pde_data.segment14,
241 l_pde_data.segment15,
242 l_pde_data.segment16,
243 l_pde_data.segment17,
244 l_pde_data.segment18,
245 l_pde_data.segment19,
246 l_pde_data.segment20,
247 l_pde_data.segment21,
248 l_pde_data.segment22,
249 l_pde_data.segment23,
250 l_pde_data.segment24,
251 l_pde_data.segment25,
252 l_pde_data.segment26,
253 l_pde_data.segment27,
254 l_pde_data.segment28,
255 l_pde_data.segment29,
256 l_pde_data.segment30);
257 End If;
258
259 -- Concatenating a negative symbol infront of sequence for template
260 -- means that a new combination's max number may be the negative number
261 -- of the template combination. This must be reset.
262
263 If l_new_seq is null or to_number(l_new_seq) < 0 then
264 l_new_seq := '0';
265 End If;
266
267 l_new_seq := to_char(((to_number(l_new_seq)) + 1));
268
269 -- Need the length of the original seq. to lpad new seq.
270 -- If seq. is template record then 1 character must be subtracted.
271 IF l_seq_segment_name = 'SEGMENT1' THEN
272 If substr(l_pde_data.segment1,1,1) = '-' then
273 l_seq_len := length(l_pde_data.segment1)-1;
274 Else
275 l_seq_len := length(l_pde_data.segment1);
276 End If;
277 ELSIF l_seq_segment_name = 'SEGMENT2' THEN
278 If substr(l_pde_data.segment2,1,1) = '-' then
279 l_seq_len := length(l_pde_data.segment2)-1;
280 Else
281 l_seq_len := length(l_pde_data.segment2);
282 End If;
283 ELSIF l_seq_segment_name = 'SEGMENT3' THEN
284 If substr(l_pde_data.segment3,1,1) = '-' then
285 l_seq_len := length(l_pde_data.segment3)-1;
286 Else
287 l_seq_len := length(l_pde_data.segment3);
288 End If;
289 ELSIF l_seq_segment_name = 'SEGMENT4' THEN
290 If substr(l_pde_data.segment4,1,1) = '-' then
291 l_seq_len := length(l_pde_data.segment4)-1;
292 Else
293 l_seq_len := length(l_pde_data.segment4);
294 End If;
295 ELSIF l_seq_segment_name = 'SEGMENT5' THEN
296 If substr(l_pde_data.segment5,1,1) = '-' then
297 l_seq_len := length(l_pde_data.segment5)-1;
298 Else
299 l_seq_len := length(l_pde_data.segment5);
300 End If;
301 ELSIF l_seq_segment_name = 'SEGMENT6' THEN
302 If substr(l_pde_data.segment6,1,1) = '-' then
303 l_seq_len := length(l_pde_data.segment6)-1;
304 Else
305 l_seq_len := length(l_pde_data.segment6);
306 End If;
307 ELSIF l_seq_segment_name = 'SEGMENT7' THEN
308 If substr(l_pde_data.segment7,1,1) = '-' then
309 l_seq_len := length(l_pde_data.segment7)-1;
310 Else
311 l_seq_len := length(l_pde_data.segment7);
312 End If;
313 ELSIF l_seq_segment_name = 'SEGMENT8' THEN
314 If substr(l_pde_data.segment8,1,1) = '-' then
315 l_seq_len := length(l_pde_data.segment8)-1;
316 Else
317 l_seq_len := length(l_pde_data.segment8);
318 End If;
319 ELSIF l_seq_segment_name = 'SEGMENT9' THEN
320 If substr(l_pde_data.segment9,1,1) = '-' then
321 l_seq_len := length(l_pde_data.segment9)-1;
322 Else
323 l_seq_len := length(l_pde_data.segment9);
324 End If;
325 ELSIF l_seq_segment_name = 'SEGMENT10' THEN
326 If substr(l_pde_data.segment10,1,1) = '-' then
327 l_seq_len := length(l_pde_data.segment10)-1;
328 Else
329 l_seq_len := length(l_pde_data.segment10);
330 End If;
331 ELSIF l_seq_segment_name = 'SEGMENT11' THEN
332 If substr(l_pde_data.segment11,1,1) = '-' then
333 l_seq_len := length(l_pde_data.segment11)-1;
334 Else
335 l_seq_len := length(l_pde_data.segment11);
336 End If;
337 ELSIF l_seq_segment_name = 'SEGMENT12' THEN
338 If substr(l_pde_data.segment12,1,1) = '-' then
339 l_seq_len := length(l_pde_data.segment12)-1;
340 Else
341 l_seq_len := length(l_pde_data.segment12);
342 End If;
343 ELSIF l_seq_segment_name = 'SEGMENT13' THEN
344 If substr(l_pde_data.segment13,1,1) = '-' then
345 l_seq_len := length(l_pde_data.segment13)-1;
346 Else
347 l_seq_len := length(l_pde_data.segment13);
348 End If;
349 ELSIF l_seq_segment_name = 'SEGMENT14' THEN
350 If substr(l_pde_data.segment14,1,1) = '-' then
351 l_seq_len := length(l_pde_data.segment14)-1;
352 Else
353 l_seq_len := length(l_pde_data.segment14);
354 End If;
355 ELSIF l_seq_segment_name = 'SEGMENT15' THEN
356 If substr(l_pde_data.segment15,1,1) = '-' then
357 l_seq_len := length(l_pde_data.segment15)-1;
358 Else
359 l_seq_len := length(l_pde_data.segment15);
360 End If;
361 ELSIF l_seq_segment_name = 'SEGMENT16' THEN
362 If substr(l_pde_data.segment16,1,1) = '-' then
363 l_seq_len := length(l_pde_data.segment16)-1;
364 Else
365 l_seq_len := length(l_pde_data.segment16);
366 End If;
367 ELSIF l_seq_segment_name = 'SEGMENT17' THEN
368 If substr(l_pde_data.segment17,1,1) = '-' then
369 l_seq_len := length(l_pde_data.segment17)-1;
370 Else
371 l_seq_len := length(l_pde_data.segment17);
372 End If;
373 ELSIF l_seq_segment_name = 'SEGMENT18' THEN
374 If substr(l_pde_data.segment18,1,1) = '-' then
375 l_seq_len := length(l_pde_data.segment18)-1;
376 Else
377 l_seq_len := length(l_pde_data.segment18);
378 End If;
379 ELSIF l_seq_segment_name = 'SEGMENT19' THEN
380 If substr(l_pde_data.segment19,1,1) = '-' then
381 l_seq_len := length(l_pde_data.segment19)-1;
382 Else
383 l_seq_len := length(l_pde_data.segment19);
384 End If;
385 ELSIF l_seq_segment_name = 'SEGMENT20' THEN
386 If substr(l_pde_data.segment20,1,1) = '-' then
387 l_seq_len := length(l_pde_data.segment20)-1;
388 Else
389 l_seq_len := length(l_pde_data.segment20);
390 End If;
391 ELSIF l_seq_segment_name = 'SEGMENT21' THEN
392 If substr(l_pde_data.segment21,1,1) = '-' then
393 l_seq_len := length(l_pde_data.segment21)-1;
394 Else
395 l_seq_len := length(l_pde_data.segment21);
396 End If;
397 ELSIF l_seq_segment_name = 'SEGMENT22' THEN
398 If substr(l_pde_data.segment22,1,1) = '-' then
399 l_seq_len := length(l_pde_data.segment22)-1;
400 Else
401 l_seq_len := length(l_pde_data.segment22);
402 End If;
403 ELSIF l_seq_segment_name = 'SEGMENT23' THEN
404 If substr(l_pde_data.segment23,1,1) = '-' then
405 l_seq_len := length(l_pde_data.segment23)-1;
406 Else
407 l_seq_len := length(l_pde_data.segment23);
408 End If;
409 ELSIF l_seq_segment_name = 'SEGMENT24' THEN
410 If substr(l_pde_data.segment24,1,1) = '-' then
411 l_seq_len := length(l_pde_data.segment24)-1;
412 Else
413 l_seq_len := length(l_pde_data.segment24);
414 End If;
415 ELSIF l_seq_segment_name = 'SEGMENT25' THEN
416 If substr(l_pde_data.segment25,1,1) = '-' then
417 l_seq_len := length(l_pde_data.segment25)-1;
418 Else
419 l_seq_len := length(l_pde_data.segment25);
420 End If;
421 ELSIF l_seq_segment_name = 'SEGMENT26' THEN
422 If substr(l_pde_data.segment26,1,1) = '-' then
423 l_seq_len := length(l_pde_data.segment26)-1;
424 Else
425 l_seq_len := length(l_pde_data.segment26);
426 End If;
427 ELSIF l_seq_segment_name = 'SEGMENT27' THEN
428 If substr(l_pde_data.segment27,1,1) = '-' then
429 l_seq_len := length(l_pde_data.segment27)-1;
430 Else
431 l_seq_len := length(l_pde_data.segment27);
432 End If;
433 ELSIF l_seq_segment_name = 'SEGMENT28' THEN
434 If substr(l_pde_data.segment28,1,1) = '-' then
435 l_seq_len := length(l_pde_data.segment28)-1;
436 Else
437 l_seq_len := length(l_pde_data.segment28);
438 End If;
439 ELSIF l_seq_segment_name = 'SEGMENT29' THEN
440 If substr(l_pde_data.segment29,1,1) = '-' then
441 l_seq_len := length(l_pde_data.segment29)-1;
442 Else
443 l_seq_len := length(l_pde_data.segment29);
444 End If;
445 ELSIF l_seq_segment_name = 'SEGMENT30' THEN
446 If substr(l_pde_data.segment30,1,1) = '-' then
447 l_seq_len := length(l_pde_data.segment30)-1;
448 Else
449 l_seq_len := length(l_pde_data.segment30);
450 End If;
451 END IF;
452 --
453
454 If l_seq_len > length(l_new_seq) then
455 l_new_seq := lpad(l_new_seq,l_seq_len,'0');
456 End If;
457
458 --
459 IF p_template_flag = 'N' THEN
460 IF l_seq_segment_name = 'SEGMENT1' THEN
461 l_pde_data.segment1 := l_new_seq;
462 ELSIF l_seq_segment_name = 'SEGMENT2' THEN
463 l_pde_data.segment2 := l_new_seq;
464 ELSIF l_seq_segment_name = 'SEGMENT3' THEN
465 l_pde_data.segment3 := l_new_seq;
466 ELSIF l_seq_segment_name = 'SEGMENT4' THEN
467 l_pde_data.segment4 := l_new_seq;
468 ELSIF l_seq_segment_name = 'SEGMENT5' THEN
469 l_pde_data.segment5 := l_new_seq;
470 ELSIF l_seq_segment_name = 'SEGMENT6' THEN
471 l_pde_data.segment6 := l_new_seq;
472 ELSIF l_seq_segment_name = 'SEGMENT7' THEN
473 l_pde_data.segment7 := l_new_seq;
474 ELSIF l_seq_segment_name = 'SEGMENT8' THEN
475 l_pde_data.segment8 := l_new_seq;
476 ELSIF l_seq_segment_name = 'SEGMENT9' THEN
477 l_pde_data.segment9 := l_new_seq;
478 ELSIF l_seq_segment_name = 'SEGMENT10' THEN
479 l_pde_data.segment10 := l_new_seq;
480 ELSIF l_seq_segment_name = 'SEGMENT11' THEN
481 l_pde_data.segment11 := l_new_seq;
482 ELSIF l_seq_segment_name = 'SEGMENT12' THEN
483 l_pde_data.segment12 := l_new_seq;
484 ELSIF l_seq_segment_name = 'SEGMENT13' THEN
485 l_pde_data.segment13 := l_new_seq;
486 ELSIF l_seq_segment_name = 'SEGMENT14' THEN
487 l_pde_data.segment14 := l_new_seq;
488 ELSIF l_seq_segment_name = 'SEGMENT15' THEN
489 l_pde_data.segment15 := l_new_seq;
490 ELSIF l_seq_segment_name = 'SEGMENT16' THEN
491 l_pde_data.segment16 := l_new_seq;
492 ELSIF l_seq_segment_name = 'SEGMENT17' THEN
493 l_pde_data.segment17 := l_new_seq;
494 ELSIF l_seq_segment_name = 'SEGMENT18' THEN
495 l_pde_data.segment18 := l_new_seq;
496 ELSIF l_seq_segment_name = 'SEGMENT19' THEN
497 l_pde_data.segment19 := l_new_seq;
498 ELSIF l_seq_segment_name = 'SEGMENT20' THEN
499 l_pde_data.segment20 := l_new_seq;
500 ELSIF l_seq_segment_name = 'SEGMENT21' THEN
501 l_pde_data.segment21 := l_new_seq;
502 ELSIF l_seq_segment_name = 'SEGMENT22' THEN
503 l_pde_data.segment22 := l_new_seq;
504 ELSIF l_seq_segment_name = 'SEGMENT23' THEN
505 l_pde_data.segment23 := l_new_seq;
506 ELSIF l_seq_segment_name = 'SEGMENT24' THEN
507 l_pde_data.segment24 := l_new_seq;
508 ELSIF l_seq_segment_name = 'SEGMENT25' THEN
509 l_pde_data.segment25 := l_new_seq;
510 ELSIF l_seq_segment_name = 'SEGMENT26' THEN
511 l_pde_data.segment26 := l_new_seq;
512 ELSIF l_seq_segment_name = 'SEGMENT27' THEN
513 l_pde_data.segment27 := l_new_seq;
514 ELSIF l_seq_segment_name = 'SEGMENT28' THEN
515 l_pde_data.segment28 := l_new_seq;
516 ELSIF l_seq_segment_name = 'SEGMENT29' THEN
517 l_pde_data.segment29 := l_new_seq;
518 ELSIF l_seq_segment_name = 'SEGMENT30' THEN
519 l_pde_data.segment30 := l_new_seq;
520 END IF;
521 ELSE -- Hard Code Sequence Number for Template Record
522 -- This ensures there is no gap generated in seq for copies.
523 IF l_seq_segment_name = 'SEGMENT1' THEN
524 l_pde_data.segment1 := '-'||l_pde_data.segment1;
525 ELSIF l_seq_segment_name = 'SEGMENT2' THEN
526 l_pde_data.segment2 := '-'||l_pde_data.segment2;
527 ELSIF l_seq_segment_name = 'SEGMENT3' THEN
528 l_pde_data.segment3 := '-'||l_pde_data.segment3;
529 ELSIF l_seq_segment_name = 'SEGMENT4' THEN
530 l_pde_data.segment4 := '-'||l_pde_data.segment4;
531 ELSIF l_seq_segment_name = 'SEGMENT5' THEN
532 l_pde_data.segment5 := '-'||l_pde_data.segment5;
533 ELSIF l_seq_segment_name = 'SEGMENT6' THEN
534 l_pde_data.segment6 := '-'||l_pde_data.segment6;
535 ELSIF l_seq_segment_name = 'SEGMENT7' THEN
536 l_pde_data.segment7 := '-'||l_pde_data.segment7;
537 ELSIF l_seq_segment_name = 'SEGMENT8' THEN
538 l_pde_data.segment8 := '-'||l_pde_data.segment8;
539 ELSIF l_seq_segment_name = 'SEGMENT9' THEN
540 l_pde_data.segment9 := '-'||l_pde_data.segment9;
541 ELSIF l_seq_segment_name = 'SEGMENT10' THEN
542 l_pde_data.segment10 := '-'||l_pde_data.segment10;
543 ELSIF l_seq_segment_name = 'SEGMENT11' THEN
544 l_pde_data.segment11 := '-'||l_pde_data.segment11;
545 ELSIF l_seq_segment_name = 'SEGMENT12' THEN
546 l_pde_data.segment12 := '-'||l_pde_data.segment12;
547 ELSIF l_seq_segment_name = 'SEGMENT13' THEN
548 l_pde_data.segment13 := '-'||l_pde_data.segment13;
549 ELSIF l_seq_segment_name = 'SEGMENT14' THEN
550 l_pde_data.segment14 := '-'||l_pde_data.segment14;
551 ELSIF l_seq_segment_name = 'SEGMENT15' THEN
552 l_pde_data.segment15 := '-'||l_pde_data.segment15;
553 ELSIF l_seq_segment_name = 'SEGMENT16' THEN
554 l_pde_data.segment16 := '-'||l_pde_data.segment16;
555 ELSIF l_seq_segment_name = 'SEGMENT17' THEN
556 l_pde_data.segment17 := '-'||l_pde_data.segment17;
557 ELSIF l_seq_segment_name = 'SEGMENT18' THEN
558 l_pde_data.segment18 := '-'||l_pde_data.segment18;
559 ELSIF l_seq_segment_name = 'SEGMENT19' THEN
560 l_pde_data.segment19 := '-'||l_pde_data.segment19;
561 ELSIF l_seq_segment_name = 'SEGMENT20' THEN
562 l_pde_data.segment20 := '-'||l_pde_data.segment20;
563 ELSIF l_seq_segment_name = 'SEGMENT21' THEN
564 l_pde_data.segment21 := '-'||l_pde_data.segment21;
565 ELSIF l_seq_segment_name = 'SEGMENT22' THEN
566 l_pde_data.segment22 := '-'||l_pde_data.segment22;
567 ELSIF l_seq_segment_name = 'SEGMENT23' THEN
568 l_pde_data.segment23 := '-'||l_pde_data.segment23;
569 ELSIF l_seq_segment_name = 'SEGMENT24' THEN
570 l_pde_data.segment24 := '-'||l_pde_data.segment24;
571 ELSIF l_seq_segment_name = 'SEGMENT25' THEN
572 l_pde_data.segment25 := '-'||l_pde_data.segment25;
573 ELSIF l_seq_segment_name = 'SEGMENT26' THEN
574 l_pde_data.segment26 := '-'||l_pde_data.segment26;
575 ELSIF l_seq_segment_name = 'SEGMENT27' THEN
576 l_pde_data.segment27 := '-'||l_pde_data.segment27;
577 ELSIF l_seq_segment_name = 'SEGMENT28' THEN
578 l_pde_data.segment28 := '-'||l_pde_data.segment28;
579 ELSIF l_seq_segment_name = 'SEGMENT29' THEN
580 l_pde_data.segment29 := '-'||l_pde_data.segment29;
581 ELSIF l_seq_segment_name = 'SEGMENT30' THEN
582 l_pde_data.segment30 := '-'||l_pde_data.segment30;
583 END IF;
584 END IF;
585
586 -- Set Status to Invalid for all copies
587 l_pos_data.status := 'INVALID';
588 --
589
590 ghr_position_api.create_position(
591 p_validate => FALSE
592 ,p_job_id => l_pos_data.job_id
593 ,p_organization_id => l_pos_data.organization_id
594 ,p_date_effective => p_effective_date_to
595 ,p_successor_position_id => l_pos_data.successor_position_id
596 ,p_relief_position_id => l_pos_data.relief_position_id
597 ,p_location_id => l_pos_data.location_id
598 ,p_comments => l_pos_data.comments
599 ,p_date_end => null
600 ,p_frequency => l_pos_data.frequency
601 ,p_probation_period => l_pos_data.probation_period
602 ,p_probation_period_units => l_pos_data.probation_period_unit_cd
603 ,p_replacement_required_flag => l_pos_data.replacement_required_flag
604 ,p_time_normal_finish => l_pos_data.time_normal_finish
605 ,p_time_normal_start => l_pos_data.time_normal_start
606 ,p_status => l_pos_data.status
607 ,p_working_hours => l_pos_data.working_hours
608 ,p_attribute_category => l_pos_data.attribute_category
609 ,p_attribute1 => l_pos_data.attribute1
610 ,p_attribute2 => l_pos_data.attribute2
611 ,p_attribute3 => l_pos_data.attribute3
612 ,p_attribute4 => l_pos_data.attribute4
613 ,p_attribute5 => l_pos_data.attribute5
614 ,p_attribute6 => l_pos_data.attribute6
615 ,p_attribute7 => l_pos_data.attribute7
616 ,p_attribute8 => l_pos_data.attribute8
617 ,p_attribute9 => l_pos_data.attribute9
618 ,p_attribute10 => l_pos_data.attribute10
619 ,p_attribute11 => l_pos_data.attribute11
620 ,p_attribute12 => l_pos_data.attribute12
621 ,p_attribute13 => l_pos_data.attribute13
622 ,p_attribute14 => l_pos_data.attribute14
623 ,p_attribute15 => l_pos_data.attribute15
624 ,p_attribute16 => l_pos_data.attribute16
625 ,p_attribute17 => l_pos_data.attribute17
626 ,p_attribute18 => l_pos_data.attribute18
627 ,p_attribute19 => l_pos_data.attribute19
628 ,p_attribute20 => l_pos_data.attribute20
629 ,p_segment1 => l_pde_data.segment1
630 ,p_segment2 => l_pde_data.segment2
631 ,p_segment3 => l_pde_data.segment3
632 ,p_segment4 => l_pde_data.segment4
633 ,p_segment5 => l_pde_data.segment5
634 ,p_segment6 => l_pde_data.segment6
635 ,p_segment7 => l_pde_data.segment7
636 ,p_segment8 => l_pde_data.segment8
637 ,p_segment9 => l_pde_data.segment9
638 ,p_segment10 => l_pde_data.segment10
639 ,p_segment11 => l_pde_data.segment11
640 ,p_segment12 => l_pde_data.segment12
641 ,p_segment13 => l_pde_data.segment13
642 ,p_segment14 => l_pde_data.segment14
643 ,p_segment15 => l_pde_data.segment15
644 ,p_segment16 => l_pde_data.segment16
645 ,p_segment17 => l_pde_data.segment17
646 ,p_segment18 => l_pde_data.segment18
647 ,p_segment19 => l_pde_data.segment19
648 ,p_segment20 => l_pde_data.segment20
649 ,p_segment21 => l_pde_data.segment21
650 ,p_segment22 => l_pde_data.segment22
651 ,p_segment23 => l_pde_data.segment23
652 ,p_segment24 => l_pde_data.segment24
653 ,p_segment25 => l_pde_data.segment25
654 ,p_segment26 => l_pde_data.segment26
655 ,p_segment27 => l_pde_data.segment27
656 ,p_segment28 => l_pde_data.segment28
657 ,p_segment29 => l_pde_data.segment29
658 ,p_segment30 => l_pde_data.segment30
659 ,p_position_id => l_new_pos_id
660 ,p_object_version_number => l_ovn
661 ,p_position_definition_id => l_dummy_number
662 ,p_name => l_new_pos_name
663 );
664
665 p_new_pos_id := l_new_pos_id;
666 p_new_pos_name := l_new_pos_name;
667 p_ovn := l_ovn;
668
669 END create_posn;
670
671 --
672 -- Given a from position id this procedure will create ALL the extra info
673 -- details associated with the from position id onto the to position id
674 -- For position copy we will explicity exclude types:
675 -- GHR_US_POS_MASS_ACTIONS
676 -- GHR_US_POS_OBLIG
677 PROCEDURE create_all_posn_ei (p_pos_id_from IN NUMBER
678 ,p_effective_date_from IN DATE
679 ,p_pos_id_to IN NUMBER
680 ,p_effective_date_to IN DATE) IS
681 CURSOR cur_pit IS
682 SELECT pit.information_type
683 FROM per_position_info_types pit
684 WHERE pit.information_type NOT IN ('GHR_US_POS_MASS_ACTIONS'
685 ,'GHR_US_POS_OBLIG');
686 BEGIN
687 FOR cur_pit_rec IN cur_pit LOOP
688 ghr_position_copy.create_posn_ei(p_pos_id_from
689 ,p_effective_date_from
690 ,p_pos_id_to
691 ,p_effective_date_to
692 ,cur_pit_rec.information_type);
693 END LOOP;
694 END create_all_posn_ei;
695 --
696 -- Given a from position id and information type this procedure will create the extra info
697 -- details associated with the from position id onto the to position id
698 PROCEDURE create_posn_ei (p_pos_id_from IN NUMBER
699 ,p_effective_date_from IN DATE
700 ,p_pos_id_to IN NUMBER
701 ,p_effective_date_to IN DATE
702 ,p_info_type IN VARCHAR2) IS
703 --
704 l_pos_ei_data per_position_extra_info%rowtype;
705 l_dummy_number NUMBER;
706 l_result_code VARCHAR2(30);
707 --
708 CURSOR cur_poi IS
709 SELECT poi.position_extra_info_id
710 FROM per_position_extra_info poi
711 WHERE poi.information_type = p_info_type
712 AND poi.position_id = p_pos_id_from;
713 --
714 BEGIN
715 -- loops to handle multi_occurrences
716 FOR cur_poi_rec IN cur_poi LOOP
717 -- Fetch from history
718 ghr_history_fetch.fetch_positionei (
719 p_position_extra_info_id => cur_poi_rec.position_extra_info_id
720 ,p_date_effective => p_effective_date_from
721 ,p_posei_data => l_pos_ei_data
722 ,p_result_code => l_result_code);
723 --
724 -- Now create it against the to position
725 --
726 ghr_position_extra_info_api.create_position_extra_info
727 (p_validate => FALSE
728 ,p_position_id => p_pos_id_to
729 ,p_information_type => p_info_type
730 ,p_effective_date => p_effective_date_to
731 ,p_poei_attribute_category => l_pos_ei_data.poei_attribute_category
732 ,p_poei_attribute1 => l_pos_ei_data.poei_attribute1
733 ,p_poei_attribute2 => l_pos_ei_data.poei_attribute2
734 ,p_poei_attribute3 => l_pos_ei_data.poei_attribute3
735 ,p_poei_attribute4 => l_pos_ei_data.poei_attribute4
736 ,p_poei_attribute5 => l_pos_ei_data.poei_attribute5
737 ,p_poei_attribute6 => l_pos_ei_data.poei_attribute6
738 ,p_poei_attribute7 => l_pos_ei_data.poei_attribute7
739 ,p_poei_attribute8 => l_pos_ei_data.poei_attribute8
740 ,p_poei_attribute9 => l_pos_ei_data.poei_attribute9
741 ,p_poei_attribute10 => l_pos_ei_data.poei_attribute10
742 ,p_poei_attribute11 => l_pos_ei_data.poei_attribute11
743 ,p_poei_attribute12 => l_pos_ei_data.poei_attribute12
744 ,p_poei_attribute13 => l_pos_ei_data.poei_attribute13
745 ,p_poei_attribute14 => l_pos_ei_data.poei_attribute14
746 ,p_poei_attribute15 => l_pos_ei_data.poei_attribute15
747 ,p_poei_attribute16 => l_pos_ei_data.poei_attribute16
748 ,p_poei_attribute17 => l_pos_ei_data.poei_attribute17
749 ,p_poei_attribute18 => l_pos_ei_data.poei_attribute18
750 ,p_poei_attribute19 => l_pos_ei_data.poei_attribute19
751 ,p_poei_attribute20 => l_pos_ei_data.poei_attribute20
752 ,p_poei_information_category => l_pos_ei_data.poei_information_category
753 ,p_poei_information1 => l_pos_ei_data.poei_information1
754 ,p_poei_information2 => l_pos_ei_data.poei_information2
755 ,p_poei_information3 => l_pos_ei_data.poei_information3
756 ,p_poei_information4 => l_pos_ei_data.poei_information4
757 ,p_poei_information5 => l_pos_ei_data.poei_information5
758 ,p_poei_information6 => l_pos_ei_data.poei_information6
759 ,p_poei_information7 => l_pos_ei_data.poei_information7
760 ,p_poei_information8 => l_pos_ei_data.poei_information8
761 ,p_poei_information9 => l_pos_ei_data.poei_information9
762 ,p_poei_information10 => l_pos_ei_data.poei_information10
763 ,p_poei_information11 => l_pos_ei_data.poei_information11
764 ,p_poei_information12 => l_pos_ei_data.poei_information12
765 ,p_poei_information13 => l_pos_ei_data.poei_information13
766 ,p_poei_information14 => l_pos_ei_data.poei_information14
767 ,p_poei_information15 => l_pos_ei_data.poei_information15
768 ,p_poei_information16 => l_pos_ei_data.poei_information16
769 ,p_poei_information17 => l_pos_ei_data.poei_information17
770 ,p_poei_information18 => l_pos_ei_data.poei_information18
771 ,p_poei_information19 => l_pos_ei_data.poei_information19
772 ,p_poei_information20 => l_pos_ei_data.poei_information20
773 ,p_poei_information21 => l_pos_ei_data.poei_information21
774 ,p_poei_information22 => l_pos_ei_data.poei_information22
775 ,p_poei_information23 => l_pos_ei_data.poei_information23
776 ,p_poei_information24 => l_pos_ei_data.poei_information24
777 ,p_poei_information25 => l_pos_ei_data.poei_information25
778 ,p_poei_information26 => l_pos_ei_data.poei_information26
779 ,p_poei_information27 => l_pos_ei_data.poei_information27
780 ,p_poei_information28 => l_pos_ei_data.poei_information28
781 ,p_poei_information29 => l_pos_ei_data.poei_information29
782 ,p_poei_information30 => l_pos_ei_data.poei_information30
783 ,p_position_extra_info_id => l_dummy_number
784 ,p_object_version_number => l_dummy_number);
785
786 END LOOP;
787 --
788 END create_posn_ei;
789
790 --
791 -- Given a position id this function will create a position record
792 -- and its associated details (currently just EI) and pass back the new position id
793 PROCEDURE create_full_posn (p_pos_id IN NUMBER
794 ,p_effective_date_from IN DATE
795 ,p_effective_date_to IN DATE
796 ,p_template_flag IN VARCHAR2
797 ,p_new_pos_id OUT NOCOPY NUMBER
798 ,p_new_pos_name OUT NOCOPY VARCHAR2
799 ,p_ovn OUT NOCOPY NUMBER) IS
800 --
801 l_new_pos_id per_positions.position_id%TYPE;
802 l_new_pos_name per_positions.name%TYPE;
803
804 BEGIN
805
806 create_posn(p_pos_id
807 ,p_effective_date_from
808 ,p_effective_date_to
809 ,p_template_flag
810 ,l_new_pos_id
811 ,l_new_pos_name
812 ,p_ovn);
813 --
814 create_all_posn_ei(p_pos_id
815 ,p_effective_date_from
816 ,l_new_pos_id
817 ,p_effective_date_to);
818 --
819 p_new_pos_id := l_new_pos_id;
820 p_new_pos_name := l_new_pos_name;
821
822 END create_full_posn;
823
824 --
825 -- Update the Template Position's Org and Job
826 --
827 FUNCTION update_position (p_pos_id IN NUMBER
828 ,p_new_org_id IN NUMBER
829 ,p_new_job_id IN NUMBER)
830 RETURN NUMBER IS
831
832 l_ovn number;
833
834 cursor c_get_ovn is
835 select object_version_number
836 from per_positions
837 where position_id = p_pos_id;
838
839 BEGIN
840
841 Update per_positions
842 set
843 organization_id = p_new_org_id,
844 job_id = p_new_job_id
845 where position_id = p_pos_id;
846
847 For ovn in c_get_ovn loop
848 l_ovn := ovn.object_version_number;
849 RETURN(l_ovn);
850 End loop;
851
852 End update_position;
853
854
855 END ghr_position_copy;