[Home] [Help]
PACKAGE BODY: APPS.GHR_POSN_COPY
Source
1 PACKAGE BODY ghr_posn_copy AS
2 /* $Header: ghrposcp.pkb 120.0 2005/05/29 03:37:07 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ghr_posn_copy.';
7 --
8 --
9
10 FUNCTION get_seq_location (p_business_group_id IN NUMBER)
11 RETURN VARCHAR2 IS
12 --
13 l_proc varchar2(72) := g_package||'get_seq_location';
14 --
15
16 CURSOR c_ori IS
17 SELECT ori.org_information4 seq_location
18 FROM hr_organization_information ori
19 WHERE ori.organization_id = p_business_group_id
20 AND ori.org_information_context = 'GHR_US_ORG_INFORMATION';
21 --
22 BEGIN
23 hr_utility.set_location('Entering get_seq_loation:'|| l_proc, 5);
24
25 FOR c_ori_rec IN c_ori LOOP
26 RETURN (c_ori_rec.seq_location);
27 END LOOP;
28 RETURN(NULL);
29 END get_seq_location;
30 --
31 --
32 ------------------------------------------------------------------
33 FUNCTION get_max_seq (p_seq_location IN VARCHAR2
34 ,p_business_group_id IN NUMBER
35 ,p_segment1 IN VARCHAR2
36 ,p_segment2 IN VARCHAR2
37 ,p_segment3 IN VARCHAR2
38 ,p_segment4 IN VARCHAR2
39 ,p_segment5 IN VARCHAR2
40 ,p_segment6 IN VARCHAR2
41 ,p_segment7 IN VARCHAR2
42 ,p_segment8 IN VARCHAR2
43 ,p_segment9 IN VARCHAR2
44 ,p_segment10 IN VARCHAR2
45 ,p_segment11 IN VARCHAR2
46 ,p_segment12 IN VARCHAR2
47 ,p_segment13 IN VARCHAR2
48 ,p_segment14 IN VARCHAR2
49 ,p_segment15 IN VARCHAR2
50 ,p_segment16 IN VARCHAR2
51 ,p_segment17 IN VARCHAR2
52 ,p_segment18 IN VARCHAR2
53 ,p_segment19 IN VARCHAR2
54 ,p_segment20 IN VARCHAR2
55 ,p_segment21 IN VARCHAR2
56 ,p_segment22 IN VARCHAR2
57 ,p_segment23 IN VARCHAR2
58 ,p_segment24 IN VARCHAR2
59 ,p_segment25 IN VARCHAR2
60 ,p_segment26 IN VARCHAR2
61 ,p_segment27 IN VARCHAR2
62 ,p_segment28 IN VARCHAR2
63 ,p_segment29 IN VARCHAR2
64 ,p_segment30 IN VARCHAR2)
65 RETURN VARCHAR2 IS
66 l_cur INTEGER;
67 l_stmt VARCHAR2(2000);
68 l_fetch_rows INTEGER;
69 l_seq_loc INTEGER;
70 l_max_seq VARCHAR2(150);
71 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
72 l_proc varchar2(72) := g_package||'get_max_seq';
73
74 --
75 cursor cur_org_id_flex_num is
76 select position_structure id_flex_num
77 from per_business_groups
78 where business_group_id = p_business_group_id;
79 --
80
81 BEGIN
82
83 hr_utility.set_location('Entering Get Max Seq:'|| l_proc, 5);
84
85 FOR cur_org_id_flex_num_rec IN cur_org_id_flex_num LOOP
86 l_id_flex_num := cur_org_id_flex_num_rec.id_flex_num;
87 END LOOP;
88
89
90 -- Build Dynamic Sql statment which locates all Position KFFs with the same
91 -- seqment values (excluding sequence) and returns the highest sequence found +1.
92
93 l_cur := dbms_sql.open_cursor;
94 l_stmt := 'SELECT MAX(TO_NUMBER('||p_seq_location||')) max_seq '||
95 'FROM per_position_definitions pde WHERE id_flex_num= '||l_id_flex_num;
96 --
97 l_seq_loc := SUBSTR(p_seq_location,8);
98
99 --
100
101 FOR i IN 1..30 LOOP
102 IF l_seq_loc <> i THEN
103 IF i = 1 and p_segment1 IS NOT NULL THEN
104 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment1,'''','''''')||'''';
105 ELSIF i = 2 and p_segment2 IS NOT NULL THEN
106 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment2,'''','''''')||'''';
107 ELSIF i = 3 and p_segment3 IS NOT NULL THEN
108 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment3,'''','''''')||'''';
109 ELSIF i = 4 and p_segment4 IS NOT NULL THEN
110 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment4,'''','''''')||'''';
111 ELSIF i = 5 and p_segment5 IS NOT NULL THEN
112 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment5,'''','''''')||'''';
113 ELSIF i = 6 and p_segment6 IS NOT NULL THEN
114 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment6,'''','''''')||'''';
115 ELSIF i = 7 and p_segment7 IS NOT NULL THEN
116 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment7,'''','''''')||'''';
117 ELSIF i = 8 and p_segment8 IS NOT NULL THEN
118 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment8,'''','''''')||'''';
119 ELSIF i = 9 and p_segment9 IS NOT NULL THEN
120 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment9,'''','''''')||'''';
121 ELSIF i = 10 and p_segment10 IS NOT NULL THEN
122 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment10,'''','''''')||'''';
123 ELSIF i = 11 and p_segment11 IS NOT NULL THEN
124 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment11,'''','''''')||'''';
125 ELSIF i = 12 and p_segment12 IS NOT NULL THEN
126 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment12,'''','''''')||'''';
127 ELSIF i = 13 and p_segment13 IS NOT NULL THEN
128 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment13,'''','''''')||'''';
129 ELSIF i = 14 and p_segment14 IS NOT NULL THEN
130 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment14,'''','''''')||'''';
131 ELSIF i = 15 and p_segment15 IS NOT NULL THEN
132 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment15,'''','''''')||'''';
133 ELSIF i = 16 and p_segment16 IS NOT NULL THEN
134 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment16,'''','''''')||'''';
135 ELSIF i = 17 and p_segment17 IS NOT NULL THEN
136 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment17,'''','''''')||'''';
137 ELSIF i = 18 and p_segment18 IS NOT NULL THEN
138 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment18,'''','''''')||'''';
139 ELSIF i = 19 and p_segment19 IS NOT NULL THEN
140 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment19,'''','''''')||'''';
141 ELSIF i = 20 and p_segment20 IS NOT NULL THEN
142 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment20,'''','''''')||'''';
143 ELSIF i = 21 and p_segment21 IS NOT NULL THEN
144 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment21,'''','''''')||'''';
145 ELSIF i = 22 and p_segment22 IS NOT NULL THEN
146 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment22,'''','''''')||'''';
147 ELSIF i = 23 and p_segment23 IS NOT NULL THEN
148 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment23,'''','''''')||'''';
149 ELSIF i = 24 and p_segment24 IS NOT NULL THEN
150 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment24,'''','''''')||'''';
151 ELSIF i = 25 and p_segment25 IS NOT NULL THEN
152 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment25,'''','''''')||'''';
153 ELSIF i = 26 and p_segment26 IS NOT NULL THEN
154 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment26,'''','''''')||'''';
155 ELSIF i = 27 and p_segment27 IS NOT NULL THEN
156 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment27,'''','''''')||'''';
157 ELSIF i = 28 and p_segment28 IS NOT NULL THEN
158 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment28,'''','''''')||'''';
159 ELSIF i = 29 and p_segment29 IS NOT NULL THEN
160 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment29,'''','''''')||'''';
161 ELSIF i = 30 and p_segment30 IS NOT NULL THEN
162 l_stmt := l_stmt||' AND SEGMENT'||i||' = '''||replace(p_segment30,'''','''''')||'''';
163 END IF;
164 END IF;
165 END LOOP;
166 l_stmt := l_stmt||' AND EXISTS (SELECT 1 FROM per_positions pos'
167 ||' WHERE pde.position_definition_id = pos.position_definition_id)';
168
169 --
170 -- dbms_sql.parse format (number, statement , language_flag)
171 dbms_sql.parse(l_cur, l_stmt, DBMS_SQL.NATIVE);
172 dbms_sql.define_column(l_cur, 1, l_max_seq,150);
173 l_fetch_rows := dbms_sql.execute(l_cur);
174 l_fetch_rows := dbms_sql.fetch_rows(l_cur);
175 dbms_sql.column_value(l_cur, 1, l_max_seq);
176 dbms_sql.close_cursor(l_cur);
177 --
178
179 /*
180 dbms_output.put_line(substr(l_stmt,1,100));
181 dbms_output.put_line(substr(l_stmt,101,100));
182 dbms_output.put_line(substr(l_stmt,201,100));
183 dbms_output.put_line('l_cur: '||l_cur);
184 dbms_output.put_line('DBMS_SQL: '||DBMS_SQL.NATIVE);
185 */
186
187 --
188 RETURN(l_max_seq);
189 --
190
191 END get_max_seq;
192 --
193 /*
194 This procedure will accept all data needed to create a position.
195 Incoming Parameters will match the core H.R. date tracked API (PQH).
196 The sequence number segment of the KFF will be determined and populated
197 per requirement doc. Then the position will be created using GHR wrappers
198 to create the position with history.
199 Do we need to use GHR wrappers?
200 This procedure will also create all child data required per design doc.
201 */
202
203 Procedure create_position_copy
204 (p_position_id in out nocopy number
205 ,p_effective_start_date out nocopy date
206 ,p_effective_end_date out nocopy date
207 ,p_position_definition_id out nocopy number
208 ,p_name out nocopy varchar2
209 ,p_object_version_number out nocopy number
210 ,p_job_id in number
211 ,p_organization_id in number
212 ,p_effective_date in date
213 ,p_date_effective in date
214 ,p_validate in boolean default false
215 ,p_availability_status_id in number default null
216 ,p_business_group_id in number default null
217 ,p_entry_step_id in number default null
218 ,p_entry_grade_rule_id in number default null
219 ,p_location_id in number default null
220 ,p_pay_freq_payroll_id in number default null
221 ,p_position_transaction_id in number default null
222 ,p_prior_position_id in number default null
223 ,p_relief_position_id in number default null
224 ,p_entry_grade_id in number default null
225 ,p_successor_position_id in number default null
226 ,p_supervisor_position_id in number default null
227 ,p_amendment_date in date default null
228 ,p_amendment_recommendation in varchar2 default null
229 ,p_amendment_ref_number in varchar2 default null
230 ,p_bargaining_unit_cd in varchar2 default null
231 ,p_comments in long default null
232 ,p_current_job_prop_end_date in date default null
233 ,p_current_org_prop_end_date in date default null
234 ,p_avail_status_prop_end_date in date default null
235 ,p_date_end in date default null
236 ,p_earliest_hire_date in date default null
237 ,p_fill_by_date in date default null
238 ,p_frequency in varchar2 default null
239 ,p_fte in number default null
240 ,p_max_persons in number default null
241 ,p_overlap_period in number default null
242 ,p_overlap_unit_cd in varchar2 default null
243 ,p_pay_term_end_day_cd in varchar2 default null
244 ,p_pay_term_end_month_cd in varchar2 default null
245 ,p_permanent_temporary_flag in varchar2 default null
246 ,p_permit_recruitment_flag in varchar2 default null
247 ,p_position_type in varchar2 default 'NONE'
248 ,p_posting_description in varchar2 default null
249 ,p_probation_period in number default null
250 ,p_probation_period_unit_cd in varchar2 default null
251 ,p_replacement_required_flag in varchar2 default null
252 ,p_review_flag in varchar2 default null
253 ,p_seasonal_flag in varchar2 default null
254 ,p_security_requirements in varchar2 default null
255 ,p_status in varchar2 default null
256 ,p_term_start_day_cd in varchar2 default null
257 ,p_term_start_month_cd in varchar2 default null
258 ,p_time_normal_finish in varchar2 default null
259 ,p_time_normal_start in varchar2 default null
260 ,p_update_source_cd in varchar2 default null
261 ,p_working_hours in number default null
262 ,p_works_council_approval_flag in varchar2 default null
263 ,p_work_period_type_cd in varchar2 default null
264 ,p_work_term_end_day_cd in varchar2 default null
265 ,p_work_term_end_month_cd in varchar2 default null
266 ,p_proposed_fte_for_layoff in number default null
267 ,p_proposed_date_for_layoff in date default null
268 ,p_pay_basis_id in number default null
269 ,p_supervisor_id in number default null
270 ,p_information1 in varchar2 default null
271 ,p_information2 in varchar2 default null
272 ,p_information3 in varchar2 default null
273 ,p_information4 in varchar2 default null
274 ,p_information5 in varchar2 default null
275 ,p_information6 in varchar2 default null
276 ,p_information7 in varchar2 default null
277 ,p_information8 in varchar2 default null
278 ,p_information9 in varchar2 default null
279 ,p_information10 in varchar2 default null
280 ,p_information11 in varchar2 default null
281 ,p_information12 in varchar2 default null
282 ,p_information13 in varchar2 default null
283 ,p_information14 in varchar2 default null
284 ,p_information15 in varchar2 default null
285 ,p_information16 in varchar2 default null
286 ,p_information17 in varchar2 default null
287 ,p_information18 in varchar2 default null
288 ,p_information19 in varchar2 default null
289 ,p_information20 in varchar2 default null
290 ,p_information21 in varchar2 default null
291 ,p_information22 in varchar2 default null
292 ,p_information23 in varchar2 default null
293 ,p_information24 in varchar2 default null
294 ,p_information25 in varchar2 default null
295 ,p_information26 in varchar2 default null
296 ,p_information27 in varchar2 default null
297 ,p_information28 in varchar2 default null
298 ,p_information29 in varchar2 default null
299 ,p_information30 in varchar2 default null
300 ,p_information_category in varchar2 default null
301 ,p_attribute1 in varchar2 default null
302 ,p_attribute2 in varchar2 default null
303 ,p_attribute3 in varchar2 default null
304 ,p_attribute4 in varchar2 default null
305 ,p_attribute5 in varchar2 default null
306 ,p_attribute6 in varchar2 default null
307 ,p_attribute7 in varchar2 default null
308 ,p_attribute8 in varchar2 default null
309 ,p_attribute9 in varchar2 default null
310 ,p_attribute10 in varchar2 default null
311 ,p_attribute11 in varchar2 default null
312 ,p_attribute12 in varchar2 default null
313 ,p_attribute13 in varchar2 default null
314 ,p_attribute14 in varchar2 default null
315 ,p_attribute15 in varchar2 default null
316 ,p_attribute16 in varchar2 default null
317 ,p_attribute17 in varchar2 default null
318 ,p_attribute18 in varchar2 default null
319 ,p_attribute19 in varchar2 default null
320 ,p_attribute20 in varchar2 default null
321 ,p_attribute21 in varchar2 default null
322 ,p_attribute22 in varchar2 default null
323 ,p_attribute23 in varchar2 default null
324 ,p_attribute24 in varchar2 default null
325 ,p_attribute25 in varchar2 default null
326 ,p_attribute26 in varchar2 default null
327 ,p_attribute27 in varchar2 default null
328 ,p_attribute28 in varchar2 default null
329 ,p_attribute29 in varchar2 default null
330 ,p_attribute30 in varchar2 default null
331 ,p_attribute_category in varchar2 default null
332 ,p_segment1 in varchar2 default null
333 ,p_segment2 in varchar2 default null
334 ,p_segment3 in varchar2 default null
335 ,p_segment4 in varchar2 default null
336 ,p_segment5 in varchar2 default null
337 ,p_segment6 in varchar2 default null
338 ,p_segment7 in varchar2 default null
339 ,p_segment8 in varchar2 default null
340 ,p_segment9 in varchar2 default null
341 ,p_segment10 in varchar2 default null
342 ,p_segment11 in varchar2 default null
343 ,p_segment12 in varchar2 default null
344 ,p_segment13 in varchar2 default null
345 ,p_segment14 in varchar2 default null
346 ,p_segment15 in varchar2 default null
347 ,p_segment16 in varchar2 default null
348 ,p_segment17 in varchar2 default null
349 ,p_segment18 in varchar2 default null
350 ,p_segment19 in varchar2 default null
351 ,p_segment20 in varchar2 default null
352 ,p_segment21 in varchar2 default null
353 ,p_segment22 in varchar2 default null
354 ,p_segment23 in varchar2 default null
355 ,p_segment24 in varchar2 default null
356 ,p_segment25 in varchar2 default null
357 ,p_segment26 in varchar2 default null
358 ,p_segment27 in varchar2 default null
359 ,p_segment28 in varchar2 default null
360 ,p_segment29 in varchar2 default null
361 ,p_segment30 in varchar2 default null
362 ,p_concat_segments in varchar2 default null
363 ,p_request_id in number default null
364 ,p_program_application_id in number default null
365 ,p_program_id in number default null
366 ,p_program_update_date in date default null
367 ) is
368
369 --
370 -- Declare cursors and local variables
371 --
372 l_position_id number;
373 l_effective_start_date date;
374 l_effective_end_date date;
375 l_position_definition_id number;
376 l_name varchar2(2000);
377 l_object_version_number number;
378 l_pos_data per_positions%rowtype;
379 l_result_code VARCHAR2(30);
380 l_pde_data per_position_definitions%rowtype;
381 l_dummy_number NUMBER;
382 l_new_pos_id per_positions.position_id%TYPE;
383 l_new_pos_name per_positions.name%TYPE;
384 l_seq_segment_name VARCHAR2(30);
385 l_new_seq VARCHAR2(150);
386 l_agency_seq VARCHAR2(150);
387 l_ovn NUMBER;
388 l_seq_len number(25);
389 l_seq_val VARCHAR2(250);
390 l_proc varchar2(72) := g_package||'create_position_copy';
391 l_concat_segments varchar2(250);
392 l_del varchar2(1) default '.';
393 l_source_posn_id number(25) default p_position_id;
394 l_session_date date;
395
396 l_segment1 varchar2(250);
397 l_segment2 varchar2(250);
398 l_segment3 varchar2(250);
399 l_segment4 varchar2(250);
400 l_segment5 varchar2(250);
401 l_segment6 varchar2(250);
402 l_segment7 varchar2(250);
403 l_segment8 varchar2(250);
404 l_segment9 varchar2(250);
405 l_segment10 varchar2(250);
406 l_segment11 varchar2(250);
407 l_segment12 varchar2(250);
408 l_segment13 varchar2(250);
409 l_segment14 varchar2(250);
410 l_segment15 varchar2(250);
411 l_segment16 varchar2(250);
412 l_segment17 varchar2(250);
413 l_segment18 varchar2(250);
414 l_segment19 varchar2(250);
415 l_segment20 varchar2(250);
416 l_segment21 varchar2(250);
417 l_segment22 varchar2(250);
418 l_segment23 varchar2(250);
419 l_segment24 varchar2(250);
420 l_segment25 varchar2(250);
421 l_segment26 varchar2(250);
422 l_segment27 varchar2(250);
423 l_segment28 varchar2(250);
424 l_segment29 varchar2(250);
425 l_segment30 varchar2(250);
426 --
427 CURSOR c_session_date IS
428 select EFFECTIVE_DATE
429 from fnd_sessions where session_id = userenv('sessionid');
430 --
431
432 BEGIN
433
434 FOR c_session_date_rec IN c_session_date LOOP
435 l_session_date := (c_session_date_rec.effective_date);
436 END LOOP;
437
438 hr_utility.set_location('Entering Copy:'|| l_proc, 5);
439 hr_utility.set_location('Effective Date:'|| p_effective_date, 6);
440 hr_utility.set_location('Date Effective:'|| p_date_effective, 7);
441 hr_utility.set_location('Session Date:'|| l_session_date, 8);
442 hr_utility.set_location('BG ID:'|| p_business_group_id, 9);
443 hr_utility.set_location('Position id '||to_char(p_position_id),10);
444
445 --
446 l_seq_segment_name := ghr_posn_copy.get_seq_location(p_business_group_id);
447
448 -- Set Locals to parameter values before overriding sequence segment for copy
449 l_segment1 := p_segment1;
450 l_segment2 := p_segment2;
451 l_segment3 := p_segment3;
452 l_segment4 := p_segment4;
453 l_segment5 := p_segment5;
454 l_segment6 := p_segment6;
455 l_segment7 := p_segment7;
456 l_segment8 := p_segment8;
457 l_segment9 := p_segment9;
458 l_segment10 := p_segment10;
459 l_segment11 := p_segment11;
460 l_segment12 := p_segment12;
461 l_segment13 := p_segment13;
462 l_segment14 := p_segment14;
463 l_segment15 := p_segment15;
464 l_segment16 := p_segment16;
465 l_segment17 := p_segment17;
466 l_segment18 := p_segment18;
467 l_segment19 := p_segment19;
468 l_segment20 := p_segment20;
469 l_segment21 := p_segment21;
470 l_segment22 := p_segment22;
471 l_segment23 := p_segment23;
472 l_segment24 := p_segment24;
473 l_segment25 := p_segment25;
474 l_segment26 := p_segment26;
475 l_segment27 := p_segment27;
476 l_segment28 := p_segment28;
477 l_segment29 := p_segment29;
478 l_segment30 := p_segment30;
479
480 hr_utility.set_location('l_segment1 :'||l_segment1, 9);
481 hr_utility.set_location('l_segment2 :'||l_segment2, 9);
482 hr_utility.set_location('l_segment3 :'||l_segment3, 9);
483 hr_utility.set_location('l_segment4 :'||l_segment4, 9);
484
485 --
486 -- Get Agency Specified Max Seq
487 --
488 hr_utility.set_location('Calling Agency Get Max:'|| l_proc, 10);
489 l_agency_seq := ghr_agency_position_copy.agency_get_max_seq(
490 l_seq_segment_name,
491 p_business_group_id,
492 p_segment1,
493 p_segment2,
494 p_segment3,
495 p_segment4,
496 p_segment5,
497 p_segment6,
498 p_segment7,
499 p_segment8,
500 p_segment9,
501 p_segment10,
502 p_segment11,
503 p_segment12,
504 p_segment13,
505 p_segment14,
506 p_segment15,
507 p_segment16,
508 p_segment17,
509 p_segment18,
510 p_segment19,
511 p_segment20,
512 p_segment21,
513 p_segment22,
514 p_segment23,
515 p_segment24,
516 p_segment25,
517 p_segment26,
518 p_segment27,
519 p_segment28,
520 p_segment29,
521 p_segment30);
522
523 hr_utility.set_location('Agency Get Max ='|| l_agency_seq, 11);
524
525
526 -- Agency get should return sequence value to be used for copy.
527 -- This value will not be incremented by 1. So a nextval will work.
528
529 hr_utility.set_location('Calling GHR get max seq:'|| l_proc, 12);
530 -- If Agency_get_max returns null then call GHR function
531
532 If l_agency_seq is null then
533
534 hr_utility.set_location('l_agency_seq is null'|| l_proc, 13);
535 hr_utility.set_location('Calling GHR Get Max'|| l_proc, 14);
536
537 l_new_seq := ghr_posn_copy.get_max_seq(
538 l_seq_segment_name,
539 p_business_group_id,
540 p_segment1,
541 p_segment2,
542 p_segment3,
543 p_segment4,
544 p_segment5,
545 p_segment6,
546 p_segment7,
547 p_segment8,
548 p_segment9,
549 p_segment10,
550 p_segment11,
551 p_segment12,
552 p_segment13,
553 p_segment14,
554 p_segment15,
555 p_segment16,
556 p_segment17,
557 p_segment18,
558 p_segment19,
559 p_segment20,
560 p_segment21,
561 p_segment22,
562 p_segment23,
563 p_segment24,
564 p_segment25,
565 p_segment26,
566 p_segment27,
567 p_segment28,
568 p_segment29,
569 p_segment30);
570
571 hr_utility.set_location('GHR Get Max = '|| l_new_seq, 15);
572
573 -- Add 1 to current max sequence returned if not null.
574 IF l_new_seq IS NULL THEN
575 l_new_seq := 1;
576 ELSE
577 l_new_seq := to_char(to_number(l_new_seq) + 1);
578 END IF;
579
580 -- Logic for Padding Zeroes to the New Position Sequence Number
581 -- Get the Sequence Number of Source position using the
582 -- function ghr_api.get_position_sequence_no_pos.
583 -- Find the length of source position's sequence Number.
584 -- Pad the new sequence to that length.
585
586 l_seq_val := ghr_api.get_position_sequence_no_pos
587 ( p_position_id => p_position_id
588 ,p_business_group_id => p_business_group_id
589 ,p_effective_date => l_session_date
590 );
591 l_seq_len := length(l_seq_val);
592 --
593 -- Sequence length passed into copy will be used
594 -- to determine length of sequence created.
595 --
596 -- Added if statments if length(l_new_seq) < l_seq_len for bug#2635850.
597
598 IF l_seq_segment_name = 'SEGMENT1' THEN
599 --l_seq_len := length(l_segment1);
600 IF length(l_new_seq) < l_seq_len THEN
601 l_segment1 := lpad(l_new_seq,l_seq_len,0);
602 ELSE
603 l_segment1 := l_new_seq;
604 END IF;
605 ELSIF l_seq_segment_name = 'SEGMENT2' THEN
606 --l_seq_len := length(l_segment2);
607 IF length(l_new_seq) < l_seq_len THEN
608 l_segment2 := lpad(l_new_seq,l_seq_len,0);
609 ELSE
610 l_segment2 := l_new_seq;
611 END IF;
612 ELSIF l_seq_segment_name = 'SEGMENT3' THEN
613 --l_seq_len := length(l_segment3);
614 IF length(l_new_seq) < l_seq_len THEN
615 l_segment3 := lpad(l_new_seq,l_seq_len,0);
616 ELSE
617 l_segment3 := l_new_seq;
618 END IF;
619 ELSIF l_seq_segment_name = 'SEGMENT4' THEN
620 --l_seq_len := length(l_segment4);
621 IF length(l_new_seq) < l_seq_len THEN
622 l_segment4 := lpad(l_new_seq,l_seq_len,0);
623 ELSE
624 l_segment4 := l_new_seq;
625 END IF;
626 ELSIF l_seq_segment_name = 'SEGMENT5' THEN
627 --l_seq_len := length(l_segment5);
628 IF length(l_new_seq) < l_seq_len THEN
629 l_segment5 := lpad(l_new_seq,l_seq_len,0);
630 ELSE
631 l_segment5 := l_new_seq;
632 END IF;
633 ELSIF l_seq_segment_name = 'SEGMENT6' THEN
634 --l_seq_len := length(l_segment6);
635 IF length(l_new_seq) < l_seq_len THEN
636 l_segment6 := lpad(l_new_seq,l_seq_len,0);
637 ELSE
638 l_segment6 := l_new_seq;
639 END IF;
640 ELSIF l_seq_segment_name = 'SEGMENT7' THEN
641 --l_seq_len := length(l_segment7);
642 IF length(l_new_seq) < l_seq_len THEN
643 l_segment7 := lpad(l_new_seq,l_seq_len,0);
644 ELSE
645 l_segment7 := l_new_seq;
646 END IF;
647 ELSIF l_seq_segment_name = 'SEGMENT8' THEN
648 --l_seq_len := length(l_segment8);
649 IF length(l_new_seq) < l_seq_len THEN
650 l_segment8 := lpad(l_new_seq,l_seq_len,0);
651 ELSE
652 l_segment8 := l_new_seq;
653 END IF;
654 ELSIF l_seq_segment_name = 'SEGMENT9' THEN
655 --l_seq_len := length(l_segment9);
656 IF length(l_new_seq) < l_seq_len THEN
657 l_segment9 := lpad(l_new_seq,l_seq_len,0);
658 ELSE
659 l_segment9 := l_new_seq;
660 END IF;
661 ELSIF l_seq_segment_name = 'SEGMENT10' THEN
662 --l_seq_len := length(l_segment10);
663 IF length(l_new_seq) < l_seq_len THEN
664 l_segment10 := lpad(l_new_seq,l_seq_len,0);
665 ELSE
666 l_segment10 := l_new_seq;
667 END IF;
668 ELSIF l_seq_segment_name = 'SEGMENT11' THEN
669 --l_seq_len := length(l_segment11);
670 IF length(l_new_seq) < l_seq_len THEN
671 l_segment11 := lpad(l_new_seq,l_seq_len,0);
672 ELSE
673 l_segment11 := l_new_seq;
674 END IF;
675 ELSIF l_seq_segment_name = 'SEGMENT12' THEN
676 --l_seq_len := length(l_segment12);
677 IF length(l_new_seq) < l_seq_len THEN
678 l_segment12 := lpad(l_new_seq,l_seq_len,0);
679 ELSE
680 l_segment12 := l_new_seq;
681 END IF;
682 ELSIF l_seq_segment_name = 'SEGMENT13' THEN
683 --l_seq_len := length(l_segment13);
684 IF length(l_new_seq) < l_seq_len THEN
685 l_segment13 := lpad(l_new_seq,l_seq_len,0);
686 ELSE
687 l_segment13 := l_new_seq;
688 END IF;
689 ELSIF l_seq_segment_name = 'SEGMENT14' THEN
690 --l_seq_len := length(l_segment14);
691 IF length(l_new_seq) < l_seq_len THEN
692 l_segment14 := lpad(l_new_seq,l_seq_len,0);
693 ELSE
694 l_segment14 := l_new_seq;
695 END IF;
696 ELSIF l_seq_segment_name = 'SEGMENT15' THEN
697 --l_seq_len := length(l_segment15);
698 IF length(l_new_seq) < l_seq_len THEN
699 l_segment15 := lpad(l_new_seq,l_seq_len,0);
700 ELSE
701 l_segment15 := l_new_seq;
702 END IF;
703 ELSIF l_seq_segment_name = 'SEGMENT16' THEN
704 --l_seq_len := length(l_segment16);
705 IF length(l_new_seq) < l_seq_len THEN
706 l_segment16 := lpad(l_new_seq,l_seq_len,0);
707 ELSE
708 l_segment16 := l_new_seq;
709 END IF;
710 ELSIF l_seq_segment_name = 'SEGMENT17' THEN
711 --l_seq_len := length(l_segment17);
712 IF length(l_new_seq) < l_seq_len THEN
713 l_segment17 := lpad(l_new_seq,l_seq_len,0);
714 ELSE
715 l_segment17 := l_new_seq;
716 END IF;
717 ELSIF l_seq_segment_name = 'SEGMENT18' THEN
718 --l_seq_len := length(l_segment18);
719 IF length(l_new_seq) < l_seq_len THEN
720 l_segment18 := lpad(l_new_seq,l_seq_len,0);
721 ELSE
722 l_segment18 := l_new_seq;
723 END IF;
724 ELSIF l_seq_segment_name = 'SEGMENT19' THEN
725 --l_seq_len := length(l_segment19);
726 IF length(l_new_seq) < l_seq_len THEN
727 l_segment19 := lpad(l_new_seq,l_seq_len,0);
728 ELSE
729 l_segment19 := l_new_seq;
730 END IF;
731 ELSIF l_seq_segment_name = 'SEGMENT20' THEN
732 --l_seq_len := length(l_segment20);
733 IF length(l_new_seq) < l_seq_len THEN
734 l_segment20 := lpad(l_new_seq,l_seq_len,0);
735 ELSE
736 l_segment20 := l_new_seq;
737 END IF;
738 ELSIF l_seq_segment_name = 'SEGMENT21' THEN
739 --l_seq_len := length(l_segment21);
740 IF length(l_new_seq) < l_seq_len THEN
741 l_segment21 := lpad(l_new_seq,l_seq_len,0);
742 ELSE
743 l_segment21 := l_new_seq;
744 END IF;
745 ELSIF l_seq_segment_name = 'SEGMENT22' THEN
746 --l_seq_len := length(l_segment22);
747 IF length(l_new_seq) < l_seq_len THEN
748 l_segment22 := lpad(l_new_seq,l_seq_len,0);
749 ELSE
750 l_segment22 := l_new_seq;
751 END IF;
752 ELSIF l_seq_segment_name = 'SEGMENT23' THEN
753 --l_seq_len := length(l_segment23);
754 IF length(l_new_seq) < l_seq_len THEN
755 l_segment23 := lpad(l_new_seq,l_seq_len,0);
756 ELSE
757 l_segment23 := l_new_seq;
758 END IF;
759 ELSIF l_seq_segment_name = 'SEGMENT24' THEN
760 --l_seq_len := length(l_segment24);
761 IF length(l_new_seq) < l_seq_len THEN
762 l_segment24 := lpad(l_new_seq,l_seq_len,0);
763 ELSE
764 l_segment24 := l_new_seq;
765 END IF;
766 ELSIF l_seq_segment_name = 'SEGMENT25' THEN
767 --l_seq_len := length(l_segment25);
768 IF length(l_new_seq) < l_seq_len THEN
769 l_segment25 := lpad(l_new_seq,l_seq_len,0);
770 ELSE
771 l_segment25 := l_new_seq;
772 END IF;
773 ELSIF l_seq_segment_name = 'SEGMENT26' THEN
774 --l_seq_len := length(l_segment26);
775 IF length(l_new_seq) < l_seq_len THEN
776 l_segment26 := lpad(l_new_seq,l_seq_len,0);
777 ELSE
778 l_segment26 := l_new_seq;
779 END IF;
780 ELSIF l_seq_segment_name = 'SEGMENT27' THEN
781 --l_seq_len := length(l_segment27);
782 IF length(l_new_seq) < l_seq_len THEN
783 l_segment27 := lpad(l_new_seq,l_seq_len,0);
784 ELSE
785 l_segment27 := l_new_seq;
786 END IF;
787 ELSIF l_seq_segment_name = 'SEGMENT28' THEN
788 --l_seq_len := length(l_segment28);
789 IF length(l_new_seq) < l_seq_len THEN
790 l_segment28 := lpad(l_new_seq,l_seq_len,0);
791 ELSE
792 l_segment28 := l_new_seq;
793 END IF;
794 ELSIF l_seq_segment_name = 'SEGMENT29' THEN
795 --l_seq_len := length(l_segment29);
796 IF length(l_new_seq) < l_seq_len THEN
797 l_segment29 := lpad(l_new_seq,l_seq_len,0);
798 ELSE
799 l_segment29 := l_new_seq;
800 END IF;
801 ELSIF l_seq_segment_name = 'SEGMENT30' THEN
802 --l_seq_len := length(l_segment30);
803 IF length(l_new_seq) < l_seq_len THEN
804 l_segment30 := lpad(l_new_seq,l_seq_len,0);
805 ELSE
806 l_segment30 := l_new_seq;
807 END IF;
808 END IF;
809 --
810
811 Else -- Use Agency Sequence Returned
812
813 -- Bug 2406584 Do not lpad agency sequence number
814
815 l_new_seq := l_agency_seq;
816
817 IF l_seq_segment_name = 'SEGMENT1' THEN
818 l_segment1 := l_new_seq;
819 ELSIF l_seq_segment_name = 'SEGMENT2' THEN
820 l_segment2 := l_new_seq;
821 ELSIF l_seq_segment_name = 'SEGMENT3' THEN
822 l_segment3 := l_new_seq;
823 ELSIF l_seq_segment_name = 'SEGMENT4' THEN
824 l_segment4 := l_new_seq;
825 ELSIF l_seq_segment_name = 'SEGMENT5' THEN
826 l_segment5 := l_new_seq;
827 ELSIF l_seq_segment_name = 'SEGMENT6' THEN
828 l_segment6 := l_new_seq;
829 ELSIF l_seq_segment_name = 'SEGMENT7' THEN
830 l_segment7 := l_new_seq;
831 ELSIF l_seq_segment_name = 'SEGMENT8' THEN
832 l_segment8 := l_new_seq;
833 ELSIF l_seq_segment_name = 'SEGMENT9' THEN
834 l_segment9 := l_new_seq;
835 ELSIF l_seq_segment_name = 'SEGMENT10' THEN
836 l_segment10 := l_new_seq;
837 ELSIF l_seq_segment_name = 'SEGMENT11' THEN
838 l_segment11 := l_new_seq;
839 ELSIF l_seq_segment_name = 'SEGMENT12' THEN
840 l_segment12 := l_new_seq;
841 ELSIF l_seq_segment_name = 'SEGMENT13' THEN
842 l_segment13 := l_new_seq;
843 ELSIF l_seq_segment_name = 'SEGMENT14' THEN
844 l_segment14 := l_new_seq;
845 ELSIF l_seq_segment_name = 'SEGMENT15' THEN
846 l_segment15 := l_new_seq;
847 ELSIF l_seq_segment_name = 'SEGMENT16' THEN
848 l_segment16 := l_new_seq;
849 ELSIF l_seq_segment_name = 'SEGMENT17' THEN
850 l_segment17 := l_new_seq;
851 ELSIF l_seq_segment_name = 'SEGMENT18' THEN
852 l_segment18 := l_new_seq;
853 ELSIF l_seq_segment_name = 'SEGMENT19' THEN
854 l_segment19 := l_new_seq;
855 ELSIF l_seq_segment_name = 'SEGMENT20' THEN
856 l_segment20 := l_new_seq;
857 ELSIF l_seq_segment_name = 'SEGMENT21' THEN
858 l_segment21 := l_new_seq;
859 ELSIF l_seq_segment_name = 'SEGMENT22' THEN
860 l_segment22 := l_new_seq;
861 ELSIF l_seq_segment_name = 'SEGMENT23' THEN
862 l_segment23 := l_new_seq;
863 ELSIF l_seq_segment_name = 'SEGMENT24' THEN
864 l_segment24 := l_new_seq;
865 ELSIF l_seq_segment_name = 'SEGMENT25' THEN
866 l_segment25 := l_new_seq;
867 ELSIF l_seq_segment_name = 'SEGMENT26' THEN
868 l_segment26 := l_new_seq;
869 ELSIF l_seq_segment_name = 'SEGMENT27' THEN
870 l_segment27 := l_new_seq;
871 ELSIF l_seq_segment_name = 'SEGMENT28' THEN
872 l_segment28 := l_new_seq;
873 ELSIF l_seq_segment_name = 'SEGMENT29' THEN
874 l_segment29 := l_new_seq;
875 ELSIF l_seq_segment_name = 'SEGMENT30' THEN
876 l_segment30 := l_new_seq;
877 END IF;
878
879 End If;
880
881 -- Set Status to Invalid for all copies
882 l_pos_data.status := 'INVALID';
883 --
884
885 hr_utility.set_location('Calling ghr_posndt.create_position:'|| l_proc, 20);
886 hr_utility.set_location('l_segment1 :'||l_segment1, 21);
887 hr_utility.set_location('l_segment2 :'||l_segment2, 21);
888 hr_utility.set_location('l_segment3 :'||l_segment3, 21);
889 hr_utility.set_location('l_segment4 :'||l_segment4, 21);
890 hr_utility.set_location('l_position_id : '||l_position_id , 21);
891 hr_utility.set_location('l_effective_start_date: '||l_effective_start_date, 21);
892 hr_utility.set_location('l_effective_end_date: '||l_effective_end_date, 21);
893 hr_utility.set_location('l_position_definition_id: '||l_position_definition_id, 21);
894 hr_utility.set_location('l_name :'||l_name, 21);
895 hr_utility.set_location('p_effective_date :'||p_effective_date, 21);
896 hr_utility.set_location('p_date_effective :'||p_date_effective, 21);
897 hr_utility.set_location('p_business_group_id :'||p_business_group_id, 21);
898 hr_utility.set_location('p_concat_segments :'||p_concat_segments, 21);
899
900 ghr_posndt_api.create_position(
901 p_position_id => l_position_id
902 ,p_effective_start_date => l_effective_start_date
903 ,p_effective_end_date => l_effective_end_date
904 ,p_position_definition_id => l_position_definition_id
905 ,p_name => l_name
906 ,p_object_version_number => l_object_version_number
907 ,p_job_id => p_job_id
908 ,p_organization_id => p_organization_id
909 ,p_effective_date => p_effective_date
910 ,p_date_effective => p_date_effective
911 ,p_validate => FALSE
912 ,p_availability_status_id => p_availability_status_id
913 ,p_business_group_id => p_business_group_id
914 ,p_entry_step_id => p_entry_step_id
915 ,p_entry_grade_rule_id => p_entry_grade_rule_id
916 ,p_location_id => p_location_id
917 ,p_pay_freq_payroll_id => p_pay_freq_payroll_id
918 ,p_position_transaction_id => p_position_transaction_id
919 ,p_prior_position_id => p_prior_position_id
920 ,p_relief_position_id => p_relief_position_id
921 ,p_entry_grade_id => p_entry_grade_id
922 ,p_successor_position_id => p_successor_position_id
923 ,p_supervisor_position_id => p_supervisor_position_id
924 ,p_amendment_date => p_amendment_date
925 ,p_amendment_recommendation => p_amendment_recommendation
926 ,p_amendment_ref_number => p_amendment_ref_number
927 ,p_bargaining_unit_cd => p_bargaining_unit_cd
928 ,p_comments => p_comments
929 ,p_current_job_prop_end_date => p_current_job_prop_end_date
930 ,p_current_org_prop_end_date => p_current_org_prop_end_date
931 ,p_avail_status_prop_end_date => p_avail_status_prop_end_date
932 ,p_date_end => p_date_end
933 ,p_earliest_hire_date => p_earliest_hire_date
934 ,p_fill_by_date => p_fill_by_date
935 ,p_frequency => p_frequency
936 ,p_fte => p_fte
937 ,p_max_persons => p_max_persons
938 ,p_overlap_period => p_overlap_period
939 ,p_overlap_unit_cd => p_overlap_unit_cd
940 ,p_pay_term_end_day_cd => p_pay_term_end_day_cd
941 ,p_pay_term_end_month_cd => p_pay_term_end_month_cd
942 ,p_permanent_temporary_flag => p_permanent_temporary_flag
943 ,p_permit_recruitment_flag => p_permit_recruitment_flag
944 ,p_position_type => p_position_type
945 ,p_posting_description => p_posting_description
946 ,p_probation_period => p_probation_period
947 ,p_probation_period_unit_cd => p_probation_period_unit_cd
948 ,p_replacement_required_flag => p_replacement_required_flag
949 ,p_review_flag => p_review_flag
950 ,p_seasonal_flag => p_seasonal_flag
951 ,p_security_requirements => p_security_requirements
952 ,p_status => p_status
953 ,p_term_start_day_cd => p_term_start_day_cd
954 ,p_term_start_month_cd => p_term_start_month_cd
955 ,p_time_normal_finish => p_time_normal_finish
956 ,p_time_normal_start => p_time_normal_start
957 ,p_update_source_cd => p_update_source_cd
958 ,p_working_hours => p_working_hours
959 ,p_works_council_approval_flag => p_works_council_approval_flag
960 ,p_work_period_type_cd => p_work_period_type_cd
961 ,p_work_term_end_day_cd => p_work_term_end_day_cd
962 ,p_work_term_end_month_cd => p_work_term_end_month_cd
963 ,p_proposed_fte_for_layoff => p_proposed_fte_for_layoff
964 ,p_proposed_date_for_layoff => p_proposed_date_for_layoff
965 ,p_pay_basis_id => p_pay_basis_id
966 ,p_supervisor_id => p_supervisor_id
967 ,p_information1 => p_information1
968 ,p_information2 => p_information2
969 ,p_information3 => p_information3
970 ,p_information4 => p_information4
971 ,p_information5 => p_information5
972 ,p_information6 => p_information6
973 ,p_information7 => p_information7
974 ,p_information8 => p_information8
975 ,p_information9 => p_information9
976 ,p_information10 => p_information10
977 ,p_information11 => p_information11
978 ,p_information12 => p_information12
979 ,p_information13 => p_information13
980 ,p_information14 => p_information14
981 ,p_information15 => p_information15
982 ,p_information16 => p_information16
983 ,p_information17 => p_information17
984 ,p_information18 => p_information18
985 ,p_information19 => p_information19
986 ,p_information20 => p_information20
987 ,p_information21 => p_information21
988 ,p_information22 => p_information22
989 ,p_information23 => p_information23
990 ,p_information24 => p_information24
991 ,p_information25 => p_information25
992 ,p_information26 => p_information26
993 ,p_information27 => p_information27
994 ,p_information28 => p_information29
995 ,p_information29 => p_information29
996 ,p_information30 => p_information30
997 ,p_information_category => p_information_category
998 ,p_attribute1 => p_attribute1
999 ,p_attribute2 => p_attribute2
1000 ,p_attribute3 => p_attribute3
1001 ,p_attribute4 => p_attribute4
1002 ,p_attribute5 => p_attribute5
1003 ,p_attribute6 => p_attribute6
1004 ,p_attribute7 => p_attribute7
1005 ,p_attribute8 => p_attribute8
1006 ,p_attribute9 => p_attribute9
1007 ,p_attribute10 => p_attribute10
1008 ,p_attribute11 => p_attribute11
1009 ,p_attribute12 => p_attribute12
1010 ,p_attribute13 => p_attribute13
1011 ,p_attribute14 => p_attribute14
1012 ,p_attribute15 => p_attribute15
1013 ,p_attribute16 => p_attribute16
1014 ,p_attribute17 => p_attribute17
1015 ,p_attribute18 => p_attribute18
1016 ,p_attribute19 => p_attribute19
1017 ,p_attribute20 => p_attribute20
1018 ,p_attribute21 => p_attribute21
1019 ,p_attribute22 => p_attribute22
1020 ,p_attribute23 => p_attribute23
1021 ,p_attribute24 => p_attribute24
1022 ,p_attribute25 => p_attribute25
1023 ,p_attribute26 => p_attribute26
1024 ,p_attribute27 => p_attribute27
1025 ,p_attribute28 => p_attribute28
1026 ,p_attribute29 => p_attribute29
1027 ,p_attribute30 => p_attribute30
1028 ,p_attribute_category => p_attribute_category
1029 ,p_segment1 => l_segment1
1030 ,p_segment2 => l_segment2
1031 ,p_segment3 => l_segment3
1032 ,p_segment4 => l_segment4
1033 ,p_segment5 => l_segment5
1034 ,p_segment6 => l_segment6
1035 ,p_segment7 => l_segment7
1036 ,p_segment8 => l_segment8
1037 ,p_segment9 => l_segment9
1038 ,p_segment10 => l_segment10
1039 ,p_segment11 => l_segment11
1040 ,p_segment12 => l_segment12
1041 ,p_segment13 => l_segment13
1042 ,p_segment14 => l_segment14
1043 ,p_segment15 => l_segment15
1044 ,p_segment16 => l_segment16
1045 ,p_segment17 => l_segment17
1046 ,p_segment18 => l_segment18
1047 ,p_segment19 => l_segment19
1048 ,p_segment20 => l_segment20
1049 ,p_segment21 => l_segment21
1050 ,p_segment22 => l_segment22
1051 ,p_segment23 => l_segment23
1052 ,p_segment24 => l_segment24
1053 ,p_segment25 => l_segment25
1054 ,p_segment26 => l_segment26
1055 ,p_segment27 => l_segment27
1056 ,p_segment28 => l_segment28
1057 ,p_segment29 => l_segment29
1058 ,p_segment30 => l_segment30
1059 ,p_concat_segments => p_concat_segments
1060 ,p_request_id => p_request_id
1061 ,p_program_application_id => p_program_application_id
1062 ,p_program_id => p_program_id
1063 ,p_program_update_date => p_program_update_date
1064 );
1065
1066 hr_utility.set_location(l_proc, 25);
1067
1068 p_position_id := l_position_id;
1069 p_effective_start_date := l_effective_start_date;
1070 p_effective_end_date := l_effective_end_date;
1071 p_position_definition_id := l_position_definition_id;
1072 p_name := l_name;
1073 p_object_version_number := l_object_version_number;
1074
1075 hr_utility.set_location('Created '||p_position_id||' '||p_name, 20);
1076
1077 -- Add Creation of Position Extra Info Here --
1078 --
1079 create_all_posn_ei(l_source_posn_id, p_effective_date, p_position_id, l_session_date);
1080
1081 -- Bug#2944210 Update the Newly Created Position Name into pqh_copy_entity_results Table
1082 UPDATE pqh_copy_entity_results
1083 SET information1 = p_name
1084 WHERE copy_entity_result_id = pqh_generic.g_result_id ;
1085 --
1086
1087 END create_position_copy;
1088
1089 --
1090 -- Given a source position id this procedure will create ALL the extra info
1091 -- details associated with the source position id onto the to position id
1092 -- For position copy we will explicity exclude types:
1093 -- GHR_US_POS_MASS_ACTIONS
1094 -- GHR_US_POS_OBLIG
1095 -- GHR_US_POS_POSITION_DESCRIPTION
1096 --
1097
1098 PROCEDURE create_all_posn_ei (p_source_posn_id IN NUMBER
1099 ,p_effective_date IN DATE
1100 ,p_position_id IN NUMBER
1101 ,p_date_effective IN DATE) IS
1102
1103 l_proc varchar2(72) := g_package||'create_all_posn_ei';
1104
1105 --
1106 --
1107 --
1108
1109 CURSOR cur_pit IS
1110 SELECT pit.information_type
1111 FROM per_position_info_types pit
1112 WHERE pit.information_type NOT IN ('GHR_US_POS_MASS_ACTIONS','GHR_US_POS_OBLIG'
1113 ,'GHR_US_POSITION_DESCRIPTION');
1114
1115 BEGIN
1116
1117 FOR cur_pit_rec IN cur_pit LOOP
1118 ghr_posn_copy.create_posn_ei(p_source_posn_id
1119 ,p_effective_date
1120 ,p_position_id
1121 ,p_date_effective
1122 ,cur_pit_rec.information_type);
1123 END LOOP;
1124 hr_utility.set_location(l_proc, 30);
1125
1126 END create_all_posn_ei;
1127 --
1128 --
1129 -- Given a source position id and information type this procedure
1130 -- will create the extra info
1131 -- details associated with the source position id onto the to position id
1132 PROCEDURE create_posn_ei (p_source_posn_id IN NUMBER
1133 ,p_effective_date IN DATE
1134 ,p_position_id IN NUMBER
1135 ,p_date_effective IN DATE
1136 ,p_info_type IN VARCHAR2) IS
1137 --
1138 l_pos_ei_data per_position_extra_info%rowtype;
1139 l_dummy_number NUMBER;
1140 l_result_code VARCHAR2(30);
1141 l_proc varchar2(72) := g_package||'create_posn_ei';
1142
1143 --
1144 CURSOR cur_poi IS
1145 SELECT poi.position_extra_info_id
1146 FROM per_position_extra_info poi
1147 WHERE poi.information_type = p_info_type
1148 AND poi.position_id = p_source_posn_id;
1149 --
1150 BEGIN
1151 -- loops to handle multi_occurrences
1152 FOR cur_poi_rec IN cur_poi LOOP
1153 -- Fetch from history
1154 ghr_history_fetch.fetch_positionei (
1155 p_position_extra_info_id => cur_poi_rec.position_extra_info_id
1156 ,p_date_effective => p_date_effective
1157 ,p_posei_data => l_pos_ei_data
1158 ,p_result_code => l_result_code);
1159 --
1160 hr_utility.set_location(l_proc, 35);
1161 --
1162 -- Now create it against the to position
1163 --
1164 /*
1165 dbms_output.put_line('Creating EI');
1166 dbms_output.put_line('Info Type '||p_info_type);
1167 dbms_output.put_line('Source Posn Id '||p_source_posn_id);
1168 dbms_output.put_line('Posn Id '||p_position_id);
1169 dbms_output.put_line('Effective Date '||p_effective_date);
1170 dbms_output.put_line('Date Effective '||p_date_effective);
1171 dbms_output.put_line('info cat '||l_pos_ei_data.poei_information_category);
1172 dbms_output.put_line('info1 '||l_pos_ei_data.poei_information1);
1173 dbms_output.put_line('info2 '||l_pos_ei_data.poei_information2);
1174 dbms_output.put_line('info3 '||l_pos_ei_data.poei_information3);
1175 dbms_output.put_line('info4 '||l_pos_ei_data.poei_information4);
1176 dbms_output.put_line('info5 '||l_pos_ei_data.poei_information5);
1177 dbms_output.put_line('info6 '||l_pos_ei_data.poei_information6);
1178 dbms_output.put_line('info7 '||l_pos_ei_data.poei_information7);
1179 dbms_output.put_line('info8 '||l_pos_ei_data.poei_information8);
1180 dbms_output.put_line('info9 '||l_pos_ei_data.poei_information9);
1181 dbms_output.put_line('info10 '||l_pos_ei_data.poei_information10);
1182 */
1183
1184 ghr_position_extra_info_api.create_position_extra_info
1185 (p_validate => FALSE
1186 ,p_position_id => p_position_id
1187 ,p_information_type => p_info_type
1188 ,p_effective_date => p_effective_date
1189 ,p_poei_attribute_category => l_pos_ei_data.poei_attribute_category
1190 ,p_poei_attribute1 => l_pos_ei_data.poei_attribute1
1191 ,p_poei_attribute2 => l_pos_ei_data.poei_attribute2
1192 ,p_poei_attribute3 => l_pos_ei_data.poei_attribute3
1193 ,p_poei_attribute4 => l_pos_ei_data.poei_attribute4
1194 ,p_poei_attribute5 => l_pos_ei_data.poei_attribute5
1195 ,p_poei_attribute6 => l_pos_ei_data.poei_attribute6
1196 ,p_poei_attribute7 => l_pos_ei_data.poei_attribute7
1197 ,p_poei_attribute8 => l_pos_ei_data.poei_attribute8
1198 ,p_poei_attribute9 => l_pos_ei_data.poei_attribute9
1199 ,p_poei_attribute10 => l_pos_ei_data.poei_attribute10
1200 ,p_poei_attribute11 => l_pos_ei_data.poei_attribute11
1201 ,p_poei_attribute12 => l_pos_ei_data.poei_attribute12
1202 ,p_poei_attribute13 => l_pos_ei_data.poei_attribute13
1203 ,p_poei_attribute14 => l_pos_ei_data.poei_attribute14
1204 ,p_poei_attribute15 => l_pos_ei_data.poei_attribute15
1205 ,p_poei_attribute16 => l_pos_ei_data.poei_attribute16
1206 ,p_poei_attribute17 => l_pos_ei_data.poei_attribute17
1207 ,p_poei_attribute18 => l_pos_ei_data.poei_attribute18
1208 ,p_poei_attribute19 => l_pos_ei_data.poei_attribute19
1209 ,p_poei_attribute20 => l_pos_ei_data.poei_attribute20
1210 ,p_poei_information_category => l_pos_ei_data.poei_information_category
1211 ,p_poei_information1 => l_pos_ei_data.poei_information1
1212 ,p_poei_information2 => l_pos_ei_data.poei_information2
1213 ,p_poei_information3 => l_pos_ei_data.poei_information3
1214 ,p_poei_information4 => l_pos_ei_data.poei_information4
1215 ,p_poei_information5 => l_pos_ei_data.poei_information5
1216 ,p_poei_information6 => l_pos_ei_data.poei_information6
1217 ,p_poei_information7 => l_pos_ei_data.poei_information7
1218 ,p_poei_information8 => l_pos_ei_data.poei_information8
1219 ,p_poei_information9 => l_pos_ei_data.poei_information9
1220 ,p_poei_information10 => l_pos_ei_data.poei_information10
1221 ,p_poei_information11 => l_pos_ei_data.poei_information11
1222 ,p_poei_information12 => l_pos_ei_data.poei_information12
1223 ,p_poei_information13 => l_pos_ei_data.poei_information13
1224 ,p_poei_information14 => l_pos_ei_data.poei_information14
1225 ,p_poei_information15 => l_pos_ei_data.poei_information15
1226 ,p_poei_information16 => l_pos_ei_data.poei_information16
1227 ,p_poei_information17 => l_pos_ei_data.poei_information17
1228 ,p_poei_information18 => l_pos_ei_data.poei_information18
1229 ,p_poei_information19 => l_pos_ei_data.poei_information19
1230 ,p_poei_information20 => l_pos_ei_data.poei_information20
1231 ,p_poei_information21 => l_pos_ei_data.poei_information21
1232 ,p_poei_information22 => l_pos_ei_data.poei_information22
1233 ,p_poei_information23 => l_pos_ei_data.poei_information23
1234 ,p_poei_information24 => l_pos_ei_data.poei_information24
1235 ,p_poei_information25 => l_pos_ei_data.poei_information25
1236 ,p_poei_information26 => l_pos_ei_data.poei_information26
1237 ,p_poei_information27 => l_pos_ei_data.poei_information27
1238 ,p_poei_information28 => l_pos_ei_data.poei_information28
1239 ,p_poei_information29 => l_pos_ei_data.poei_information29
1240 ,p_poei_information30 => l_pos_ei_data.poei_information30
1241 ,p_position_extra_info_id => l_dummy_number
1242 ,p_object_version_number => l_dummy_number);
1243
1244 hr_utility.set_location(l_proc, 40);
1245 --dbms_output.put_line('EI Created ');
1246
1247 END LOOP;
1248
1249 hr_utility.set_location(l_proc, 50);
1250 --
1251 END create_posn_ei;
1252
1253 END ghr_posn_copy;