[Home] [Help]
PACKAGE BODY: APPS.PQH_ASG_WRAPPER
Source
1 PACKAGE BODY pqh_asg_wrapper AS
2 /* $Header: peasgmup.pkb 120.1 2005/11/21 02:58:36 ayegappa noship $ */
3 --
4 g_package varchar2(33) := ' pqh_asg_wrapper.';
5 g_org_hierarchy_id number;
6 g_org_starting_node number;
7 g_org_hierarchy_root number;
8 g_pos_hierarchy_id number;
9 g_pos_starting_node number;
10 g_pos_hierarchy_root number;
11 g_txn_id number;
12 --
13 -- ---------------------------------------------------------------------------+
14 -- |----------------------------< SHOW_WORKER_NUMBER >------------------------+
15 -- ---------------------------------------------------------------------------+
16 --
17 FUNCTION show_worker_number
18 (p_employee_number IN VARCHAR2
19 ,p_npw_number IN VARCHAR2
20 -- ,p_applicant_number IN VARCHAR2
21 ) RETURN VARCHAR2 IS
22 --
23 l_worker_number VARCHAR2(80) := NULL;
24 --
25 BEGIN
26 --
27 IF p_employee_number IS NOT NULL THEN
28 --
29 l_worker_number := p_employee_number;
30 --
31 END IF;
32 --
33 IF p_npw_number IS NOT NULL THEN
34 --
35 IF l_worker_number IS NOT NULL THEN
36 --
37 l_worker_number := l_worker_number||','||p_npw_number;
38 --
39 ELSE
40 --
41 l_worker_number := p_npw_number;
42 --
43 END IF;
44 --
45 END IF;
46 --
47 /* Removed the part that attaches applicant number to Full name */
48 --
49 RETURN(l_worker_number);
50 --
51 END show_worker_number;
52 --
53 -- ---------------------------------------------------------------------------+
54 -- |------------------------< GET_ASSIGNMENT_STATUS_TYPE >--------------------+
55 -- ---------------------------------------------------------------------------+
56 --
57 FUNCTION get_assignment_status_type
58 (p_assignment_status_type_id IN NUMBER) RETURN VARCHAR2 IS
59 --
60 CURSOR get_status_type IS
61 SELECT p.per_system_status
62 FROM per_assignment_status_types p
63 WHERE p.assignment_status_type_id = p_assignment_status_type_id;
64 --
65 l_proc VARCHAR2(72) := g_package||'get_assignment_status_type';
66 l_assignment_status_type per_assignment_status_types.per_system_status%TYPE;
67
68 BEGIN
69 --
70 hr_utility.set_location('Entering : '||l_proc, 10);
71 --
72 OPEN get_status_type;
73 FETCH get_status_type INTO l_assignment_status_type;
74 --
75 IF get_status_type%NOTFOUND THEN
76 --
77 hr_utility.set_location(l_proc, 20);
78 --
79 CLOSE get_status_type;
80 --
81 hr_utility.set_message(801,'HR_7940_ASG_INV_ASG_STAT_TYPE');
82 hr_utility.raise_error;
83 --
84 ELSE
85 --
86 hr_utility.set_location(l_proc, 30);
87 --
88 CLOSE get_status_type;
89 --
90 END IF;
91 --
92 hr_utility.set_location('Leaving : '||l_proc,100);
93 --
94 RETURN(l_assignment_status_type);
95 --
96 END get_assignment_status_type;
97 --
98 -- ---------------------------------------------------------------------------+
99 -- |------------------------< IS_TYPE_AN_APPLICANT_TYPE >---------------------+
100 -- ---------------------------------------------------------------------------+
101 --
102 FUNCTION Is_Type_An_Applicant_Type
103 (p_person_type_id IN per_person_types.person_type_id%TYPE)
104 RETURN CHAR IS
105 --
106 Cursor c_get_system_person_type Is
107 Select information22
108 from pqh_copy_entity_attribs
109 Where information22 IS NOT NULL
110 And row_type_cd = 'CRITERIA'
111 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
112 --
113 Cursor c_get_system_type_name(p_person_type_id IN
114 per_person_types.person_type_id%TYPE) Is
115 Select ppt.system_person_type
116 From per_person_types ppt
117 Where ppt.person_type_id = p_person_type_id;
118 --
119 v_system_person_type per_person_types.system_person_type%TYPE := NULL;
120 v_criteria_system_person_type per_person_types.system_person_type%TYPE := NULL;
121 v_dummy_field varchar2(50) := NULL;
122 v_return_value varchar2(5);
123 --
124 l_proc varchar2(72) := g_package||'Is_Type_An_Applicant_Type';
125 --
126 BEGIN
127 --
128 hr_utility.set_location('Entering:'|| l_proc, 10);
129 --
130 -- Check to see if a system person type has been
131 -- entered as part of the query criteria in the form.
132 --
133 Open c_get_system_person_type;
134 Fetch c_get_system_person_type into v_criteria_system_person_type;
135 --
136 hr_utility.set_location(l_proc,20);
137 --
138 -- If no system person type has been entered then
139 -- check that the employee is either an Applicant
140 -- or an Employee_Applicant.
141 --
142 If c_get_system_person_type%NOTFOUND Then
143 --
144 hr_utility.set_location(l_proc,30);
145 --
146 close c_get_system_person_type;
147 --
148 -- Retrieve the system person type for this employee
149 --
150 Open c_get_system_type_name(p_person_type_id);
151 Fetch c_get_system_type_name Into v_system_person_type;
152 Close c_get_system_type_name;
153 --
154 hr_utility.set_location(l_proc,40);
155 --
156 -- If the person is an Applicant or Employee_Applicant
157 -- then set the return value to TRUE else set to FALSE
158 --
159 If v_system_person_type in ('APL','EMP_APL') then
160 --
161 v_return_value := 'TRUE';
162 --
163 Else
164 --
165 v_return_value := 'FALSE';
166 --
167 End If;
168 --
169 -- If a system person type has been entered then check
170 -- that the person's system person type matches the
171 -- one entered as part of the query criteria.
172 --
173 Else
174 --
175 hr_utility.set_location(l_proc,50);
176 --
177 -- Retrieve the system person type for this employee
178 --
179 Open c_get_system_type_name(p_person_type_id);
180 Fetch c_get_system_type_name Into v_system_person_type;
181 Close c_get_system_type_name;
182 --
183 -- Return TRUE if the persons system type matches
184 -- the one entered as part of the query criteria
185 --
186 If v_system_person_type = v_criteria_system_person_type Then
187 --
188 v_return_value := 'TRUE';
189 --
190 -- If the persons system type is an Employee_Applicant or
191 -- Applicant AND the query criteria is for BOTH then return
192 -- TRUE.
193 --
194 Elsif v_criteria_system_person_type = 'BOTH' and
195 v_system_person_type IN ('APL','EMP_APL') Then
196 --
197 v_return_value := 'TRUE';
198 --
199 -- If they don't match return FALSE
200 --
201 Else
202 --
203 v_return_value := 'FALSE';
204 --
205 End If;
206 --
207 End If;
208 --
209 hr_utility.set_location('Leaving '||l_proc,80);
210 --
211 RETURN(v_return_value);
212 --
213 END Is_Type_An_Applicant_Type;
214 --
215 -- ---------------------------------------------------------------------------+
216 -- |-------------------------< IS_TYPE_A_SYSTEM_TYPE >------------------------+
217 -- ---------------------------------------------------------------------------+
218 --
219 FUNCTION Is_Type_A_System_Type2
220 (p_person_type_id IN per_person_types.person_type_id%TYPE)
221 RETURN CHAR IS
222 --
223 Cursor c_get_system_person_type Is
224 Select information22
225 from pqh_copy_entity_attribs
226 Where information22 IS NOT NULL
227 And row_type_cd = 'CRITERIA'
228 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
229 --
230 Cursor c_get_system_type_name(p_person_type_id IN
231 per_person_types.person_type_id%TYPE) Is
232 Select ppt.system_person_type
233 From per_person_types ppt
234 Where ppt.person_type_id = p_person_type_id;
235 --
236 v_system_person_type per_person_types.system_person_type%TYPE := NULL;
237 v_criteria_system_person_type per_person_types.system_person_type%TYPE := NULL;
238 v_dummy_field varchar2(50) := NULL;
239 v_return_value varchar2(5);
240 --
241 l_proc varchar2(72) := g_package||'Is_Type_A_System_Type';
242 --
243 BEGIN
244 --
245 hr_utility.set_location('Entering:'|| l_proc, 10);
246 --
247 -- Check to see if a system person type has been
248 -- entered as part of the query criteria in the form.
249 --
250 Open c_get_system_person_type;
251 Fetch c_get_system_person_type into v_criteria_system_person_type;
252 --
253 hr_utility.set_location(l_proc,20);
254 --
255 -- If no system person type has been entered then
256 -- check that the employee is either an Employee
257 -- or an Employee_Applicant.
258 --
259 If c_get_system_person_type%NOTFOUND Then
260 --
261 hr_utility.set_location(l_proc,30);
262 --
263 close c_get_system_person_type;
264 --
265 -- Retrieve the system person type for this employee
266 --
267 Open c_get_system_type_name(p_person_type_id);
268 Fetch c_get_system_type_name Into v_system_person_type;
269 Close c_get_system_type_name;
270 --
271 hr_utility.set_location(l_proc,40);
272 --
273 -- If the person is an Employee or Employee_Applicant
274 -- then set the return value to TRUE else set to FALSE
275 --
276 If v_system_person_type in ('EMP','EMP_APL') then
277 --
278 v_return_value := 'TRUE';
279 --
280 Else
281 --
282 v_return_value := 'FALSE';
283 --
284 End If;
285 --
286 -- If a system person type has been entered then check
287 -- that the person's system person type matches the
288 -- one entered as part of the query criteria.
289 --
290 Else
291 --
292 hr_utility.set_location(l_proc,50);
293 --
294 -- Retrieve the system person type for this employee
295 --
296 Open c_get_system_type_name(p_person_type_id);
297 Fetch c_get_system_type_name Into v_system_person_type;
298 Close c_get_system_type_name;
299 --
300 -- Return TRUE if the persons system type matches
301 -- the one entered as part of the query criteria
302 --
303 If v_system_person_type = v_criteria_system_person_type Then
304 --
305 v_return_value := 'TRUE';
306 --
307 -- If the persons system type is an Employee_Applicant or
308 -- Employee AND the query criteria is for BOTH then return
309 -- TRUE.
310 --
311 Elsif v_criteria_system_person_type = 'BOTH' and
312 v_system_person_type IN ('EMP','EMP_APL') Then
313 --
314 v_return_value := 'TRUE';
315 --
316 -- If they don't match return FALSE
317 --
318 Else
319 --
320 v_return_value := 'FALSE';
321 --
322 End If;
323 --
324 End If;
325 --
326 hr_utility.set_location('Leaving '||l_proc,80);
327 --
328 RETURN(v_return_value);
329 --
330 END Is_Type_A_System_Type2;
331 --
332 -- ---------------------------------------------------------------------------+
333 -- |------------------------< IS_PERSON_CORRECT_TYPE >------------------------+
334 -- ---------------------------------------------------------------------------+
335 --
336 FUNCTION Is_Person_Correct_Type
337 (p_person_type_id IN per_person_types.person_type_id%TYPE)
338 RETURN CHAR IS
339 --
340 Cursor c_get_criteria_person_type Is
341 Select NVL(check_information1,'N'),
342 NVL(check_information2,'N'),
343 NVL(check_information3,'N')
344 from pqh_copy_entity_attribs
345 Where (check_information1 IS NOT NULL OR
346 check_information2 IS NOT NULL OR
347 check_information3 IS NOT NULL)
348 and row_type_cd = 'CRITERIA'
349 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
350 --
351 Cursor c_get_system_type_name(p_person_type_id IN
352 per_person_types.person_type_id%TYPE) Is
353 Select ppt.system_person_type
354 From per_person_types ppt
355 Where ppt.person_type_id = p_person_type_id;
356 --
357 v_system_person_type per_person_types.system_person_type%TYPE := NULL;
358 v_emp VARCHAR2(3) := NULL;
359 v_apl VARCHAR2(3) := NULL;
360 v_cwk VARCHAR2(3) := NULL;
361 v_dummy_field VARCHAR2(50) := NULL;
362 v_return_value VARCHAR2(5);
363 --
364 l_proc VARCHAR2(72) := g_package||'Is_Person_Correct_Type';
365 --
366 BEGIN
367 --
368 hr_utility.set_location('Entering:'||pqh_gen_form.g_txn_id||'/'|| l_proc, 10);
369 --
370 IF pqh_gen_form.g_txn_id IS NULL THEN
371 --
372 hr_utility.set_location(l_proc,15);
373 --
374 pqh_gen_form.g_txn_id := pqh_generic.g_txn_id; -- #3553723
375 -- RETURN ( 'TRUE');-- #3553723
376 --
377 END IF;
378 --
379 OPEN c_get_criteria_person_type;
380 FETCH c_get_criteria_person_type INTO v_emp, v_cwk, v_apl;
381 --
382 hr_utility.set_location(l_proc||v_emp||v_cwk||v_apl,20);
383 --
384 IF c_get_criteria_person_type%FOUND Then
385 --
386 hr_utility.set_location(l_proc,30);
387 --
388 CLOSE c_get_criteria_person_type;
389 --
390 -- Fetch the persons system person type name
391 --
392 OPEN c_get_system_type_name(p_person_type_id);
393 FETCH c_get_system_type_name INTO v_system_person_type;
394 CLOSE c_get_system_type_name;
395 --
396 hr_utility.set_location(l_proc||v_system_person_type,40);
397 --
398 v_return_value := 'FALSE';
399 --
400 IF v_system_person_type IN ('EMP', 'EMP_APL') AND
401 v_emp = 'Y' THEN
402 --
403 hr_utility.set_location(l_proc,50);
404 --
405 v_return_value := 'TRUE';
406 --
407 ELSIF v_system_person_type IN ('APL', 'APL_EX_APL', 'EMP_APL', 'EX_EMP_APL') AND
408 v_apl = 'Y' THEN
409 --
410 hr_utility.set_location(l_proc,60);
411 --
412 v_return_value := 'TRUE';
413 --
414 ELSIF v_system_person_type IN ('CWK') AND
415 v_cwk = 'Y' THEN
416 --
417 hr_utility.set_location(l_proc,70);
418 --
419 v_return_value := 'TRUE';
420 --
421 END IF;
422 --
423 hr_utility.set_location(l_proc,80);
424 --
425 END IF;
426 --
427 -- Bug fix 3547257
428 if ( nvl(v_emp,'N') <>'Y' and nvl(v_cwk,'N') <>'Y') then
429 hr_utility.set_message(800,'HR_449558_NO_CHECK_BOX');
430 hr_utility.raise_error;
431 end if;
432 --
433 hr_utility.set_location('Leaving '||l_proc,999);
434 --
435 RETURN(v_return_value);
436 --
437 END Is_Person_Correct_Type;
438 --
439 -- ---------------------------------------------------------------------------+
440 -- |------------------------< IS_TYPE_A_SYSTEM_TYPE >-------------------------+
441 -- ---------------------------------------------------------------------------+
442 --
443 FUNCTION Is_Type_A_System_Type
444 (p_person_type_id IN per_person_types.person_type_id%TYPE)
445 RETURN CHAR IS
446 --
447 Cursor c_get_system_person_type Is
448 Select information22
449 from pqh_copy_entity_attribs
450 Where information22 IS NOT NULL
451 And row_type_cd = 'CRITERIA'
452 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
453 --
454 Cursor c_compare_person_type(p_system_person_type IN
455 per_person_types.system_person_type%TYPE) Is
456 Select 'x'
457 From per_person_types ppt
458 Where ppt.system_person_type = p_system_person_type
459 and ppt.person_type_id = p_person_type_id;
460 --
461 Cursor c_get_system_type_name(p_person_type_id IN
462 per_person_types.person_type_id%TYPE) Is
463 Select ppt.system_person_type
464 From per_person_types ppt
465 Where ppt.person_type_id = p_person_type_id;
466 --
467 v_system_person_type per_person_types.system_person_type%TYPE := NULL;
468 v_dummy_field Varchar2(50) := NULL;
469 v_return_value varchar2(5);
470 --
471 l_proc varchar2(72) := g_package||'Is_Type_A_System_Type';
472 --
473 BEGIN
474 --
475 hr_utility.set_location('Entering:'|| l_proc, 10);
476 --
477 Open c_get_system_person_type;
478 Fetch c_get_system_person_type into v_system_person_type;
479 --
480 hr_utility.set_location(l_proc,20);
481 --
482 If c_get_system_person_type%NOTFOUND Then
483 --
484 hr_utility.set_location(l_proc,30);
485 --
486 close c_get_system_person_type;
487 --
488 Open c_get_system_type_name(p_person_type_id);
489 Fetch c_get_system_type_name Into v_system_person_type;
490 Close c_get_system_type_name;
491 --
492 hr_utility.set_location(l_proc,40);
493 --
494 If v_system_person_type in ('EMP','EMP_APL') then
495 --
496 v_return_value := 'TRUE';
497 --
498 Else
499 --
500 v_return_value := 'FALSE';
501 --
502 End If;
503 --
504 Else
505 --
506 hr_utility.set_location(l_proc,50);
507 --
508 Open c_compare_person_type(v_system_person_type);
509 Fetch c_compare_person_type Into v_dummy_field;
510 --
511 If c_compare_person_type%NOTFOUND Then
512 --
513 hr_utility.set_location(l_proc,60);
514 --
515 close c_compare_person_type;
516 v_return_value := 'FALSE';
517 --
518 Else
519 --
520 hr_utility.set_location(l_proc,70);
521 --
522 close c_compare_person_type;
523 v_return_value := 'TRUE';
524 --
525 End If;
526 --
527 End If;
528 --
529 hr_utility.set_location('Leaving '||l_proc,80);
530 --
531 RETURN(v_return_value);
532 --
533 END Is_Type_A_System_Type;
534 --
535 --
536 -- ---------------------------------------------------------------------------+
537 -- |------------------------------< IS_ORG_A_NODE >---------------------------+
538 -- ---------------------------------------------------------------------------+
539 --
540 FUNCTION Is_Org_A_Node
541 (p_search_org_id IN hr_organization_units.organization_id%TYPE
542 ,p_organization_structure_id IN per_org_structure_versions_v.organization_structure_id%TYPE)
543 RETURN CHAR IS
544 --
545 Cursor c_get_structure_version Is
546 Select posvv.org_structure_version_id version_id
547 From per_organization_structures_v posv,
548 per_org_structure_versions_v posvv,
549 fnd_sessions fs
550 Where posvv.organization_structure_id = posv.organization_structure_id
551 and fs.effective_date Between posvv.date_from
552 and NVL(posvv.date_to,hr_general.end_of_time)
553 And posv.organization_structure_id = p_organization_structure_id
554 And fs.session_id = userenv('sessionid');
555 --
556 -- Bug fix 3648688.
557 -- Cursor modified to improve performance.
558 -- per_org_structure_elements is used instead of per_org_structure_elements_v.
559
560 Cursor c_orgs_in_hierarchy
561 (p_version_id IN per_org_structure_versions_v.organization_structure_id%TYPE) IS
562 select posev.organization_id_parent org_id
563 from per_org_structure_elements posev
564 where posev.org_Structure_version_id = p_version_id
565 UNION
566 select posev.organization_id_child org_id
567 from per_org_structure_elements posev
568 where posev.org_Structure_version_id = p_version_id;
569 --
570 v_org_in_hierarchy BOOLEAN := FALSE;
571 v_users_starting_node VARCHAR2(240) := NULL;
572 v_return_message VARCHAR2(5);
573 --
574 v_version_id per_org_structure_versions_v.organization_structure_id%TYPE := NULL;
575 --
576 l_proc varchar2(72) := g_package||'Is_Org_A_Node';
577 --
578 BEGIN
579 --
580 hr_utility.set_location('Entering:'|| l_proc, 10);
581 --
582 Open c_get_structure_version;
583 Fetch c_get_structure_version Into v_version_id;
584 Close c_get_structure_version;
585 --
586 hr_utility.set_location(l_proc,20);
587 --
588 For c_rec in c_orgs_in_hierarchy(v_version_id) Loop
589 --
590 If c_rec.org_id = p_search_org_id Then
591 --
592 v_org_in_hierarchy := TRUE;
593 --
594 End If;
595 --
596 Exit When v_org_in_hierarchy;
597 --
598 End Loop;
599 --
600 hr_utility.set_location(l_proc,30);
601 --
602 if v_org_in_hierarchy Then
603 --
604 v_return_message := 'TRUE';
605 --
606 hr_utility.set_location(l_proc,40);
607 --
608 ElsIf Not v_org_in_hierarchy Then
609 --
610 v_return_message := 'FALSE';
611 --
612 hr_utility.set_location(l_proc,50);
613 --
614 End If;
615 --
616 hr_utility.set_location('Leaving'|| l_proc, 999);
617 --
618 Return(v_return_message);
619 --
620 END Is_Org_A_Node;
621 --
622 -- ---------------------------------------------------------------------------+
623 -- |-------------------------< IS_ORG_IN_HIERARCHY >--------------------------+
624 -- ---------------------------------------------------------------------------+
625 --
626 FUNCTION Is_Org_In_Hierarchy
627 (p_search_org_id IN hr_organization_units.organization_id%TYPE)
628 RETURN CHAR IS
629 --
630 Cursor c_is_field_populated Is
631 Select to_number(information2),
632 to_number(information3)
633 from pqh_copy_entity_attribs
634 Where information2 IS NOT NULL
635 And row_type_cd = 'CRITERIA'
636 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
637 --
638 Cursor c_get_hierarchy_root(p_org_hierarchy_id number) is
639 select organization_id_parent
640 from per_org_structure_elements ose,
641 per_org_structure_versions osv
642 where osv.organization_structure_id = p_org_hierarchy_id
643 and hr_general.effective_date between osv.date_from
644 and nvl(osv.date_to,hr_general.end_of_time)
645 and osv.org_structure_version_id = ose.org_structure_version_id
646 and not exists
647 (select 'X'
648 from per_org_structure_elements ose2
649 where ose.organization_id_parent = ose2.organization_id_child
650 and osv.org_structure_version_id=ose2.org_structure_version_id);
651 --
652 v_org_in_hierarchy BOOLEAN := FALSE;
653 v_return_message VARCHAR2(5);
654 v_org_hierarchy_id number;
655 v_starting_node number;
656 l_dummy varchar2(1);
657 --
658 l_proc varchar2(72) := g_package||'Is_Org_In_hierarchy';
659 --
660 BEGIN
661 --
662 hr_utility.set_location('Entering:'|| l_proc||'/'||p_search_org_Id, 10);
663 --
664 /*
665 ** This tells us whether org hierarchy query criteria
666 ** has been entered for this mass update.
667 */
668 Open c_is_field_populated;
669 Fetch c_is_field_populated into v_org_hierarchy_id, v_starting_node;
670 Close c_is_field_populated;
671
672 if v_org_hierarchy_id is null then
673 /*
674 ** No org criteria has been entered so exit returning TRUE.
675 */
676 hr_utility.set_location(l_proc,20);
677
678 return('TRUE');
679
680 else
681
682 if g_org_hierarchy_id is null then
683 /*
684 ** Global org hierarchy not set...
685 */
686 g_org_hierarchy_id := v_org_hierarchy_id;
687 g_org_starting_node := v_starting_node;
688 g_org_hierarchy_root := null;
689 if v_starting_node is null then
690 /*
691 ** derive the root node.
692 */
693 hr_utility.set_location(l_proc,22);
694 open c_get_hierarchy_root(v_org_hierarchy_id);
695 fetch c_get_hierarchy_root into g_org_hierarchy_root;
696 close c_get_hierarchy_root;
697 end if;
698 end if;
699
700 if g_org_hierarchy_id <> v_org_hierarchy_id OR
701 nvl(g_org_starting_node,hr_api.g_number) <>
702 nvl(v_starting_node,hr_api.g_number) then
703 /*
704 ** Org hierarchy has changed...
705 */
706 g_org_hierarchy_id := v_org_hierarchy_id;
707 g_org_starting_node := v_starting_node;
708 g_org_hierarchy_root := null;
709 if v_starting_node is null then
710 /*
711 ** derive the root node.
712 */
713 hr_utility.set_location(l_proc,25);
714 open c_get_hierarchy_root(v_org_hierarchy_id);
715 fetch c_get_hierarchy_root into g_org_hierarchy_root;
716 close c_get_hierarchy_root;
717 end if;
718 end if;
719
720 hr_utility.set_location(l_proc||'/'||g_org_hierarchy_id||'/'||
721 g_org_starting_node||'/'||g_org_hierarchy_root,30);
722 --
723 --
724 If g_org_starting_node IS NULL and
725 p_search_org_id <> g_org_hierarchy_root Then
726 --
727 hr_utility.set_location(l_proc,50);
728 /*
729 ** I've got an org hierarchy but no starting node so the search org
730 ** can appear anywhere in the hierarchy. We'll implement this as a tree
731 ** walk up to the root node.
732 */
733 begin
734 l_dummy := null;
735 select 'X'
736 into l_dummy
737 from dual
738 where g_org_hierarchy_root in (
739 SELECT o.organization_id_parent
740 FROM per_org_structure_elements o
741 CONNECT BY o.organization_id_child = PRIOR o.organization_id_parent
742 AND o.org_structure_version_id = PRIOR o.org_structure_version_id
743 START WITH o.organization_id_child = p_search_org_id
744 AND o.org_structure_version_id =
745 (SELECT v.org_structure_version_id
746 FROM per_org_structure_versions v
747 WHERE v.organization_structure_id = g_org_hierarchy_id
748 AND hr_general.effective_date BETWEEN v.date_from
749 AND NVL(v.date_to, hr_general.end_of_time)));
750 if l_dummy = 'X' then
751 hr_utility.set_location(l_proc,52);
752 v_org_in_hierarchy := TRUE;
753 end if;
754 exception
755 when no_data_found then
756 hr_utility.set_location(l_proc,55);
757 v_org_in_hierarchy := FALSE;
758
759 when others then
760 raise;
761
762 end;
763 --
764 Elsif g_org_starting_node IS NULL and
765 p_search_org_id = g_org_hierarchy_root Then
766 --
767 hr_utility.set_location(l_proc,60);
768 /*
769 ** I've not got a starting node but the search node is the root
770 ** node
771 */
772 v_org_in_hierarchy := TRUE;
773 --
774 ElsIf g_org_starting_node IS NOT NULL AND
775 g_org_starting_node = p_search_org_id Then
776 --
777 hr_utility.set_location(l_proc,65);
778 /*
779 ** I've got a starting node and it's the search org
780 */
781 --
782 v_org_in_hierarchy := TRUE;
783 --
784 ElsIf g_org_starting_node IS NOT NULL AND
785 g_org_starting_node <> p_search_org_id Then
786 --
787 hr_utility.set_location(l_proc,70);
788 /*
789 ** I've got a starting node, it's not the search org so search
790 ** the hierarchy from the search org looking for the
791 ** starting node.
792 */
793 begin
794 l_dummy := null;
795 select 'X'
796 into l_dummy
797 from dual
798 where g_org_starting_node in (
799 SELECT o.organization_id_parent
800 FROM per_org_structure_elements o
801 CONNECT BY o.organization_id_child = PRIOR o.organization_id_parent
802 AND o.org_structure_version_id = PRIOR o.org_structure_version_id
803 START WITH o.organization_id_child = p_search_org_id
804 AND o.org_structure_version_id =
805 (SELECT v.org_structure_version_id
806 FROM per_org_structure_versions v
807 WHERE v.organization_structure_id = g_org_hierarchy_id
808 AND hr_general.effective_date BETWEEN v.date_from
809 AND NVL(v.date_to, hr_general.end_of_time)));
810 if l_dummy = 'X' then
811 v_org_in_hierarchy := TRUE;
812 end if;
813 exception
814 when no_data_found then
815 hr_utility.set_location(l_proc,75);
816 v_org_in_hierarchy := FALSE;
817
818 when others then
819 raise;
820
821 end;
822 --
823 End If;
824 --
825 End If;
826 --
827 If v_org_in_hierarchy Then
828 --
829 v_return_message := 'TRUE';
830 --
831 ElsIf Not v_org_in_hierarchy Then
832 --
833 v_return_message := 'FALSE';
834 --
835 End If;
836 --
837 hr_utility.set_location('Leaving: '||l_proc||' returning '
838 ||v_return_message,999);
839 --
840 Return(v_return_message);
841 --
842 END Is_Org_In_Hierarchy;
843 --
844 -- ---------------------------------------------------------------------------+
845 -- |------------------------------< IS_POS_A_NODE >---------------------------+
846 -- ---------------------------------------------------------------------------+
847 --
848 FUNCTION Is_Pos_A_Node
849 (p_search_pos_id IN per_positions.position_id%TYPE
850 ,p_position_structure_id IN per_pos_structure_versions_v.position_structure_id%TYPE
851 ,p_effective_date IN DATE)
852 RETURN CHAR IS
853 --
854 Cursor c_get_structure_version Is
855 Select ppsvv.pos_structure_version_id version_id
856 From per_position_structures_v ppsv,
857 per_pos_structure_versions_v ppsvv
858 Where ppsvv.position_structure_id = ppsv.position_structure_id
859 And ppsv.position_structure_id = p_position_structure_id
860 and p_effective_date between to_date(date_from,'DD/MM/YYYY') and
861 NVL(to_date(date_to,'DD/MM/YYYY'),p_effective_date);
862 --
863 -- Bug fix 3648688.
864 -- cursor modified to improve performance.
865 -- per_pos_structure_elements is used instead of per_pos_structure_elements_v.
866
867 Cursor c_pos_in_hierarchy
868 (p_version_id IN per_pos_structure_versions_v.position_structure_id%TYPE) IS
869 select ppsev.parent_position_id pos_id
870 from per_pos_structure_elements ppsev
871 where ppsev.pos_structure_version_id = p_version_id
872 UNION
873 select ppsev.subordinate_position_id pos_id
874 from per_pos_structure_elements ppsev
875 where ppsev.pos_structure_version_id = p_version_id;
876 --
877 v_pos_in_hierarchy BOOLEAN := FALSE;
878 v_users_starting_node VARCHAR2(240) := NULL;
879 v_return_message VARCHAR2(5);
880 --
881 v_version_id per_pos_structure_versions_v.position_structure_id%TYPE := NULL;
882 --
883 l_proc varchar2(72) := g_package||'Is_Pos_A_Node';
884 --
885 BEGIN
886 --
887 hr_utility.set_location('Entering: '||l_proc,10);
888 --
889 Open c_get_structure_version;
890 Fetch c_get_structure_version Into v_version_id;
891 Close c_get_structure_version;
892 --
893 hr_utility.set_location(l_proc,20);
894 --
895 For c_rec in c_pos_in_hierarchy(v_version_id) Loop
896 --
897 If c_rec.pos_id = p_search_pos_id Then
898 --
899 v_pos_in_hierarchy := TRUE;
900 --
901 End If;
902 --
903 Exit When v_pos_in_hierarchy;
904 --
905 End Loop;
906 --
907 hr_utility.set_location('Leaving: '||l_proc,30);
908 --
909 if v_pos_in_hierarchy Then
910 --
911 v_return_message := 'TRUE';
912 --
913 ElsIf Not v_pos_in_hierarchy Then
914 --
915 v_return_message := 'FALSE';
916 --
917 End If;
918 --
919 hr_utility.set_location('Leaving: '||l_proc,40);
920 --
921 Return(v_return_message);
922 --
923 END Is_Pos_A_Node;
924 --
925 -- ---------------------------------------------------------------------------+
926 -- |-------------------------< IS_POSITION_IN_HIERARCHY >---------------------+
927 -- ---------------------------------------------------------------------------+
928 --
929 FUNCTION Is_Position_In_Hierarchy
930 (p_search_pos_id IN hr_all_positions_f.position_id%TYPE)
931 RETURN CHAR IS
932 --
933 Cursor c_get_hierarchy_root(p_pos_hierarchy_id number) is
934 select parent_position_id
935 from per_pos_structure_elements ose,
936 per_pos_structure_versions osv
937 where osv.position_structure_id = p_pos_hierarchy_id
938 and hr_general.effective_date between osv.date_from
939 and nvl(osv.date_to,hr_general.end_of_time)
940 and osv.pos_structure_version_id = ose.pos_structure_version_id
941 and not exists
942 (select 'X'
943 from per_pos_structure_elements ose2
944 where ose.parent_position_id= ose2.subordinate_position_id
945 and osv.pos_structure_version_id=ose2.pos_structure_version_id);
946 --
947 Cursor c_is_field_populated Is
948 Select to_number(information8),
949 to_number(information9)
950 from pqh_copy_entity_attribs
951 Where information8 IS NOT NULL
952 And row_type_cd = 'CRITERIA'
953 And copy_entity_txn_id = pqh_gen_form.g_txn_id;
954 --
955 v_pos_in_hierarchy BOOLEAN := FALSE;
956 v_return_message VARCHAR2(5);
957 v_pos_hierarchy number;
958 v_starting_node number;
959 l_dummy varchar2(1);
960 --
961 l_proc varchar2(72) := g_package||'Is_Position_In_Hierarchy';
962 --
963 BEGIN
964 --
965 hr_utility.set_location('Entering: '||l_proc||'/'||p_search_pos_id,10);
966 Open c_is_field_populated;
967 Fetch c_is_field_populated into v_pos_hierarchy, v_starting_node;
968 Close c_is_field_populated;
969 --
970 --
971 If v_pos_hierarchy is null Then
972 /*
973 ** No pos hierarchy criteria has been entered or the
974 ** assignment has no position so exit returning TRUE.
975 */
976 hr_utility.set_location(l_proc,20);
977
978 return('TRUE');
979 --
980 Else
981 --
982 if g_pos_hierarchy_id is null then
983 /*
984 ** Global pos hierarchy not set...
985 */
986 g_pos_hierarchy_id := v_pos_hierarchy;
987 g_pos_starting_node := v_starting_node;
988 g_pos_hierarchy_root := null;
989 if v_starting_node is null then
990 /*
991 ** derive the root node.
992 */
993 hr_utility.set_location(l_proc,22);
994 open c_get_hierarchy_root(v_pos_hierarchy);
995 fetch c_get_hierarchy_root into g_pos_hierarchy_root;
996 close c_get_hierarchy_root;
997 end if;
998 end if;
999
1000 if g_pos_hierarchy_id <> v_pos_hierarchy OR
1001 nvl(g_pos_starting_node,hr_api.g_number) <>
1002 nvl(v_starting_node,hr_api.g_number)then
1003 /*
1004 ** Org hierarchy has changed...
1005 */
1006 g_pos_hierarchy_id := v_pos_hierarchy;
1007 g_pos_starting_node := v_starting_node;
1008 g_pos_hierarchy_root := null;
1009 if v_starting_node is null then
1010 /*
1011 ** derive the root node.
1012 */
1013 hr_utility.set_location(l_proc,25);
1014 open c_get_hierarchy_root(v_pos_hierarchy);
1015 fetch c_get_hierarchy_root into g_pos_hierarchy_root;
1016 close c_get_hierarchy_root;
1017 end if;
1018 end if;
1019
1020 hr_utility.set_location(l_proc||'/'||g_pos_hierarchy_id||'/'||
1021 g_pos_starting_node||'/'||g_pos_hierarchy_root,30);
1022 --
1023 if g_pos_starting_node IS NULL and
1024 p_search_pos_id <> g_pos_hierarchy_root then
1025
1026 hr_utility.set_location(l_proc,40);
1027 /*
1028 ** I've got a pos hierarchy but no starting node so the search pos
1029 ** just needs to appear in the hierarchy. Implement this as a tree
1030 ** walk up the hierarchy to the root node.
1031 */
1032 begin
1033 l_dummy := null;
1034 select 'X'
1035 into l_dummy
1036 from dual
1037 where g_pos_hierarchy_root in (
1038 SELECT o.parent_position_id
1039 FROM per_pos_structure_elements o
1040 CONNECT BY o.subordinate_position_id = PRIOR o.parent_position_id
1041 AND o.pos_structure_version_id = PRIOR o.pos_structure_version_id
1042 START WITH o.subordinate_position_id = p_search_pos_id
1043 AND o.pos_structure_version_id =
1044 (SELECT v.pos_structure_version_id
1045 FROM per_pos_structure_versions v
1046 WHERE v.position_structure_id = g_pos_hierarchy_id
1047 AND hr_general.effective_date BETWEEN v.date_from
1048 AND NVL(v.date_to, hr_general.end_of_time)));
1049 if l_dummy = 'X' then
1050 hr_utility.set_location(l_proc,50);
1051 v_pos_in_hierarchy := TRUE;
1052 end if;
1053 exception
1054 when no_data_found then
1055 hr_utility.set_location(l_proc,60);
1056 v_pos_in_hierarchy := FALSE;
1057
1058 when others then
1059 raise;
1060
1061 end;
1062 Elsif g_pos_starting_node IS NULL and
1063 p_search_pos_id = g_pos_hierarchy_root Then
1064 --
1065 hr_utility.set_location(l_proc,70);
1066 /*
1067 ** I've not got a starting node but the search node is the root
1068 ** node
1069 */
1070 v_pos_in_hierarchy := TRUE;
1071 --
1072 ElsIf g_pos_starting_node IS NOT NULL AND
1073 g_pos_starting_node = p_search_pos_id Then
1074 --
1075 hr_utility.set_location(l_proc,80);
1076 /*
1077 ** I've got a starting node and it's the search pos
1078 */
1079 --
1080 v_pos_in_hierarchy := TRUE;
1081 --
1082 ElsIf g_pos_starting_node IS NOT NULL AND
1083 g_pos_starting_node <> p_search_pos_id Then
1084 --
1085 hr_utility.set_location(l_proc,90);
1086 /*
1087 ** I've got a starting node, it's not the search pos so search
1088 ** the hierarchy from the search pos looking for the
1089 ** starting node.
1090 */
1091 begin
1092 l_dummy := null;
1093 select 'X'
1094 into l_dummy
1095 from dual
1096 where g_pos_starting_node in (
1097 SELECT o.parent_position_id
1098 FROM per_pos_structure_elements o
1099 CONNECT BY o.subordinate_position_id = PRIOR o.parent_position_id
1100 AND o.pos_structure_version_id = PRIOR o.pos_structure_version_id
1101 START WITH o.subordinate_position_id = p_search_pos_id
1102 AND o.pos_structure_version_id =
1103 (SELECT v.pos_structure_version_id
1104 FROM per_pos_structure_versions v
1105 WHERE v.position_structure_id = g_pos_hierarchy_id
1106 AND hr_general.effective_date BETWEEN v.date_from
1107 AND NVL(v.date_to, hr_general.end_of_time)));
1108 if l_dummy = 'X' then
1109 hr_utility.set_location(l_proc,100);
1110 v_pos_in_hierarchy := TRUE;
1111 end if;
1112 exception
1113 when no_data_found then
1114 hr_utility.set_location(l_proc,110);
1115 v_pos_in_hierarchy := FALSE;
1116
1117 when others then
1118 raise;
1119
1120 end;
1121 --
1122 End If;
1123 --
1124 End If;
1125 --
1126 If v_pos_in_hierarchy Then
1127 --
1128 v_return_message := 'TRUE';
1129 --
1130 ElsIf Not v_pos_in_hierarchy Then
1131 --
1132 v_return_message := 'FALSE';
1133 --
1134 End If;
1135 --
1136 hr_utility.set_location('Leaving: '||l_proc||' returning '
1137 ||v_return_message,120);
1138 --
1139 Return(v_return_message);
1140 --
1141 END Is_Position_In_Hierarchy;
1142 --
1143 -- ---------------------------------------------------------------------------+
1144 -- |------------------------------< WRITE_HEADER >----------------------------+
1145 -- ---------------------------------------------------------------------------+
1146 --
1147 PROCEDURE Write_Header(p_assignment_number IN
1148 per_assignments_f.assignment_number%TYPE) IS
1149 --
1150 l_proc varchar2(72) := g_package||'Write_Header';
1151 --
1152 BEGIN
1153 --
1154 hr_utility.set_location('Entering: '||l_proc,10);
1155 --
1156 fnd_file.new_line(fnd_file.log,1);
1157 fnd_file.put_line(fnd_file.log,'Errors For Assignment Number '||p_assignment_number);
1158 fnd_file.put_line(fnd_file.log,'==================================');
1159 --fnd_file.new_line(fnd_file.log,1);
1160 --
1161 hr_utility.set_location('Leaving: '||l_proc,20);
1162 --
1163 END Write_Header;
1164 --
1165 -- ---------------------------------------------------------------------------+
1166 -- |-------------------------------< LOG_ERROR >------------------------------+
1167 -- ---------------------------------------------------------------------------+
1168 --
1169 PROCEDURE Log_Error(p_type IN VARCHAR2,
1170 p_assignment_number IN per_assignments_f.assignment_number%TYPE,
1171 p_warning_message IN VARCHAR2,
1172 p_already_errored IN OUT NOCOPY BOOLEAN) IS
1173 --
1174 v_error_message VARCHAR2(255) := NULL;
1175 --
1176 l_proc VARCHAR2(72) := g_package||'Log_Error';
1177 --
1178 l_already_errored BOOLEAN := p_already_errored;
1179 --
1180 BEGIN
1181 --
1182 hr_utility.set_location('Entering: '||l_proc,10);
1183 --
1184 If p_already_errored = FALSE Then
1185 --
1186 Write_Header(p_assignment_number);
1187 p_already_errored := TRUE;
1188 --
1189 End If;
1190 --
1191 fnd_file.put_line(fnd_file.log,'TYPE = '||p_type);
1192 --
1193 If p_type = 'ERROR' Then
1194 --
1195 hr_utility.set_location(l_proc,20);
1196 --
1197 fnd_file.new_line(fnd_file.log,1);
1198 fnd_file.put_line(fnd_file.log,SQLERRM);
1199 --
1200 --v_error_message := rtrim(substr(SQLERRM,1,INSTR(SQLERRM,'Cause:')-1),' ');
1201 --fnd_file.put_line(fnd_file.log,v_error_message);
1202 --
1203 -- v_error_message :=
1204 -- substr(SQLERRM,INSTR(SQLERRM,'Cause:'),INSTR(SQLERRM,'Action:')-1);
1205 -- fnd_file.put_line(fnd_file.log,v_error_message);
1206 --
1207 ElsIf p_type = 'WARNING' Then
1208 --
1209 hr_utility.set_location(l_proc,30);
1210 --
1211 fnd_file.put_line(fnd_file.log,p_warning_message);
1212 --
1213 End If;
1214 --
1215 hr_utility.set_location('Leaving: '||l_proc,40);
1216 --
1217 EXCEPTION
1218 when others then
1219 p_already_errored := l_already_errored;
1220 RAISE;
1221
1222 END Log_Error;
1223 --
1224 -- ---------------------------------------------------------------------------+
1225 -- |-------------------------< CHK_FOR_NON_APL_FIELDS >-----------------------+
1226 -- ---------------------------------------------------------------------------+
1227 --
1228 FUNCTION chk_for_non_apl_fields
1229 (p_hourly_salaried_code IN VARCHAR2
1230 ,p_labour_union_member_flag IN VARCHAR2
1231 ,p_project_title IN VARCHAR2
1232 ,p_vendor_assignment_number IN VARCHAR2
1233 ,p_vendor_employee_number IN VARCHAR2
1234 ,p_vendor_id IN NUMBER
1235 ,p_vendor_site_id IN NUMBER ) RETURN BOOLEAN IS
1236 --
1237 l_return_value BOOLEAN := FALSE;
1238 --
1239 BEGIN
1240 --
1241 IF p_hourly_salaried_code IS NOT NULL OR
1242 p_labour_union_member_flag IS NOT NULL OR
1243 p_project_title IS NOT NULL OR
1244 p_vendor_assignment_number IS NOT NULL OR
1245 p_vendor_employee_number IS NOT NULL OR
1246 p_vendor_id IS NOT NULL OR
1247 p_vendor_site_id IS NOT NULL THEN
1248 --
1249 l_return_value := TRUE;
1250 --
1251 END IF;
1252 --
1253 RETURN(l_return_value);
1254 --
1255 END chk_for_non_apl_fields;
1256 --
1257 -- ---------------------------------------------------------------------------+
1258 -- |-------------------------< CHK_FOR_NON_EMP_FIELDS >-----------------------+
1259 -- ---------------------------------------------------------------------------+
1260 --
1261 FUNCTION chk_for_non_emp_fields
1262 (p_application_id IN NUMBER
1263 ,p_person_referred_by_id IN NUMBER
1264 ,p_project_title IN VARCHAR2
1265 ,p_recruiter_id IN NUMBER
1266 ,p_recruitment_activity_id IN NUMBER
1267 ,p_source_organization_id IN NUMBER
1268 ,p_vacancy_id IN NUMBER
1269 ,p_vendor_assignment_number IN VARCHAR2
1270 ,p_vendor_employee_number IN VARCHAR2
1271 ,p_vendor_id IN NUMBER
1272 ,p_vendor_site_id IN NUMBER
1273 ,p_projected_assignment_end IN DATE) RETURN BOOLEAN IS
1274 --
1275 l_return_value BOOLEAN := FALSE;
1276 --
1277 BEGIN
1278 --
1279 IF nvl(p_application_id,hr_api.g_number) <> hr_api.g_number OR
1280 nvl(p_person_referred_by_id, hr_api.g_number) <> hr_api.g_number OR
1281 nvl(p_project_title, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1282 nvl(p_recruiter_id, hr_api.g_number) <> hr_api.g_number OR
1283 nvl(p_recruitment_activity_id,hr_api.g_number) <> hr_api.g_number OR
1284 nvl(p_source_organization_id, hr_api.g_number) <> hr_api.g_number OR
1285 nvl(p_vacancy_id, hr_api.g_number) <> hr_api.g_number OR
1286 nvl(p_vendor_assignment_number, hr_api.g_varchar2)<> hr_api.g_varchar2 OR
1287 nvl(p_vendor_employee_number, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1288 nvl(p_vendor_id, hr_api.g_number) <> hr_api.g_number OR
1289 nvl(p_vendor_site_id,hr_api.g_number) <> hr_api.g_number OR
1290 nvl(p_projected_assignment_end,hr_api.g_date) <> hr_api.g_date THEN
1291 --
1292 l_return_value := TRUE;
1293 --
1294 END IF;
1295 --
1296 RETURN(l_return_value);
1297 --
1298 END chk_for_non_emp_fields;
1299 --
1300 --
1301 -- ---------------------------------------------------------------------------+
1302 -- |-------------------------< CHK_FOR_NON_CWK_FIELDS >-----------------------+
1303 -- ---------------------------------------------------------------------------+
1304 --
1305 FUNCTION chk_for_non_cwk_fields
1306 (p_application_id IN NUMBER
1307 ,p_bargaining_unit_code IN VARCHAR2
1308 ,p_cag_segment1 IN VARCHAR2
1309 ,p_cag_segment10 IN VARCHAR2
1310 ,p_cag_segment11 IN VARCHAR2
1311 ,p_cag_segment12 IN VARCHAR2
1312 ,p_cag_segment13 IN VARCHAR2
1313 ,p_cag_segment14 IN VARCHAR2
1314 ,p_cag_segment15 IN VARCHAR2
1315 ,p_cag_segment16 IN VARCHAR2
1316 ,p_cag_segment17 IN VARCHAR2
1317 ,p_cag_segment18 IN VARCHAR2
1318 ,p_cag_segment19 IN VARCHAR2
1319 ,p_cag_segment2 IN VARCHAR2
1320 ,p_cag_segment20 IN VARCHAR2
1321 ,p_cag_segment3 IN VARCHAR2
1322 ,p_cag_segment4 IN VARCHAR2
1323 ,p_cag_segment5 IN VARCHAR2
1324 ,p_cag_segment6 IN VARCHAR2
1325 ,p_cag_segment7 IN VARCHAR2
1326 ,p_cag_segment8 IN VARCHAR2
1327 ,p_cag_segment9 IN VARCHAR2
1328 ,p_cagr_id_flex_num IN NUMBER
1329 ,p_collective_agreement_id IN NUMBER
1330 ,p_contract_id IN NUMBER
1331 ,p_date_probation_end IN DATE
1332 ,p_grade_ladder_pgm_id IN NUMBER
1333 ,p_grade_id IN NUMBER
1334 ,p_hourly_salaried_code IN VARCHAR2
1335 ,p_pay_basis_id IN NUMBER
1336 ,p_payroll_id IN NUMBER
1337 ,p_perf_review_period IN NUMBER
1338 ,p_perf_review_period_frequency IN VARCHAR2
1339 ,p_person_referred_by_id IN NUMBER
1340 ,p_probation_period IN NUMBER
1341 ,p_probation_unit IN VARCHAR2
1342 ,p_recruiter_id IN NUMBER
1343 ,p_recruitment_activity_id IN NUMBER
1344 ,p_sal_review_period IN NUMBER
1345 ,p_sal_review_period_frequency IN VARCHAR2
1346 ,p_source_organization_id IN NUMBER
1347 ,p_special_ceiling_step_id IN NUMBER
1348 ,p_vacancy_id IN NUMBER) RETURN BOOLEAN IS
1349 --
1350 l_return_value BOOLEAN := FALSE;
1351 --
1352 BEGIN
1353 --
1354 IF nvl(p_application_id,hr_api.g_number) <> hr_api.g_number OR
1355 nvl(p_bargaining_unit_code, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1356 nvl(p_cag_segment1 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1357 nvl(p_cag_segment10, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1358 nvl(p_cag_segment11 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1359 nvl(p_cag_segment12 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1360 nvl(p_cag_segment13, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1361 nvl(p_cag_segment14 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1362 nvl(p_cag_segment15 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1363 nvl(p_cag_segment16, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1364 nvl(p_cag_segment17, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1365 nvl(p_cag_segment18 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1366 nvl(p_cag_segment19 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1367 nvl(p_cag_segment2 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1368 nvl(p_cag_segment20 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1369 nvl(p_cag_segment3, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1370 nvl(p_cag_segment4, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1371 nvl(p_cag_segment5 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1372 nvl(p_cag_segment6, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1373 nvl(p_cag_segment7 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1374 nvl(p_cag_segment8 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1375 nvl(p_cag_segment9 , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1376 nvl(p_cagr_id_flex_num, hr_api.g_number) <> hr_api.g_number OR
1377 nvl(p_collective_agreement_id, hr_api.g_number) <> hr_api.g_number OR
1378 nvl(p_contract_id, hr_api.g_number) <> hr_api.g_number OR
1379 nvl(p_date_probation_end , hr_api.g_date) <> hr_api.g_date OR
1380 nvl(p_grade_ladder_pgm_id, hr_api.g_number) <> hr_api.g_number OR
1381 nvl(p_grade_id, hr_api.g_number) <> hr_api.g_number OR
1382 nvl(p_hourly_salaried_code, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1383 nvl(p_pay_basis_id, hr_api.g_number) <> hr_api.g_number OR
1384 nvl(p_payroll_id, hr_api.g_number) <> hr_api.g_number OR
1385 nvl(p_perf_review_period, hr_api.g_number) <> hr_api.g_number OR
1386 nvl(p_perf_review_period_frequency,hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1387 nvl(p_person_referred_by_id, hr_api.g_number) <> hr_api.g_number OR
1388 nvl(p_probation_period , hr_api.g_number) <> hr_api.g_number OR
1389 nvl(p_probation_unit , hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1390 nvl(p_recruiter_id, hr_api.g_number) <> hr_api.g_number OR
1391 nvl(p_recruitment_activity_id, hr_api.g_number) <> hr_api.g_number OR
1392 nvl(p_sal_review_period ,hr_api.g_number) <> hr_api.g_number OR
1393 nvl(p_sal_review_period_frequency, hr_api.g_varchar2) <> hr_api.g_varchar2 OR
1394 nvl(p_source_organization_id, hr_api.g_number) <> hr_api.g_number OR
1395 nvl(p_special_ceiling_step_id, hr_api.g_number) <> hr_api.g_number OR
1396 nvl(p_vacancy_id, hr_api.g_number) <> hr_api.g_number THEN
1397 --
1398 l_return_value := TRUE;
1399 --
1400 END IF;
1401 --
1402 RETURN(l_return_value);
1403 --
1404 END chk_for_non_cwk_fields;
1405 --
1406 --
1407 -- ---------------------------------------------------------------------------+
1408 -- |---------------------------------< UPD_ASG >------------------------------+
1409 -- ---------------------------------------------------------------------------+
1410 --
1411 PROCEDURE upd_asg (
1412 p_ASS_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1413 p_ASS_ATTRIBUTE1 IN VARCHAR2 ,
1414 p_ASS_ATTRIBUTE10 IN VARCHAR2 ,
1415 p_ASS_ATTRIBUTE11 IN VARCHAR2 ,
1416 p_ASS_ATTRIBUTE12 IN VARCHAR2 ,
1417 p_ASS_ATTRIBUTE13 IN VARCHAR2 ,
1418 p_ASS_ATTRIBUTE14 IN VARCHAR2 ,
1419 p_ASS_ATTRIBUTE15 IN VARCHAR2 ,
1420 p_ASS_ATTRIBUTE16 IN VARCHAR2 ,
1421 p_ASS_ATTRIBUTE17 IN VARCHAR2 ,
1422 p_ASS_ATTRIBUTE18 IN VARCHAR2 ,
1423 p_ASS_ATTRIBUTE19 IN VARCHAR2 ,
1424 p_ASS_ATTRIBUTE2 IN VARCHAR2 ,
1425 p_ASS_ATTRIBUTE20 IN VARCHAR2 ,
1426 p_ASS_ATTRIBUTE21 IN VARCHAR2 ,
1427 p_ASS_ATTRIBUTE22 IN VARCHAR2 ,
1428 p_ASS_ATTRIBUTE23 IN VARCHAR2 ,
1429 p_ASS_ATTRIBUTE24 IN VARCHAR2 ,
1430 p_ASS_ATTRIBUTE25 IN VARCHAR2 ,
1431 p_ASS_ATTRIBUTE26 IN VARCHAR2 ,
1432 p_ASS_ATTRIBUTE27 IN VARCHAR2 ,
1433 p_ASS_ATTRIBUTE28 IN VARCHAR2 ,
1434 p_ASS_ATTRIBUTE29 IN VARCHAR2 ,
1435 p_ASS_ATTRIBUTE3 IN VARCHAR2 ,
1436 p_ASS_ATTRIBUTE30 IN VARCHAR2 ,
1437 p_ASS_ATTRIBUTE4 IN VARCHAR2 ,
1438 p_ASS_ATTRIBUTE5 IN VARCHAR2 ,
1439 p_ASS_ATTRIBUTE6 IN VARCHAR2 ,
1440 p_ASS_ATTRIBUTE7 IN VARCHAR2 ,
1441 p_ASS_ATTRIBUTE8 IN VARCHAR2 ,
1442 p_ASS_ATTRIBUTE9 IN VARCHAR2 ,
1443 p_ASSIGNMENT_ID IN NUMBER ,
1444 p_ASSIGNMENT_NUMBER IN VARCHAR2 ,
1445 p_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER ,
1446 p_BARGAINING_UNIT_CODE IN VARCHAR2 ,
1447 p_CAGR_GRADE_DEF_ID IN OUT NOCOPY NUMBER ,
1448 p_CAGR_ID_FLEX_NUM IN NUMBER ,
1449 p_CHANGE_REASON IN VARCHAR2 ,
1450 p_COLLECTIVE_AGREEMENT_ID IN NUMBER ,
1451 p_COMMENT_ID OUT NOCOPY NUMBER ,
1452 p_CONTRACT_ID IN NUMBER ,
1453 p_DATE_PROBATION_END IN DATE ,
1454 p_DEFAULT_CODE_COMB_ID IN NUMBER ,
1455 p_ESTABLISHMENT_ID IN NUMBER ,
1456 p_EMPLOYMENT_CATEGORY IN VARCHAR2 ,
1457 p_FREQUENCY IN VARCHAR2 ,
1458 p_GRADE_ID IN NUMBER ,
1459 p_HOURLY_SALARIED_CODE IN VARCHAR2 ,
1460 p_INTERNAL_ADDRESS_LINE IN VARCHAR2 ,
1461 p_JOB_ID IN NUMBER ,
1462 p_LABOUR_UNION_MEMBER_FLAG IN VARCHAR2 ,
1463 p_LOCATION_ID IN NUMBER ,
1464 p_MANAGER_FLAG IN VARCHAR2 ,
1465 p_NORMAL_HOURS IN NUMBER ,
1466 p_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
1467 p_ORGANIZATION_ID IN OUT NOCOPY NUMBER ,
1468 p_PAY_BASIS_ID IN NUMBER ,
1469 p_PAYROLL_ID IN NUMBER ,
1470 p_PERF_REVIEW_PERIOD IN NUMBER ,
1471 p_PERF_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 ,
1472 p_POSITION_ID IN NUMBER ,
1473 p_PROBATION_PERIOD IN NUMBER ,
1474 p_PROBATION_UNIT IN VARCHAR2 ,
1475 p_SAL_REVIEW_PERIOD IN NUMBER ,
1476 p_SAL_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 ,
1477 p_SET_OF_BOOKS_ID IN NUMBER ,
1478 p_SOFT_CODING_KEYFLEX_ID OUT NOCOPY NUMBER ,
1479 p_SOURCE_TYPE IN VARCHAR2 ,
1480 p_SPECIAL_CEILING_STEP_ID IN OUT NOCOPY NUMBER ,
1481 p_SUPERVISOR_ID IN NUMBER ,
1482 P_SUPERVISOR_ASSIGNMENT_ID IN NUMBER ,
1483 p_TIME_NORMAL_FINISH IN VARCHAR2 ,
1484 p_TIME_NORMAL_START IN VARCHAR2 ,
1485 p_TITLE IN VARCHAR2 ,
1486 p_ENTRIES_CHANGED_WARNING OUT NOCOPY VARCHAR2,
1487 p_GROUP_NAME OUT NOCOPY VARCHAR2,
1488 p_ORG_NOW_NO_MANAGER_WARNING OUT NOCOPY BOOLEAN ,
1489 p_PEOPLE_GROUP_ID OUT NOCOPY NUMBER ,
1490 p_SPP_DELETE_WARNING OUT NOCOPY BOOLEAN ,
1491 p_TAX_DISTRICT_CHANGED_WARNING OUT NOCOPY BOOLEAN ,
1492 p_CAG_SEGMENT1 IN VARCHAR2 ,
1493 p_CAG_SEGMENT10 IN VARCHAR2 ,
1494 p_CAG_SEGMENT11 IN VARCHAR2 ,
1495 p_CAG_SEGMENT12 IN VARCHAR2 ,
1496 p_CAG_SEGMENT13 IN VARCHAR2 ,
1497 p_CAG_SEGMENT14 IN VARCHAR2 ,
1498 p_CAG_SEGMENT15 IN VARCHAR2 ,
1499 p_CAG_SEGMENT16 IN VARCHAR2 ,
1500 p_CAG_SEGMENT17 IN VARCHAR2 ,
1501 p_CAG_SEGMENT18 IN VARCHAR2 ,
1502 p_CAG_SEGMENT19 IN VARCHAR2 ,
1503 p_CAG_SEGMENT2 IN VARCHAR2 ,
1504 p_CAG_SEGMENT20 IN VARCHAR2 ,
1505 p_CAG_SEGMENT3 IN VARCHAR2 ,
1506 p_CAG_SEGMENT4 IN VARCHAR2 ,
1507 p_CAG_SEGMENT5 IN VARCHAR2 ,
1508 p_CAG_SEGMENT6 IN VARCHAR2 ,
1509 p_CAG_SEGMENT7 IN VARCHAR2 ,
1510 p_CAG_SEGMENT8 IN VARCHAR2 ,
1511 p_CAG_SEGMENT9 IN VARCHAR2 ,
1512 p_CAGR_CONCATENATED_SEGMENTS OUT NOCOPY VARCHAR2 ,
1513 p_COMMENTS IN VARCHAR2 ,
1514 p_CONCAT_SEGMENTS IN VARCHAR2 ,
1515 p_CONCATENATED_SEGMENTS IN OUT NOCOPY VARCHAR2 ,
1516 p_DATETRACK_UPDATE_MODE IN VARCHAR2 ,
1517 p_EFFECTIVE_DATE IN DATE ,
1518 p_EFFECTIVE_END_DATE OUT NOCOPY DATE ,
1519 p_EFFECTIVE_START_DATE OUT NOCOPY DATE ,
1520 p_NO_MANAGERS_WARNING OUT NOCOPY BOOLEAN ,
1521 p_OTHER_MANAGER_WARNING OUT NOCOPY BOOLEAN ,
1522 p_GSP_POST_PROCESS_WARNING OUT NOCOPY VARCHAR2 ,
1523 p_SEGMENT1 IN VARCHAR2 ,
1524 p_SEGMENT10 IN VARCHAR2 ,
1525 p_SEGMENT11 IN VARCHAR2 ,
1526 p_SEGMENT12 IN VARCHAR2 ,
1527 p_SEGMENT13 IN VARCHAR2 ,
1528 p_SEGMENT14 IN VARCHAR2 ,
1529 p_SEGMENT15 IN VARCHAR2 ,
1530 p_SEGMENT16 IN VARCHAR2 ,
1531 p_SEGMENT17 IN VARCHAR2 ,
1532 p_SEGMENT18 IN VARCHAR2 ,
1533 p_SEGMENT19 IN VARCHAR2 ,
1534 p_SEGMENT2 IN VARCHAR2 ,
1535 p_SEGMENT20 IN VARCHAR2 ,
1536 p_SEGMENT21 IN VARCHAR2 ,
1537 p_SEGMENT22 IN VARCHAR2 ,
1538 p_SEGMENT23 IN VARCHAR2 ,
1539 p_SEGMENT24 IN VARCHAR2 ,
1540 p_SEGMENT25 IN VARCHAR2 ,
1541 p_SEGMENT26 IN VARCHAR2 ,
1542 p_SEGMENT27 IN VARCHAR2 ,
1543 p_SEGMENT28 IN VARCHAR2 ,
1544 p_SEGMENT29 IN VARCHAR2 ,
1545 p_SEGMENT3 IN VARCHAR2 ,
1546 p_SEGMENT30 IN VARCHAR2 ,
1547 p_SEGMENT4 IN VARCHAR2 ,
1548 p_SEGMENT5 IN VARCHAR2 ,
1549 p_SEGMENT6 IN VARCHAR2 ,
1550 p_SEGMENT7 IN VARCHAR2 ,
1551 p_SEGMENT8 IN VARCHAR2 ,
1552 p_SEGMENT9 IN VARCHAR2 ,
1553 p_SCL_SEGMENT1 IN VARCHAR2 ,
1554 p_SCL_SEGMENT10 IN VARCHAR2 ,
1555 p_SCL_SEGMENT11 IN VARCHAR2 ,
1556 p_SCL_SEGMENT12 IN VARCHAR2 ,
1557 p_SCL_SEGMENT13 IN VARCHAR2 ,
1558 p_SCL_SEGMENT14 IN VARCHAR2 ,
1559 p_SCL_SEGMENT15 IN VARCHAR2 ,
1560 p_SCL_SEGMENT16 IN VARCHAR2 ,
1561 p_SCL_SEGMENT17 IN VARCHAR2 ,
1562 p_SCL_SEGMENT18 IN VARCHAR2 ,
1563 p_SCL_SEGMENT19 IN VARCHAR2 ,
1564 p_SCL_SEGMENT2 IN VARCHAR2 ,
1565 p_SCL_SEGMENT20 IN VARCHAR2 ,
1566 p_SCL_SEGMENT21 IN VARCHAR2 ,
1567 p_SCL_SEGMENT22 IN VARCHAR2 ,
1568 p_SCL_SEGMENT23 IN VARCHAR2 ,
1569 p_SCL_SEGMENT24 IN VARCHAR2 ,
1570 p_SCL_SEGMENT25 IN VARCHAR2 ,
1571 p_SCL_SEGMENT26 IN VARCHAR2 ,
1572 p_SCL_SEGMENT27 IN VARCHAR2 ,
1573 p_SCL_SEGMENT28 IN VARCHAR2 ,
1574 p_SCL_SEGMENT29 IN VARCHAR2 ,
1575 p_SCL_SEGMENT3 IN VARCHAR2 ,
1576 p_SCL_SEGMENT30 IN VARCHAR2 ,
1577 p_SCL_SEGMENT4 IN VARCHAR2 ,
1578 p_SCL_SEGMENT5 IN VARCHAR2 ,
1579 p_SCL_SEGMENT6 IN VARCHAR2 ,
1580 p_SCL_SEGMENT7 IN VARCHAR2 ,
1581 p_SCL_SEGMENT8 IN VARCHAR2 ,
1582 p_SCL_SEGMENT9 IN VARCHAR2 ,
1583 p_GRADE_LADDER_PGM_ID IN NUMBER ,
1584 p_VALIDATE IN BOOLEAN ) IS
1585 --
1586 cursor csr_check_estab is
1587 select count(organization_id)
1588 from hr_organization_information hoi_estab
1589 where hoi_estab.organization_id = p_organization_id
1590 and hoi_estab.org_information_context || '' = 'CLASS'
1591 and hoi_estab.org_information1 = 'FR_ETABLISSEMENT'
1592 and hoi_estab.org_information2= 'Y';
1593 ---
1594 cursor csr_get_org is
1595 select organization_id
1596 from per_all_assignments_f
1597 where assignment_id = p_assignment_id
1598 and p_effective_date between effective_start_date and effective_end_date;
1599 ---
1600 v_log_message VARCHAR2(255);
1601 v_already_errored BOOLEAN := FALSE;
1602 l_asg_future_changes_warning BOOLEAN := FALSE;
1603 l_entries_changed_warning VARCHAR2(10);
1604 l_pay_proposal_warning BOOLEAN := FALSE;
1605 l_proc VARCHAR2(72) := g_package||'upd_asg';
1606 l_effective_date DATE;
1607 l_assignment_status per_assignment_status_types.per_system_status%TYPE;
1608 --
1609 l_message_text VARCHAR2(255);
1610 --
1611 l_OBJECT_VERSION_NUMBER NUMBER := p_OBJECT_VERSION_NUMBER ;
1612 -- l_ORGANIZATION_ID NUMBER := p_ORGANIZATION_ID ;
1613 L_CAGR_GRADE_DEF_ID NUMBER := P_CAGR_GRADE_DEF_ID;
1614 l_SPECIAL_CEILING_STEP_ID NUMBER := p_SPECIAL_CEILING_STEP_ID ;
1615 l_CONCATENATED_SEGMENTS VARCHAR2(4000) := p_CONCATENATED_SEGMENTS ;
1616 l_concat_segments VARCHAR2(4000):= p_concat_segments;
1617 l_dummy_b boolean;
1618 l_dummy_n number := null;
1619 l_dummy_v varchar2(4000);
1620 -- Checking for duplicate assignments
1621 l_establishment_id number;
1622 l_organization_id number;
1623 l_duplicate_assignment number;
1624 --
1625 BEGIN
1626 --
1627 --hr_utility.trace_on(NULL,'ORACLE'); --ynegoro
1628 hr_utility.set_location('Entering : '|| l_proc, 10);
1629
1630 -- check for duplicate assignments
1631 l_duplicate_assignment := 0;
1632 if p_asg_id.first is not null then
1633 for i in p_asg_id.first..p_asg_id.last
1634 loop
1635 if p_asg_id(i) = p_assignment_id then
1636 l_duplicate_assignment := 1;
1637 hr_utility.set_location('Assignment already updated'||l_proc, 5);
1638 end if;
1639 end loop;
1640 end if;
1641 if l_duplicate_assignment = 0 then
1642 if p_asg_id.last is not null then
1643 p_asg_id(p_asg_id.last + 1) := p_assignment_id;
1644 else
1645 p_asg_id(1) := p_assignment_id;
1646 end if;
1647 hr_utility.set_location('Assignment appended to plsql table'||l_proc, 5);
1648 hr_utility.set_location('p_asg_id.last'||p_asg_id.last, 5);
1649 --
1650 -- Issue a savepoint.
1651 --
1652 SAVEPOINT upd_asg;
1653 --
1654 l_effective_date := TRUNC(p_effective_date);
1655 --
1656 -- Check the organization for establishment
1657 BEGIN
1658 OPEN csr_check_estab;
1659 fetch csr_check_estab into l_establishment_id;
1660 close csr_check_estab;
1661 if l_establishment_id > 0 then
1662 -- The organization is an establishment
1663 l_establishment_id := p_organization_id;
1664 open csr_get_org;
1665 fetch csr_get_org into l_organization_id;
1666 close csr_get_org;
1667
1668 hr_utility.set_location('it is establishment', 55);
1669 hr_utility.set_location('l_establishment_id'||l_establishment_id, 55);
1670 hr_utility.set_location('l_organization_id'||l_organization_id, 55);
1671 else
1672 l_establishment_id := p_establishment_id;
1673 l_organization_id := p_organization_id;
1674 hr_utility.set_location('it is not establishment', 55);
1675 hr_utility.set_location('l_establishment_id'||l_establishment_id, 55);
1676 hr_utility.set_location('l_organization_id'||l_organization_id, 55);
1677 end if;
1678 end;
1679 --
1680 BEGIN
1681 --
1682 hr_utility.set_location(l_proc,15);
1683 --
1684 hr_assignment_api.update_emp_asg (
1685 P_VALIDATE => P_VALIDATE ,
1686 P_EFFECTIVE_DATE => L_EFFECTIVE_DATE ,
1687 P_DATETRACK_UPDATE_MODE => P_DATETRACK_UPDATE_MODE ,
1688 P_ASSIGNMENT_ID => P_ASSIGNMENT_ID ,
1689 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
1690 P_SUPERVISOR_ID => P_SUPERVISOR_ID ,
1691 P_SUPERVISOR_ASSIGNMENT_ID => P_SUPERVISOR_ASSIGNMENT_ID ,
1692 P_ASSIGNMENT_NUMBER => P_ASSIGNMENT_NUMBER ,
1693 P_CHANGE_REASON => P_CHANGE_REASON ,
1694 P_COMMENTS => P_COMMENTS ,
1695 P_DATE_PROBATION_END => P_DATE_PROBATION_END ,
1696 P_DEFAULT_CODE_COMB_ID => P_DEFAULT_CODE_COMB_ID ,
1697 P_FREQUENCY => P_FREQUENCY ,
1698 P_INTERNAL_ADDRESS_LINE => P_INTERNAL_ADDRESS_LINE ,
1699 P_MANAGER_FLAG => P_MANAGER_FLAG ,
1700 P_NORMAL_HOURS => P_NORMAL_HOURS ,
1701 P_PERF_REVIEW_PERIOD => P_PERF_REVIEW_PERIOD ,
1702 P_PERF_REVIEW_PERIOD_FREQUENCY => P_PERF_REVIEW_PERIOD_FREQUENCY ,
1703 P_PROBATION_PERIOD => P_PROBATION_PERIOD ,
1704 P_PROBATION_UNIT => P_PROBATION_UNIT ,
1705 P_SAL_REVIEW_PERIOD => P_SAL_REVIEW_PERIOD ,
1706 P_SAL_REVIEW_PERIOD_FREQUENCY => P_SAL_REVIEW_PERIOD_FREQUENCY ,
1707 P_SET_OF_BOOKS_ID => P_SET_OF_BOOKS_ID ,
1708 P_SOURCE_TYPE => P_SOURCE_TYPE ,
1709 P_TIME_NORMAL_FINISH => P_TIME_NORMAL_FINISH ,
1710 P_TIME_NORMAL_START => P_TIME_NORMAL_START ,
1711 P_BARGAINING_UNIT_CODE => P_BARGAINING_UNIT_CODE ,
1712 P_LABOUR_UNION_MEMBER_FLAG => P_LABOUR_UNION_MEMBER_FLAG ,
1713 P_HOURLY_SALARIED_CODE => P_HOURLY_SALARIED_CODE ,
1714 P_ASS_ATTRIBUTE_CATEGORY => P_ASS_ATTRIBUTE_CATEGORY ,
1715 P_ASS_ATTRIBUTE1 => P_ASS_ATTRIBUTE1 ,
1716 P_ASS_ATTRIBUTE2 => P_ASS_ATTRIBUTE2 ,
1717 P_ASS_ATTRIBUTE3 => P_ASS_ATTRIBUTE3 ,
1718 P_ASS_ATTRIBUTE4 => P_ASS_ATTRIBUTE4 ,
1719 P_ASS_ATTRIBUTE5 => P_ASS_ATTRIBUTE5 ,
1720 P_ASS_ATTRIBUTE6 => P_ASS_ATTRIBUTE6 ,
1721 P_ASS_ATTRIBUTE7 => P_ASS_ATTRIBUTE7 ,
1722 P_ASS_ATTRIBUTE8 => P_ASS_ATTRIBUTE8 ,
1723 P_ASS_ATTRIBUTE9 => P_ASS_ATTRIBUTE9 ,
1724 P_ASS_ATTRIBUTE10 => P_ASS_ATTRIBUTE10 ,
1725 P_ASS_ATTRIBUTE11 => P_ASS_ATTRIBUTE11 ,
1726 P_ASS_ATTRIBUTE12 => P_ASS_ATTRIBUTE12 ,
1727 P_ASS_ATTRIBUTE13 => P_ASS_ATTRIBUTE13 ,
1728 P_ASS_ATTRIBUTE14 => P_ASS_ATTRIBUTE14 ,
1729 P_ASS_ATTRIBUTE15 => P_ASS_ATTRIBUTE15 ,
1730 P_ASS_ATTRIBUTE16 => P_ASS_ATTRIBUTE16 ,
1731 P_ASS_ATTRIBUTE17 => P_ASS_ATTRIBUTE17 ,
1732 P_ASS_ATTRIBUTE18 => P_ASS_ATTRIBUTE18 ,
1733 P_ASS_ATTRIBUTE19 => P_ASS_ATTRIBUTE19 ,
1734 P_ASS_ATTRIBUTE20 => P_ASS_ATTRIBUTE20 ,
1735 P_ASS_ATTRIBUTE21 => P_ASS_ATTRIBUTE21 ,
1736 P_ASS_ATTRIBUTE22 => P_ASS_ATTRIBUTE22 ,
1737 P_ASS_ATTRIBUTE23 => P_ASS_ATTRIBUTE23 ,
1738 P_ASS_ATTRIBUTE24 => P_ASS_ATTRIBUTE24 ,
1739 P_ASS_ATTRIBUTE25 => P_ASS_ATTRIBUTE25 ,
1740 P_ASS_ATTRIBUTE26 => P_ASS_ATTRIBUTE26 ,
1741 P_ASS_ATTRIBUTE27 => P_ASS_ATTRIBUTE27 ,
1742 P_ASS_ATTRIBUTE28 => P_ASS_ATTRIBUTE28 ,
1743 P_ASS_ATTRIBUTE29 => P_ASS_ATTRIBUTE29 ,
1744 P_ASS_ATTRIBUTE30 => P_ASS_ATTRIBUTE30 ,
1745 P_TITLE => P_TITLE ,
1746 P_SEGMENT1 => P_SCL_SEGMENT1 ,
1747 P_SEGMENT2 => P_SCL_SEGMENT2 ,
1748 P_SEGMENT3 => P_SCL_SEGMENT3 ,
1749 P_SEGMENT4 => P_SCL_SEGMENT4 ,
1750 P_SEGMENT5 => P_SCL_SEGMENT5 ,
1751 P_SEGMENT6 => P_SCL_SEGMENT6 ,
1752 P_SEGMENT7 => P_SCL_SEGMENT7 ,
1753 P_SEGMENT8 => P_SCL_SEGMENT8 ,
1754 P_SEGMENT9 => P_SCL_SEGMENT9 ,
1755 P_SEGMENT10 => P_SCL_SEGMENT10 ,
1756 P_SEGMENT11 => P_SCL_SEGMENT11 ,
1757 P_SEGMENT12 => P_SCL_SEGMENT12 ,
1758 P_SEGMENT13 => P_SCL_SEGMENT13 ,
1759 P_SEGMENT14 => P_SCL_SEGMENT14 ,
1760 P_SEGMENT15 => P_SCL_SEGMENT15 ,
1761 P_SEGMENT16 => P_SCL_SEGMENT16 ,
1762 P_SEGMENT17 => P_SCL_SEGMENT17 ,
1763 P_SEGMENT18 => P_SCL_SEGMENT18 ,
1764 P_SEGMENT19 => P_SCL_SEGMENT19 ,
1765 P_SEGMENT20 => P_SCL_SEGMENT20 ,
1766 P_SEGMENT21 => P_SCL_SEGMENT21 ,
1767 P_SEGMENT22 => P_SCL_SEGMENT22 ,
1768 P_SEGMENT23 => P_SCL_SEGMENT23 ,
1769 P_SEGMENT24 => P_SCL_SEGMENT24 ,
1770 P_SEGMENT25 => P_SCL_SEGMENT25 ,
1771 P_SEGMENT26 => P_SCL_SEGMENT26 ,
1772 P_SEGMENT27 => P_SCL_SEGMENT27 ,
1773 P_SEGMENT28 => P_SCL_SEGMENT28 ,
1774 P_SEGMENT29 => P_SCL_SEGMENT29 ,
1775 P_SEGMENT30 => P_SCL_SEGMENT30 ,
1776 P_CONCAT_SEGMENTS => L_CONCAT_SEGMENTS ,
1777 --P_CONTRACT_ID => P_CONTRACT_ID ,
1778 P_ESTABLISHMENT_ID => l_establishment_id ,
1779 P_COLLECTIVE_AGREEMENT_ID => P_COLLECTIVE_AGREEMENT_ID ,
1780 --P_CAGR_ID_FLEX_NUM => P_CAGR_ID_FLEX_NUM ,
1781 P_CAG_SEGMENT1 => P_CAG_SEGMENT1 ,
1782 P_CAG_SEGMENT2 => P_CAG_SEGMENT2 ,
1783 P_CAG_SEGMENT3 => P_CAG_SEGMENT3 ,
1784 P_CAG_SEGMENT4 => P_CAG_SEGMENT4 ,
1785 P_CAG_SEGMENT5 => P_CAG_SEGMENT5 ,
1786 P_CAG_SEGMENT6 => P_CAG_SEGMENT6 ,
1787 P_CAG_SEGMENT7 => P_CAG_SEGMENT7 ,
1788 P_CAG_SEGMENT8 => P_CAG_SEGMENT8 ,
1789 P_CAG_SEGMENT9 => P_CAG_SEGMENT9 ,
1790 P_CAG_SEGMENT10 => P_CAG_SEGMENT10 ,
1791 P_CAG_SEGMENT11 => P_CAG_SEGMENT11 ,
1792 P_CAG_SEGMENT12 => P_CAG_SEGMENT12 ,
1793 P_CAG_SEGMENT13 => P_CAG_SEGMENT13 ,
1794 P_CAG_SEGMENT14 => P_CAG_SEGMENT14 ,
1795 P_CAG_SEGMENT15 => P_CAG_SEGMENT15 ,
1796 P_CAG_SEGMENT16 => P_CAG_SEGMENT16 ,
1797 P_CAG_SEGMENT17 => P_CAG_SEGMENT17 ,
1798 P_CAG_SEGMENT18 => P_CAG_SEGMENT18 ,
1799 P_CAG_SEGMENT19 => P_CAG_SEGMENT19 ,
1800 P_CAG_SEGMENT20 => P_CAG_SEGMENT20 ,
1801 P_CAGR_GRADE_DEF_ID => L_CAGR_GRADE_DEF_ID ,
1802 P_CAGR_CONCATENATED_SEGMENTS => P_CAGR_CONCATENATED_SEGMENTS ,
1803 P_CONCATENATED_SEGMENTS => P_CONCATENATED_SEGMENTS ,
1804 P_SOFT_CODING_KEYFLEX_ID => P_SOFT_CODING_KEYFLEX_ID ,
1805 P_COMMENT_ID => P_COMMENT_ID ,
1806 P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE ,
1807 P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE ,
1808 P_NO_MANAGERS_WARNING => P_NO_MANAGERS_WARNING ,
1809 P_OTHER_MANAGER_WARNING => P_OTHER_MANAGER_WARNING ,
1810 P_HOURLY_SALARIED_WARNING => l_dummy_b ,
1811 P_GSP_POST_PROCESS_WARNING => P_GSP_POST_PROCESS_WARNING );
1812 --
1813 hr_utility.set_location(l_proc, 20);
1814 --
1815 If p_no_managers_warning = TRUE Then
1816 --
1817 hr_utility.set_message(800,'HR_289214_NO_MANAGERS');
1818 l_message_text := 'WARNING: '||hr_utility.get_message;
1819 --
1820 Log_Error(p_type => 'WARNING',
1821 p_assignment_number => p_assignment_number,
1822 p_warning_message => l_message_text,
1823 p_already_errored => v_already_errored);
1824 --
1825 ElsIf p_other_manager_warning = TRUE Then
1826 --
1827 hr_utility.set_message(800,'HR_289215_DUPLICATE_MANAGERS');
1828 l_message_text := 'WARNING: '||hr_utility.get_message;
1829 --
1830 Log_Error(p_type => 'WARNING',
1831 p_assignment_number => p_assignment_number,
1832 p_warning_message => l_message_text,
1833 p_already_errored => v_already_errored);
1834 --
1835 End If;
1836 --
1837 if p_gsp_post_process_warning is not null then
1838 --
1839 fnd_message.set_name('PQH',p_gsp_post_process_warning);
1840 l_message_text := 'WARNING: '||hr_utility.get_message;
1841 --
1842 Log_Error(p_type => 'WARNING',
1843 p_assignment_number => p_assignment_number,
1844 p_warning_message => l_message_text,
1845 p_already_errored => v_already_errored);
1846 --
1847 end if;
1848
1849 hr_utility.set_location(l_proc, 30);
1850 --
1851 EXCEPTION
1852 --
1853 When OTHERS Then
1854 --
1855 hr_utility.set_location(l_proc, 35);
1856 --
1857 ROLLBACK TO upd_asg;
1858 --
1859 Log_Error(p_type => 'ERROR',
1860 p_assignment_number => p_assignment_number,
1861 p_warning_message => NULL,
1862 p_already_errored => v_already_errored);
1863 RAISE;
1864 --
1865 END;
1866 --
1867 BEGIN
1868 --
1869 hr_utility.set_location(l_proc, 40);
1870 --
1871 hr_assignment_api.update_emp_asg_criteria (
1872 P_VALIDATE => P_VALIDATE ,
1873 P_EFFECTIVE_DATE => L_EFFECTIVE_DATE ,
1874 P_DATETRACK_UPDATE_MODE => P_DATETRACK_UPDATE_MODE ,
1875 P_CALLED_FROM_MASS_UPDATE => TRUE ,
1876 P_ASSIGNMENT_ID => P_ASSIGNMENT_ID ,
1877 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
1878 P_GRADE_ID => P_GRADE_ID ,
1879 P_POSITION_ID => P_POSITION_ID ,
1880 P_JOB_ID => P_JOB_ID ,
1881 P_PAYROLL_ID => P_PAYROLL_ID ,
1882 P_LOCATION_ID => P_LOCATION_ID ,
1883 P_SPECIAL_CEILING_STEP_ID => P_SPECIAL_CEILING_STEP_ID ,
1884 P_ORGANIZATION_ID => l_organization_id ,
1885 P_PAY_BASIS_ID => P_PAY_BASIS_ID ,
1886 P_SEGMENT1 => P_SEGMENT1 ,
1887 P_SEGMENT2 => P_SEGMENT2 ,
1888 P_SEGMENT3 => P_SEGMENT3 ,
1889 P_SEGMENT4 => P_SEGMENT4 ,
1890 P_SEGMENT5 => P_SEGMENT5 ,
1891 P_SEGMENT6 => P_SEGMENT6 ,
1892 P_SEGMENT7 => P_SEGMENT7 ,
1893 P_SEGMENT8 => P_SEGMENT8 ,
1894 P_SEGMENT9 => P_SEGMENT9 ,
1895 P_SEGMENT10 => P_SEGMENT10 ,
1896 P_SEGMENT11 => P_SEGMENT11 ,
1897 P_SEGMENT12 => P_SEGMENT12 ,
1898 P_SEGMENT13 => P_SEGMENT13 ,
1899 P_SEGMENT14 => P_SEGMENT14 ,
1900 P_SEGMENT15 => P_SEGMENT15 ,
1901 P_SEGMENT16 => P_SEGMENT16 ,
1902 P_SEGMENT17 => P_SEGMENT17 ,
1903 P_SEGMENT18 => P_SEGMENT18 ,
1904 P_SEGMENT19 => P_SEGMENT19 ,
1905 P_SEGMENT20 => P_SEGMENT20 ,
1906 P_SEGMENT21 => P_SEGMENT21 ,
1907 P_SEGMENT22 => P_SEGMENT22 ,
1908 P_SEGMENT23 => P_SEGMENT23 ,
1909 P_SEGMENT24 => P_SEGMENT24 ,
1910 P_SEGMENT25 => P_SEGMENT25 ,
1911 P_SEGMENT26 => P_SEGMENT26 ,
1912 P_SEGMENT27 => P_SEGMENT27 ,
1913 P_SEGMENT28 => P_SEGMENT28 ,
1914 P_SEGMENT29 => P_SEGMENT29 ,
1915 P_SEGMENT30 => P_SEGMENT30 ,
1916 P_CONCAT_SEGMENTS => P_CONCAT_SEGMENTS ,
1917 P_GROUP_NAME => P_GROUP_NAME ,
1918 P_EMPLOYMENT_CATEGORY => P_EMPLOYMENT_CATEGORY ,
1919 P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE ,
1920 P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE ,
1921 P_PEOPLE_GROUP_ID => P_PEOPLE_GROUP_ID ,
1922 P_GRADE_LADDER_PGM_ID => P_GRADE_LADDER_PGM_ID ,
1923 P_ORG_NOW_NO_MANAGER_WARNING => P_ORG_NOW_NO_MANAGER_WARNING ,
1924 P_OTHER_MANAGER_WARNING => P_OTHER_MANAGER_WARNING ,
1925 P_SPP_DELETE_WARNING => P_SPP_DELETE_WARNING ,
1926 P_ENTRIES_CHANGED_WARNING => P_ENTRIES_CHANGED_WARNING ,
1927 P_TAX_DISTRICT_CHANGED_WARNING => P_TAX_DISTRICT_CHANGED_WARNING ,
1928 P_SOFT_CODING_KEYFLEX_ID => l_dummy_n ,
1929 P_CONCATENATED_SEGMENTS => l_dummy_v ,
1930 P_GSP_POST_PROCESS_WARNING => P_GSP_POST_PROCESS_WARNING );
1931 --
1932 hr_utility.set_location(l_proc, 50);
1933 --
1934 If p_spp_delete_warning = TRUE Then
1935 --
1936 hr_utility.set_message(800,'HR_289826_SPP_DELETE_WARN_API');
1937 l_message_text := 'WARNING: '||hr_utility.get_message;
1938 --
1939 Log_Error(p_type => 'WARNING',
1940 p_assignment_number => p_assignment_number,
1941 p_warning_message => l_message_text,
1942 p_already_errored => v_already_errored);
1943 --
1944 end if;
1945 --
1946 If p_org_now_no_manager_warning = TRUE Then
1947 --
1948 hr_utility.set_message(800,'HR_289214_NO_MANAGERS');
1949 l_message_text := 'WARNING: '||hr_utility.get_message;
1950 --
1951 Log_Error(p_type => 'WARNING',
1952 p_assignment_number => p_assignment_number,
1953 p_warning_message => l_message_text,
1954 p_already_errored => v_already_errored);
1955 --
1956 ElsIf p_other_manager_warning = TRUE Then
1957 --
1958 hr_utility.set_message(800,'HR_289215_DUPLICATE_MANAGERS');
1959 l_message_text := 'WARNING: '||hr_utility.get_message;
1960 --
1961 Log_Error(p_type => 'WARNING',
1962 p_assignment_number => p_assignment_number,
1963 p_warning_message => l_message_text,
1964 p_already_errored => v_already_errored);
1965 --
1966 End If;
1967 --
1968 If p_gsp_post_process_warning is not null Then
1969 --
1970 fnd_message.set_name('PQH',p_gsp_post_process_warning);
1971 l_message_text := 'WARNING: '||hr_utility.get_message;
1972 --
1973 Log_Error(p_type => 'WARNING',
1974 p_assignment_number => p_assignment_number,
1975 p_warning_message => l_message_text,
1976 p_already_errored => v_already_errored);
1977 --
1978 End If;
1979 --
1980 hr_utility.set_location(l_proc, 60);
1981 --
1982 EXCEPTION
1983 --
1984 When OTHERS Then
1985 --
1986 hr_utility.set_location(l_proc, 65);
1987 --
1988 ROLLBACK TO upd_asg;
1989 --
1990 Log_Error(p_type => 'ERROR',
1991 p_assignment_number => p_assignment_number,
1992 p_warning_message => NULL,
1993 p_already_errored => v_already_errored);
1994 RAISE;
1995 --
1996 END;
1997 --
1998 hr_utility.set_location(l_proc, 70);
1999 --
2000 BEGIN
2001 --
2002 -- Retrieve the Assignment Status from the id past in.
2003 --
2004 l_assignment_status :=
2005 get_assignment_status_type
2006 (p_assignment_status_type_id => p_assignment_status_type_id);
2007 --
2008 hr_utility.set_location(l_proc, 80);
2009 --
2010 -- Call the correct assignment status API
2011 -- depending on what the assignment status is being
2012 -- changed to.
2013 --
2014 IF l_assignment_status = 'SUSP_ASSIGN' THEN
2015 --
2016 hr_utility.set_location(l_proc, 90);
2017 --
2018 hr_assignment_api.suspend_emp_asg
2019 (p_validate => p_validate
2020 ,p_effective_date => l_effective_date
2021 ,p_datetrack_update_mode => 'CORRECTION' --p_datetrack_update_mode
2022 ,p_assignment_id => p_assignment_id
2023 ,p_change_reason => p_change_reason
2024 ,p_object_version_number => p_object_version_number
2025 ,p_assignment_status_type_id => p_assignment_status_type_id
2026 ,p_effective_start_date => p_effective_start_date
2027 ,p_effective_end_date => p_effective_end_date);
2028 --
2029 hr_utility.set_location(l_proc, 100);
2030 --
2031 ELSIF l_assignment_status = 'TERM_ASSIGN' THEN
2032 --
2033 hr_utility.set_location(l_proc, 110);
2034 --
2035 hr_assignment_api.actual_termination_emp_asg
2036 (p_validate => p_validate
2037 ,p_assignment_id => p_assignment_id
2038 ,p_object_version_number => p_object_version_number
2039 ,p_actual_termination_date => l_effective_date
2040 ,p_assignment_status_type_id => p_assignment_status_type_id
2041 ,p_effective_start_date => p_effective_start_date
2042 ,p_effective_end_date => p_effective_end_date
2043 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2044 ,p_entries_changed_warning => l_entries_changed_warning
2045 ,p_pay_proposal_warning => l_pay_proposal_warning);
2046 --
2047 hr_utility.set_location(l_proc, 120);
2048 --
2049 IF l_asg_future_changes_warning = TRUE THEN
2050 --
2051 hr_utility.set_message(800,'HR_289216_FUTURE_CHANGES_REMOV');
2052 l_message_text := 'WARNING: '||hr_utility.get_message;
2053 --
2054 Log_Error
2055 (p_type => 'WARNING'
2056 ,p_assignment_number => p_assignment_number
2057 ,p_warning_message => l_message_text
2058 ,p_already_errored => v_already_errored);
2059 --
2060 ELSIF l_entries_changed_warning <> 'N' THEN
2061 --
2062 hr_utility.set_message(800,'HR_289218_ENTRIES_CHANGED');
2063 l_message_text := 'WARNING: '||hr_utility.get_message;
2064 --
2065 Log_Error
2066 (p_type => 'WARNING'
2067 ,p_assignment_number => p_assignment_number
2068 ,p_warning_message => l_message_text
2069 ,p_already_errored => v_already_errored);
2070 --
2071 ELSIF l_pay_proposal_warning THEN
2072 --
2073 hr_utility.set_message(800,'HR_289217_PAY_PROPOSAL_REMOVED');
2074 l_message_text := 'WARNING: '||hr_utility.get_message;
2075 --
2076 Log_Error
2077 (p_type => 'WARNING'
2078 ,p_assignment_number => p_assignment_number
2079 ,p_warning_message => l_message_text
2080 ,p_already_errored => v_already_errored);
2081 --
2082 END IF;
2083 --
2084 ELSE
2085 --
2086 hr_utility.set_location(l_proc, 130);
2087 --
2088 hr_assignment_api.activate_emp_asg
2089 (p_validate => p_validate
2090 ,p_effective_date => l_effective_date
2091 ,p_datetrack_update_mode => 'CORRECTION' --p_datetrack_update_mode
2092 ,p_assignment_id => p_assignment_id
2093 ,p_change_reason => p_change_reason
2094 ,p_object_version_number => p_object_version_number
2095 ,p_assignment_status_type_id => p_assignment_status_type_id
2096 ,p_effective_start_date => p_effective_start_date
2097 ,p_effective_end_date => p_effective_end_date);
2098 --
2099 hr_utility.set_location(l_proc, 140);
2100 --
2101 END IF;
2102 --
2103 EXCEPTION
2104 --
2105 WHEN OTHERS THEN
2106 --
2107 hr_utility.set_location(l_proc, 145);
2108 --
2109 p_OBJECT_VERSION_NUMBER := l_OBJECT_VERSION_NUMBER ;
2110 p_ORGANIZATION_ID := l_ORGANIZATION_ID ;
2111 p_SPECIAL_CEILING_STEP_ID := l_SPECIAL_CEILING_STEP_ID ;
2112 p_CONCATENATED_SEGMENTS := l_CONCATENATED_SEGMENTS ;
2113 p_CAGR_GRADE_DEF_ID := l_CAGR_GRADE_DEF_ID;
2114
2115 p_COMMENT_ID := null;
2116 p_SOFT_CODING_KEYFLEX_ID := null;
2117 p_ENTRIES_CHANGED_WARNING := null;
2118 p_GROUP_NAME := null;
2119 p_ORG_NOW_NO_MANAGER_WARNING := null;
2120 p_PEOPLE_GROUP_ID := null;
2121 p_SPP_DELETE_WARNING := null;
2122 p_TAX_DISTRICT_CHANGED_WARNING := null;
2123 p_CAGR_CONCATENATED_SEGMENTS := null;
2124 p_EFFECTIVE_END_DATE := null;
2125 p_EFFECTIVE_START_DATE := null;
2126 p_NO_MANAGERS_WARNING := null;
2127 p_OTHER_MANAGER_WARNING := null;
2128
2129 ROLLBACK TO upd_asg;
2130 --
2131 Log_Error(p_type => 'ERROR',
2132 p_assignment_number => p_assignment_number,
2133 p_warning_message => NULL,
2134 p_already_errored => v_already_errored);
2135 --
2136 RAISE;
2137 --
2138 END;
2139 else
2140 hr_utility.set_location('duplicate assignment'||l_proc, 995);
2141 end if; --ends duplicate assignment checking
2142 --
2143 hr_utility.set_location('Leaving : '||l_proc, 999);
2144 --
2145 END upd_asg;
2146 --
2147 -- ---------------------------------------------------------------------------+
2148 -- |-------------------------< UPDATE_APPLICANT_ASG >-------------------------+
2149 -- ---------------------------------------------------------------------------+
2150 --
2151 PROCEDURE update_applicant_asg
2152 (p_validate in boolean default false
2153 ,p_effective_date in date
2154 ,p_datetrack_update_mode in varchar2
2155 ,p_assignment_id in number
2156 ,p_object_version_number in out nocopy number
2157 ,p_recruiter_id in number default hr_api.g_number
2158 ,p_grade_id in number default hr_api.g_number
2159 ,p_position_id in number default hr_api.g_number
2160 ,p_job_id in number default hr_api.g_number
2161 ,p_payroll_id in number default hr_api.g_number
2162 ,p_location_id in number default hr_api.g_number
2163 ,p_person_referred_by_id in number default hr_api.g_number
2164 ,p_assignment_status_type_id in number default hr_api.g_number
2165 ,p_supervisor_id in number default hr_api.g_number
2166 ,p_supervisor_assignment_id IN NUMBER DEFAULT hr_api.g_number
2167 ,p_special_ceiling_step_id in number default hr_api.g_number
2168 ,p_recruitment_activity_id in number default hr_api.g_number
2169 ,p_source_organization_id in number default hr_api.g_number
2170 ,p_organization_id in number default hr_api.g_number
2171 ,p_vacancy_id in number default hr_api.g_number
2172 ,p_pay_basis_id in number default hr_api.g_number
2173 ,p_application_id in number default hr_api.g_number
2174 ,p_change_reason in varchar2 default hr_api.g_varchar2
2175 ,p_comments in varchar2 default hr_api.g_varchar2
2176 ,p_date_probation_end in date default hr_api.g_date
2177 ,p_default_code_comb_id in number default hr_api.g_number
2178 ,p_employment_category in varchar2 default hr_api.g_varchar2
2179 ,p_frequency in varchar2 default hr_api.g_varchar2
2180 ,p_internal_address_line in varchar2 default hr_api.g_varchar2
2181 ,p_manager_flag in varchar2 default hr_api.g_varchar2
2182 ,p_normal_hours in number default hr_api.g_number
2183 ,p_perf_review_period in number default hr_api.g_number
2184 ,p_perf_review_period_frequency in varchar2 default hr_api.g_varchar2
2185 ,p_probation_period in number default hr_api.g_number
2186 ,p_probation_unit in varchar2 default hr_api.g_varchar2
2187 ,p_sal_review_period in number default hr_api.g_number
2188 ,p_sal_review_period_frequency in varchar2 default hr_api.g_varchar2
2189 ,p_set_of_books_id in number default hr_api.g_number
2190 ,p_source_type in varchar2 default hr_api.g_varchar2
2191 ,p_time_normal_finish in varchar2 default hr_api.g_varchar2
2192 ,p_time_normal_start in varchar2 default hr_api.g_varchar2
2193 ,p_bargaining_unit_code in varchar2 default hr_api.g_varchar2
2194 ,p_ass_attribute_category in varchar2 default hr_api.g_varchar2
2195 ,p_ass_attribute1 in varchar2 default hr_api.g_varchar2
2196 ,p_ass_attribute2 in varchar2 default hr_api.g_varchar2
2197 ,p_ass_attribute3 in varchar2 default hr_api.g_varchar2
2198 ,p_ass_attribute4 in varchar2 default hr_api.g_varchar2
2199 ,p_ass_attribute5 in varchar2 default hr_api.g_varchar2
2200 ,p_ass_attribute6 in varchar2 default hr_api.g_varchar2
2201 ,p_ass_attribute7 in varchar2 default hr_api.g_varchar2
2202 ,p_ass_attribute8 in varchar2 default hr_api.g_varchar2
2203 ,p_ass_attribute9 in varchar2 default hr_api.g_varchar2
2204 ,p_ass_attribute10 in varchar2 default hr_api.g_varchar2
2205 ,p_ass_attribute11 in varchar2 default hr_api.g_varchar2
2206 ,p_ass_attribute12 in varchar2 default hr_api.g_varchar2
2207 ,p_ass_attribute13 in varchar2 default hr_api.g_varchar2
2208 ,p_ass_attribute14 in varchar2 default hr_api.g_varchar2
2209 ,p_ass_attribute15 in varchar2 default hr_api.g_varchar2
2210 ,p_ass_attribute16 in varchar2 default hr_api.g_varchar2
2211 ,p_ass_attribute17 in varchar2 default hr_api.g_varchar2
2212 ,p_ass_attribute18 in varchar2 default hr_api.g_varchar2
2213 ,p_ass_attribute19 in varchar2 default hr_api.g_varchar2
2214 ,p_ass_attribute20 in varchar2 default hr_api.g_varchar2
2215 ,p_ass_attribute21 in varchar2 default hr_api.g_varchar2
2216 ,p_ass_attribute22 in varchar2 default hr_api.g_varchar2
2217 ,p_ass_attribute23 in varchar2 default hr_api.g_varchar2
2218 ,p_ass_attribute24 in varchar2 default hr_api.g_varchar2
2219 ,p_ass_attribute25 in varchar2 default hr_api.g_varchar2
2220 ,p_ass_attribute26 in varchar2 default hr_api.g_varchar2
2221 ,p_ass_attribute27 in varchar2 default hr_api.g_varchar2
2222 ,p_ass_attribute28 in varchar2 default hr_api.g_varchar2
2223 ,p_ass_attribute29 in varchar2 default hr_api.g_varchar2
2224 ,p_ass_attribute30 in varchar2 default hr_api.g_varchar2
2225 ,p_title in varchar2 default hr_api.g_varchar2
2226 ,p_scl_segment1 in varchar2 default hr_api.g_varchar2
2227 ,p_scl_segment2 in varchar2 default hr_api.g_varchar2
2228 ,p_scl_segment3 in varchar2 default hr_api.g_varchar2
2229 ,p_scl_segment4 in varchar2 default hr_api.g_varchar2
2230 ,p_scl_segment5 in varchar2 default hr_api.g_varchar2
2231 ,p_scl_segment6 in varchar2 default hr_api.g_varchar2
2232 ,p_scl_segment7 in varchar2 default hr_api.g_varchar2
2233 ,p_scl_segment8 in varchar2 default hr_api.g_varchar2
2234 ,p_scl_segment9 in varchar2 default hr_api.g_varchar2
2235 ,p_scl_segment10 in varchar2 default hr_api.g_varchar2
2236 ,p_scl_segment11 in varchar2 default hr_api.g_varchar2
2237 ,p_scl_segment12 in varchar2 default hr_api.g_varchar2
2238 ,p_scl_segment13 in varchar2 default hr_api.g_varchar2
2239 ,p_scl_segment14 in varchar2 default hr_api.g_varchar2
2240 ,p_scl_segment15 in varchar2 default hr_api.g_varchar2
2241 ,p_scl_segment16 in varchar2 default hr_api.g_varchar2
2242 ,p_scl_segment17 in varchar2 default hr_api.g_varchar2
2243 ,p_scl_segment18 in varchar2 default hr_api.g_varchar2
2244 ,p_scl_segment19 in varchar2 default hr_api.g_varchar2
2245 ,p_scl_segment20 in varchar2 default hr_api.g_varchar2
2246 ,p_scl_segment21 in varchar2 default hr_api.g_varchar2
2247 ,p_scl_segment22 in varchar2 default hr_api.g_varchar2
2248 ,p_scl_segment23 in varchar2 default hr_api.g_varchar2
2249 ,p_scl_segment24 in varchar2 default hr_api.g_varchar2
2250 ,p_scl_segment25 in varchar2 default hr_api.g_varchar2
2251 ,p_scl_segment26 in varchar2 default hr_api.g_varchar2
2252 ,p_scl_segment27 in varchar2 default hr_api.g_varchar2
2253 ,p_scl_segment28 in varchar2 default hr_api.g_varchar2
2254 ,p_scl_segment29 in varchar2 default hr_api.g_varchar2
2255 ,p_scl_segment30 in varchar2 default hr_api.g_varchar2
2256 ,p_concatenated_segments in out nocopy varchar2
2257 ,p_pgp_segment1 in varchar2 default hr_api.g_varchar2
2258 ,p_pgp_segment2 in varchar2 default hr_api.g_varchar2
2259 ,p_pgp_segment3 in varchar2 default hr_api.g_varchar2
2260 ,p_pgp_segment4 in varchar2 default hr_api.g_varchar2
2261 ,p_pgp_segment5 in varchar2 default hr_api.g_varchar2
2262 ,p_pgp_segment6 in varchar2 default hr_api.g_varchar2
2263 ,p_pgp_segment7 in varchar2 default hr_api.g_varchar2
2264 ,p_pgp_segment8 in varchar2 default hr_api.g_varchar2
2265 ,p_pgp_segment9 in varchar2 default hr_api.g_varchar2
2266 ,p_pgp_segment10 in varchar2 default hr_api.g_varchar2
2267 ,p_pgp_segment11 in varchar2 default hr_api.g_varchar2
2268 ,p_pgp_segment12 in varchar2 default hr_api.g_varchar2
2269 ,p_pgp_segment13 in varchar2 default hr_api.g_varchar2
2270 ,p_pgp_segment14 in varchar2 default hr_api.g_varchar2
2271 ,p_pgp_segment15 in varchar2 default hr_api.g_varchar2
2272 ,p_pgp_segment16 in varchar2 default hr_api.g_varchar2
2273 ,p_pgp_segment17 in varchar2 default hr_api.g_varchar2
2274 ,p_pgp_segment18 in varchar2 default hr_api.g_varchar2
2275 ,p_pgp_segment19 in varchar2 default hr_api.g_varchar2
2276 ,p_pgp_segment20 in varchar2 default hr_api.g_varchar2
2277 ,p_pgp_segment21 in varchar2 default hr_api.g_varchar2
2278 ,p_pgp_segment22 in varchar2 default hr_api.g_varchar2
2279 ,p_pgp_segment23 in varchar2 default hr_api.g_varchar2
2280 ,p_pgp_segment24 in varchar2 default hr_api.g_varchar2
2281 ,p_pgp_segment25 in varchar2 default hr_api.g_varchar2
2282 ,p_pgp_segment26 in varchar2 default hr_api.g_varchar2
2283 ,p_pgp_segment27 in varchar2 default hr_api.g_varchar2
2284 ,p_pgp_segment28 in varchar2 default hr_api.g_varchar2
2285 ,p_pgp_segment29 in varchar2 default hr_api.g_varchar2
2286 ,p_pgp_segment30 in varchar2 default hr_api.g_varchar2
2287 ,p_concat_segments in varchar2 default hr_api.g_varchar2
2288 ,p_contract_id in number default hr_api.g_number
2289 ,p_establishment_id in number default hr_api.g_number
2290 ,p_collective_agreement_id in number default hr_api.g_number
2291 ,p_cagr_id_flex_num in number default hr_api.g_number
2292 ,p_cag_segment1 in varchar2 default hr_api.g_varchar2
2293 ,p_cag_segment2 in varchar2 default hr_api.g_varchar2
2294 ,p_cag_segment3 in varchar2 default hr_api.g_varchar2
2295 ,p_cag_segment4 in varchar2 default hr_api.g_varchar2
2296 ,p_cag_segment5 in varchar2 default hr_api.g_varchar2
2297 ,p_cag_segment6 in varchar2 default hr_api.g_varchar2
2298 ,p_cag_segment7 in varchar2 default hr_api.g_varchar2
2299 ,p_cag_segment8 in varchar2 default hr_api.g_varchar2
2300 ,p_cag_segment9 in varchar2 default hr_api.g_varchar2
2301 ,p_cag_segment10 in varchar2 default hr_api.g_varchar2
2302 ,p_cag_segment11 in varchar2 default hr_api.g_varchar2
2303 ,p_cag_segment12 in varchar2 default hr_api.g_varchar2
2304 ,p_cag_segment13 in varchar2 default hr_api.g_varchar2
2305 ,p_cag_segment14 in varchar2 default hr_api.g_varchar2
2306 ,p_cag_segment15 in varchar2 default hr_api.g_varchar2
2307 ,p_cag_segment16 in varchar2 default hr_api.g_varchar2
2308 ,p_cag_segment17 in varchar2 default hr_api.g_varchar2
2309 ,p_cag_segment18 in varchar2 default hr_api.g_varchar2
2310 ,p_cag_segment19 in varchar2 default hr_api.g_varchar2
2311 ,p_cag_segment20 in varchar2 default hr_api.g_varchar2
2312 ,p_grade_ladder_pgm_id in number default hr_api.g_number
2313 ,p_cagr_grade_def_id in out nocopy number
2314 ,p_cagr_concatenated_segments out nocopy varchar2
2315 ,p_group_name out nocopy varchar2
2316 ,p_comment_id out nocopy number
2317 ,p_people_group_id out nocopy number
2318 ,p_soft_coding_keyflex_id out nocopy number
2319 ,p_effective_start_date out nocopy date
2320 ,p_effective_end_date out nocopy date ) is
2321 --
2322 -- Procedure Return Variables
2323 --
2324 l_group_name varchar2(240) := NULL;
2325 l_comment_id number := NULL;
2326 l_people_group_id number := NULL;
2327 l_soft_coding_keyflex_id number := NULL;
2328 l_cagr_grade_def_id number := p_cagr_grade_def_id;
2329 l_cagr_concatenated_segments varchar2(240);
2330 l_scl_concat_segments hr_soft_Coding_keyflex.concatenated_segments%TYPE;
2331 --
2332 -- General Variables
2333
2334 l_object_version_number number := p_object_version_number ;
2335 l_concatenated_segments varchar2(4000) := p_concatenated_segments ;
2336 --
2337 --
2338 l_proc varchar2(72) := g_package||'Update_Applicant_Asg';
2339 v_log_message varchar2(255);
2340 v_already_errored boolean := FALSE;
2341 --
2342 l_effective_date DATE;
2343 l_assignment_status per_assignment_status_types.per_system_status%TYPE;
2344 --
2345 l_message_text VARCHAR2(255);
2346 --
2347 BEGIN
2348 --
2349 hr_utility.set_location('Entering '||l_proc,10);
2350 --
2351 l_effective_date := TRUNC(p_effective_date);
2352 --
2353 l_scl_concat_segments := p_concatenated_segments;
2354 --
2355 BEGIN
2356 --
2357 hr_utility.set_location(l_proc,20);
2358 --
2359 hr_assignment_api.update_apl_asg
2360 (p_validate => p_validate
2361 ,p_effective_date => l_effective_date
2362 ,p_datetrack_update_mode => p_datetrack_update_mode
2363 ,p_assignment_id => p_assignment_id
2364 ,p_object_version_number => p_object_version_number
2365 ,p_recruiter_id => p_recruiter_id
2366 ,p_grade_id => p_grade_id
2367 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
2368 ,p_position_id => p_position_id
2369 ,p_job_id => p_job_id
2370 ,p_payroll_id => p_payroll_id
2371 ,p_location_id => p_location_id
2372 ,p_person_referred_by_id => p_person_referred_by_id
2373 ,p_supervisor_id => p_supervisor_id
2374 ,p_supervisor_assignment_id => p_supervisor_assignment_id
2375 ,p_special_ceiling_step_id => p_special_ceiling_step_id
2376 ,p_recruitment_activity_id => p_recruitment_activity_id
2377 ,p_source_organization_id => p_source_organization_id
2378 ,p_organization_id => p_organization_id
2379 ,p_vacancy_id => p_vacancy_id
2380 ,p_pay_basis_id => p_pay_basis_id
2381 ,p_application_id => p_application_id
2382 ,p_change_reason => p_change_reason
2383 ,p_comments => p_comments
2384 ,p_date_probation_end => p_date_probation_end
2385 ,p_default_code_comb_id => p_default_code_comb_id
2386 ,p_employment_category => p_employment_category
2387 ,p_frequency => p_frequency
2388 ,p_internal_address_line => p_internal_address_line
2389 ,p_manager_flag => p_manager_flag
2390 ,p_normal_hours => p_normal_hours
2391 ,p_perf_review_period => p_perf_review_period
2392 ,p_perf_review_period_frequency => p_perf_review_period_frequency
2393 ,p_probation_period => p_probation_period
2394 ,p_probation_unit => p_probation_unit
2395 ,p_sal_review_period => p_sal_review_period
2396 ,p_sal_review_period_frequency => p_sal_review_period_frequency
2397 ,p_set_of_books_id => p_set_of_books_id
2398 ,p_source_type => p_source_type
2399 ,p_time_normal_finish => p_time_normal_finish
2400 ,p_time_normal_start => p_time_normal_start
2401 ,p_bargaining_unit_code => p_bargaining_unit_code
2402 ,p_ass_attribute_category => p_ass_attribute_category
2403 ,p_ass_attribute1 => p_ass_attribute1
2404 ,p_ass_attribute2 => p_ass_attribute2
2405 ,p_ass_attribute3 => p_ass_attribute3
2406 ,p_ass_attribute4 => p_ass_attribute4
2407 ,p_ass_attribute5 => p_ass_attribute5
2408 ,p_ass_attribute6 => p_ass_attribute6
2409 ,p_ass_attribute7 => p_ass_attribute7
2410 ,p_ass_attribute8 => p_ass_attribute8
2411 ,p_ass_attribute9 => p_ass_attribute9
2412 ,p_ass_attribute10 => p_ass_attribute10
2413 ,p_ass_attribute11 => p_ass_attribute11
2414 ,p_ass_attribute12 => p_ass_attribute12
2415 ,p_ass_attribute13 => p_ass_attribute13
2416 ,p_ass_attribute14 => p_ass_attribute14
2417 ,p_ass_attribute15 => p_ass_attribute15
2418 ,p_ass_attribute16 => p_ass_attribute16
2419 ,p_ass_attribute17 => p_ass_attribute17
2420 ,p_ass_attribute18 => p_ass_attribute18
2421 ,p_ass_attribute19 => p_ass_attribute19
2422 ,p_ass_attribute20 => p_ass_attribute20
2423 ,p_ass_attribute21 => p_ass_attribute21
2424 ,p_ass_attribute22 => p_ass_attribute22
2425 ,p_ass_attribute23 => p_ass_attribute23
2426 ,p_ass_attribute24 => p_ass_attribute24
2427 ,p_ass_attribute25 => p_ass_attribute25
2428 ,p_ass_attribute26 => p_ass_attribute26
2429 ,p_ass_attribute27 => p_ass_attribute27
2430 ,p_ass_attribute28 => p_ass_attribute28
2431 ,p_ass_attribute29 => p_ass_attribute29
2432 ,p_ass_attribute30 => p_ass_attribute30
2433 ,p_title => p_title
2434 ,p_scl_segment1 => p_scl_segment1
2435 ,p_scl_segment2 => p_scl_segment2
2436 ,p_scl_segment3 => p_scl_segment3
2437 ,p_scl_segment4 => p_scl_segment4
2438 ,p_scl_segment5 => p_scl_segment5
2439 ,p_scl_segment6 => p_scl_segment6
2440 ,p_scl_segment7 => p_scl_segment7
2441 ,p_scl_segment8 => p_scl_segment8
2442 ,p_scl_segment9 => p_scl_segment9
2443 ,p_scl_segment10 => p_scl_segment10
2444 ,p_scl_segment11 => p_scl_segment11
2445 ,p_scl_segment12 => p_scl_segment12
2446 ,p_scl_segment13 => p_scl_segment13
2447 ,p_scl_segment14 => p_scl_segment14
2448 ,p_scl_segment15 => p_scl_segment15
2449 ,p_scl_segment16 => p_scl_segment16
2450 ,p_scl_segment17 => p_scl_segment17
2451 ,p_scl_segment18 => p_scl_segment18
2452 ,p_scl_segment19 => p_scl_segment19
2453 ,p_scl_segment20 => p_scl_segment20
2454 ,p_scl_segment21 => p_scl_segment21
2455 ,p_scl_segment22 => p_scl_segment22
2456 ,p_scl_segment23 => p_scl_segment23
2457 ,p_scl_segment24 => p_scl_segment24
2458 ,p_scl_segment25 => p_scl_segment25
2459 ,p_scl_segment26 => p_scl_segment26
2460 ,p_scl_segment27 => p_scl_segment27
2461 ,p_scl_segment28 => p_scl_segment28
2462 ,p_scl_segment29 => p_scl_segment29
2463 ,p_scl_segment30 => p_scl_segment30
2464 ,p_scl_concat_segments => l_scl_concat_segments
2465 ,p_concatenated_segments => p_concatenated_segments
2466 ,p_pgp_segment1 => p_pgp_segment1
2467 ,p_pgp_segment2 => p_pgp_segment2
2468 ,p_pgp_segment3 => p_pgp_segment3
2469 ,p_pgp_segment4 => p_pgp_segment4
2470 ,p_pgp_segment5 => p_pgp_segment5
2471 ,p_pgp_segment6 => p_pgp_segment6
2472 ,p_pgp_segment7 => p_pgp_segment7
2473 ,p_pgp_segment8 => p_pgp_segment8
2474 ,p_pgp_segment9 => p_pgp_segment9
2475 ,p_pgp_segment10 => p_pgp_segment10
2476 ,p_pgp_segment11 => p_pgp_segment11
2477 ,p_pgp_segment12 => p_pgp_segment12
2478 ,p_pgp_segment13 => p_pgp_segment13
2479 ,p_pgp_segment14 => p_pgp_segment14
2480 ,p_pgp_segment15 => p_pgp_segment15
2481 ,p_pgp_segment16 => p_pgp_segment16
2482 ,p_pgp_segment17 => p_pgp_segment17
2483 ,p_pgp_segment18 => p_pgp_segment18
2484 ,p_pgp_segment19 => p_pgp_segment19
2485 ,p_pgp_segment20 => p_pgp_segment20
2486 ,p_pgp_segment21 => p_pgp_segment21
2487 ,p_pgp_segment22 => p_pgp_segment22
2488 ,p_pgp_segment23 => p_pgp_segment23
2489 ,p_pgp_segment24 => p_pgp_segment24
2490 ,p_pgp_segment25 => p_pgp_segment25
2491 ,p_pgp_segment26 => p_pgp_segment26
2492 ,p_pgp_segment27 => p_pgp_segment27
2493 ,p_pgp_segment28 => p_pgp_segment28
2494 ,p_pgp_segment29 => p_pgp_segment29
2495 ,p_pgp_segment30 => p_pgp_segment30
2496 ,p_concat_segments => p_concat_segments
2497 --,p_contract_id => p_contract_id
2498 ,p_establishment_id => p_establishment_id
2499 --,p_collective_agreement_id => p_collective_agreement_id
2500 --,p_cagr_id_flex_num => p_cagr_id_flex_num
2501 ,p_cag_segment1 => p_cag_segment1
2502 ,p_cag_segment2 => p_cag_segment2
2503 ,p_cag_segment3 => p_cag_segment3
2504 ,p_cag_segment4 => p_cag_segment4
2505 ,p_cag_segment5 => p_cag_segment5
2506 ,p_cag_segment6 => p_cag_segment6
2507 ,p_cag_segment7 => p_cag_segment7
2508 ,p_cag_segment8 => p_cag_segment8
2509 ,p_cag_segment9 => p_cag_segment9
2510 ,p_cag_segment10 => p_cag_segment10
2511 ,p_cag_segment11 => p_cag_segment11
2512 ,p_cag_segment12 => p_cag_segment12
2513 ,p_cag_segment13 => p_cag_segment13
2514 ,p_cag_segment14 => p_cag_segment14
2515 ,p_cag_segment15 => p_cag_segment15
2516 ,p_cag_segment16 => p_cag_segment16
2517 ,p_cag_segment17 => p_cag_segment17
2518 ,p_cag_segment18 => p_cag_segment18
2519 ,p_cag_segment19 => p_cag_segment19
2520 ,p_cag_segment20 => p_cag_segment20
2521 ,p_cagr_grade_def_id => l_cagr_grade_def_id
2522 ,p_cagr_concatenated_segments => p_cagr_concatenated_segments
2523 ,p_group_name => p_group_name
2524 ,p_comment_id => p_comment_id
2525 ,p_people_group_id => p_people_group_id
2526 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
2527 ,p_effective_start_date => p_effective_start_date
2528 ,p_effective_end_date => p_effective_end_date);
2529 --
2530 hr_utility.set_location(l_proc,30);
2531 --
2532 EXCEPTION
2533 --
2534 WHEN OTHERS THEN
2535 --
2536 log_error
2537 (p_type => 'ERROR'
2538 ,p_assignment_number => p_assignment_id
2539 ,p_warning_message => NULL
2540 ,p_already_errored => v_already_errored);
2541 --
2542 RAISE;
2543 --
2544 END;
2545 --
2546 BEGIN
2547 --
2548 hr_utility.set_location(l_proc,40);
2549 --
2550 -- Retrieve the Assignment Status from the id past in.
2551 --
2552 l_assignment_status :=
2553 get_assignment_status_type
2554 (p_assignment_status_type_id => p_assignment_status_type_id);
2555 --
2556 hr_utility.set_location(l_proc, 50);
2557 --
2558 -- Call the correct assignment status API
2559 -- depending on what the assignment status is being
2560 -- changed to.
2561 --
2562 IF l_assignment_status = 'ACCEPTED' THEN
2563 --
2564 hr_utility.set_location(l_proc, 60);
2565 --
2566 hr_assignment_api.accept_apl_asg
2567 (p_validate => p_validate
2568 ,p_effective_date => l_effective_date
2569 ,p_datetrack_update_mode => 'CORRECTION'
2570 ,p_assignment_id => p_assignment_id
2571 ,p_object_version_number => p_object_version_number
2572 ,p_assignment_status_type_id => p_assignment_status_type_id
2573 ,p_change_reason => p_change_reason
2574 ,p_effective_start_date => p_effective_start_date
2575 ,p_effective_end_date => p_effective_end_date
2576 );
2577 --
2578 hr_utility.set_location(l_proc, 100);
2579 --
2580 ELSIF l_assignment_status = 'ACTIVE_APL' THEN
2581 --
2582 hr_utility.set_location(l_proc, 110);
2583 --
2584 hr_assignment_api.activate_apl_asg
2585 (p_validate => p_validate
2586 ,p_effective_date => l_effective_date
2587 ,p_datetrack_update_mode => 'CORRECTION'
2588 ,p_assignment_id => p_assignment_id
2589 ,p_object_version_number => p_object_version_number
2590 ,p_assignment_status_type_id => p_assignment_status_type_id
2591 ,p_change_reason => p_change_reason
2592 ,p_effective_start_date => p_effective_start_date
2593 ,p_effective_end_date => p_effective_end_date);
2594 --
2595 hr_utility.set_location(l_proc, 120);
2596 --
2597 ELSIF l_assignment_status = 'OFFER' THEN
2598 --
2599 hr_utility.set_location(l_proc,130);
2600 --
2601 hr_assignment_api.offer_apl_asg
2602 (p_validate => p_validate
2603 ,p_effective_date => l_effective_date
2604 ,p_datetrack_update_mode => 'CORRECTION'
2605 ,p_assignment_id => p_assignment_id
2606 ,p_object_version_number => p_object_version_number
2607 ,p_assignment_status_type_id => p_assignment_status_type_id
2608 ,p_change_reason => p_change_reason
2609 ,p_effective_start_date => p_effective_start_date
2610 ,p_effective_end_date => p_effective_end_date);
2611 --
2612 hr_utility.set_location(l_proc,140);
2613 --
2614 ELSE
2615 --
2616 hr_utility.set_location(l_proc, 130);
2617 --
2618 hr_assignment_internal.update_status_type_apl_asg
2619 (p_effective_date => l_effective_date
2620 ,p_datetrack_update_mode => 'CORRECTION'
2621 ,p_assignment_id => p_assignment_id
2622 ,p_object_version_number => p_object_version_number
2623 ,p_expected_system_status => l_assignment_status
2624 ,p_assignment_status_type_id => p_assignment_status_type_id
2625 ,p_change_reason => p_change_reason
2626 ,p_effective_start_date => p_effective_start_date
2627 ,p_effective_end_date => p_effective_end_date);
2628 --
2629 hr_utility.set_location(l_proc, 140);
2630 --
2631 END IF;
2632 --
2633 EXCEPTION
2634 --
2635 WHEN OTHERS THEN
2636 --
2637
2638 p_object_version_number := l_object_version_number ;
2639 p_concatenated_segments := l_concatenated_segments ;
2640 p_cagr_grade_def_id := l_CAGR_GRADE_DEF_ID ;
2641
2642 p_cagr_concatenated_segments := null ;
2643 p_group_name := null ;
2644 p_comment_id := null ;
2645 p_people_group_id := null ;
2646 p_soft_coding_keyflex_id := null ;
2647 p_effective_start_date := null ;
2648 p_effective_end_date := null ;
2649
2650 Log_Error(p_type => 'ERROR',
2651 p_assignment_number => p_assignment_id,
2652 p_warning_message => NULL,
2653 p_already_errored => v_already_errored);
2654 --
2655 RAISE;
2656 --
2657 END;
2658 --
2659 hr_utility.set_location('Leaving '||l_proc, 70);
2660 --
2661 END update_applicant_asg;
2662 --
2663 -- ---------------------------------------------------------------------------+
2664 -- |-----------------------< UPDATE_CWK_ASSIGNMENT >--------------------------+
2665 -- ---------------------------------------------------------------------------+
2666 --
2667 PROCEDURE update_cwk_assignment
2668 (p_validate IN BOOLEAN DEFAULT FALSE
2669 ,p_effective_date IN DATE DEFAULT hr_api.g_date
2670 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT hr_api.g_varchar2
2671 ,p_assignment_id IN NUMBER DEFAULT hr_api.g_number
2672 ,p_object_version_number IN OUT NOCOPY NUMBER
2673 ,p_assignment_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
2674 ,p_assignment_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
2675 ,p_change_reason IN VARCHAR2 DEFAULT hr_api.g_varchar2
2676 ,p_comments IN VARCHAR2 DEFAULT hr_api.g_varchar2
2677 ,p_default_code_comb_id IN NUMBER DEFAULT hr_api.g_number
2678 ,p_establishment_id IN NUMBER DEFAULT hr_api.g_number
2679 ,p_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
2680 ,p_internal_address_line IN VARCHAR2 DEFAULT hr_api.g_varchar2
2681 ,p_labour_union_member_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
2682 ,p_manager_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
2683 ,p_normal_hours IN NUMBER DEFAULT hr_api.g_number
2684 ,p_project_title IN VARCHAR2 DEFAULT hr_api.g_varchar2
2685 ,p_set_of_books_id IN NUMBER DEFAULT hr_api.g_number
2686 ,p_source_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
2687 ,p_supervisor_id IN NUMBER DEFAULT hr_api.g_number
2688 ,p_supervisor_assignment_id IN NUMBER DEFAULT hr_api.g_number
2689 ,p_time_normal_finish IN VARCHAR2 DEFAULT hr_api.g_varchar2
2690 ,p_time_normal_start IN VARCHAR2 DEFAULT hr_api.g_varchar2
2691 ,p_title IN VARCHAR2 DEFAULT hr_api.g_varchar2
2692 ,p_vendor_assignment_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
2693 ,p_vendor_employee_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
2694 ,p_vendor_id IN NUMBER DEFAULT hr_api.g_number
2695 ,p_vendor_site_id IN NUMBER DEFAULT hr_api.g_number
2696 ,p_assignment_status_type_id IN NUMBER DEFAULT hr_api.g_number
2697 ,p_concat_segments IN VARCHAR2 DEFAULT hr_api.g_varchar2
2698 ,p_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
2699 ,p_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2700 ,p_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2701 ,p_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2702 ,p_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2703 ,p_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2704 ,p_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2705 ,p_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2706 ,p_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2707 ,p_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2708 ,p_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2709 ,p_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2710 ,p_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2711 ,p_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2712 ,p_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2713 ,p_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2714 ,p_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2715 ,p_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2716 ,p_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2717 ,p_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2718 ,p_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2719 ,p_attribute21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2720 ,p_attribute22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2721 ,p_attribute23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2722 ,p_attribute24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2723 ,p_attribute25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2724 ,p_attribute26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2725 ,p_attribute27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2726 ,p_attribute28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2727 ,p_attribute29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2728 ,p_attribute30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2729 ,p_scl_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2730 ,p_scl_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2731 ,p_scl_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2732 ,p_scl_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2733 ,p_scl_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2734 ,p_scl_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2735 ,p_scl_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2736 ,p_scl_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2737 ,p_scl_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2738 ,p_scl_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2739 ,p_scl_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2740 ,p_scl_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2741 ,p_scl_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2742 ,p_scl_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2743 ,p_scl_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2744 ,p_scl_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2745 ,p_scl_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2746 ,p_scl_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2747 ,p_scl_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2748 ,p_scl_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2749 ,p_scl_segment21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2750 ,p_scl_segment22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2751 ,p_scl_segment23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2752 ,p_scl_segment24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2753 ,p_scl_segment25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2754 ,p_scl_segment26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2755 ,p_scl_segment27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2756 ,p_scl_segment28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2757 ,p_scl_segment29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2758 ,p_scl_segment30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2759 --,p_grade_id IN NUMBER DEFAULT hr_api.g_number
2760 ,p_position_id IN NUMBER DEFAULT hr_api.g_number
2761 ,p_job_id IN NUMBER DEFAULT hr_api.g_number
2762 ,p_location_id IN NUMBER DEFAULT hr_api.g_number
2763 ,p_organization_id IN NUMBER DEFAULT hr_api.g_number
2764 ,p_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2765 ,p_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2766 ,p_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2767 ,p_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2768 ,p_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2769 ,p_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2770 ,p_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2771 ,p_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2772 ,p_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2773 ,p_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2774 ,p_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2775 ,p_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2776 ,p_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2777 ,p_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2778 ,p_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2779 ,p_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2780 ,p_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2781 ,p_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2782 ,p_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2783 ,p_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2784 ,p_segment21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2785 ,p_segment22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2786 ,p_segment23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2787 ,p_segment24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2788 ,p_segment25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2789 ,p_segment26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2790 ,p_segment27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2791 ,p_segment28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2792 ,p_segment29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2793 ,p_segment30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
2794 ,p_projected_assignment_end IN DATE DEFAULT hr_api.g_date
2795 ,p_org_now_no_manager_warning OUT NOCOPY BOOLEAN
2796 ,p_effective_start_date OUT NOCOPY DATE
2797 ,p_effective_end_date OUT NOCOPY DATE
2798 ,p_comment_id OUT NOCOPY NUMBER
2799 ,p_no_managers_warning OUT NOCOPY BOOLEAN
2800 ,p_other_manager_warning OUT NOCOPY BOOLEAN
2801 ,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
2802 ,p_concatenated_segments OUT NOCOPY VARCHAR2
2803 ,p_hourly_salaried_warning OUT NOCOPY BOOLEAN
2804 ,p_scl_concat_segments OUT NOCOPY VARCHAR2
2805 ,p_people_group_name OUT NOCOPY VARCHAR2
2806 ,p_people_group_id OUT NOCOPY NUMBER
2807 ,p_spp_delete_warning OUT NOCOPY BOOLEAN
2808 ,p_entries_changed_warning OUT NOCOPY VARCHAR2
2809 ,p_tax_district_changed_warning OUT NOCOPY BOOLEAN) IS
2810 --
2811 -- Define Local Variables
2812 --
2813 l_proc VARCHAR2(72) := g_package||'update_cwk_assignment';
2814 l_effective_date DATE;
2815 l_message_text VARCHAR2(255);
2816 l_log_message VARCHAR2(255);
2817 l_already_errored BOOLEAN := FALSE;
2818 --
2819 -- OUT Parameters for update_cwk_asg
2820 --
2821 l_object_version_number NUMBER := p_object_version_number;
2822 l_orig_ovn NUMBER := p_object_version_number;
2823 l_org_now_no_manager_warning BOOLEAN;
2824 l_effective_start_date DATE;
2825 l_effective_end_date DATE;
2826 l_comment_id NUMBER;
2827 l_no_managers_warning BOOLEAN;
2828 l_other_manager_warning BOOLEAN;
2829 l_soft_coding_keyflex_id NUMBER;
2830 l_concatenated_segments VARCHAR2(2000);
2831 l_hourly_salaried_warning BOOLEAN;
2832 --
2833 -- OUT Parameters for update_cwk_asg_Criteria
2834 --
2835 l_people_group_name VARCHAR2(240);
2836 l_people_group_id NUMBER;
2837 l_spp_delete_warning BOOLEAN;
2838 l_entries_changed_warning VARCHAR2(10);
2839 l_tax_district_changed_warning BOOLEAN;
2840 l_scl_concat_segments VARCHAR2(4000);
2841 --
2842 BEGIN
2843 --
2844 hr_utility.set_location('Entering : '|| l_proc, 10);
2845 --
2846 -- Issue a savepoint.
2847 --
2848 SAVEPOINT update_cwk_assignment;
2849 --
2850 l_effective_date := TRUNC(p_effective_date);
2851 --
2852 BEGIN
2853 --
2854 hr_assignment_api.update_cwk_asg
2855 (p_validate => p_validate
2856 ,p_effective_date => p_effective_date
2857 ,p_datetrack_update_mode => p_datetrack_update_mode
2858 ,p_assignment_id => p_assignment_id
2859 ,p_object_version_number => l_object_version_number
2860 ,p_assignment_category => p_assignment_category
2861 ,p_assignment_number => p_assignment_number
2862 ,p_change_reason => p_change_reason
2863 ,p_comments => p_comments
2864 ,p_default_code_comb_id => p_default_code_comb_id
2865 ,p_establishment_id => p_establishment_id
2866 ,p_frequency => p_frequency
2867 ,p_internal_address_line => p_internal_address_line
2868 ,p_labour_union_member_flag => p_labour_union_member_flag
2869 ,p_manager_flag => p_manager_flag
2870 ,p_normal_hours => p_normal_hours
2871 ,p_project_title => p_project_title
2872 ,p_set_of_books_id => p_set_of_books_id
2873 ,p_source_type => p_source_type
2874 ,p_supervisor_id => p_supervisor_id
2875 ,p_supervisor_assignment_id => p_supervisor_assignment_id
2876 ,p_time_normal_finish => p_time_normal_finish
2877 ,p_time_normal_start => p_time_normal_start
2878 ,p_title => p_title
2879 ,p_vendor_assignment_number => p_vendor_assignment_number
2880 ,p_vendor_employee_number => p_vendor_employee_number
2881 ,p_vendor_id => p_vendor_id
2882 ,p_vendor_site_id => p_vendor_site_id
2883 ,p_assignment_status_type_id => p_assignment_status_type_id
2884 ,p_concat_segments => p_concat_segments
2885 ,p_attribute_category => p_attribute_category
2886 ,p_attribute1 => p_attribute1
2887 ,p_attribute2 => p_attribute2
2888 ,p_attribute3 => p_attribute3
2889 ,p_attribute4 => p_attribute4
2890 ,p_attribute5 => p_attribute5
2891 ,p_attribute6 => p_attribute6
2892 ,p_attribute7 => p_attribute7
2893 ,p_attribute8 => p_attribute8
2894 ,p_attribute9 => p_attribute9
2895 ,p_attribute10 => p_attribute10
2896 ,p_attribute11 => p_attribute11
2897 ,p_attribute12 => p_attribute12
2898 ,p_attribute13 => p_attribute13
2899 ,p_attribute14 => p_attribute14
2900 ,p_attribute15 => p_attribute15
2901 ,p_attribute16 => p_attribute16
2902 ,p_attribute17 => p_attribute17
2903 ,p_attribute18 => p_attribute18
2904 ,p_attribute19 => p_attribute19
2905 ,p_attribute20 => p_attribute20
2906 ,p_attribute21 => p_attribute21
2907 ,p_attribute22 => p_attribute22
2908 ,p_attribute23 => p_attribute23
2909 ,p_attribute24 => p_attribute24
2910 ,p_attribute25 => p_attribute25
2911 ,p_attribute26 => p_attribute26
2912 ,p_attribute27 => p_attribute27
2913 ,p_attribute28 => p_attribute28
2914 ,p_attribute29 => p_attribute29
2915 ,p_attribute30 => p_attribute30
2916 ,p_scl_segment1 => p_scl_segment1
2917 ,p_scl_segment2 => p_scl_segment2
2918 ,p_scl_segment3 => p_scl_segment3
2919 ,p_scl_segment4 => p_scl_segment4
2920 ,p_scl_segment5 => p_scl_segment5
2921 ,p_scl_segment6 => p_scl_segment6
2922 ,p_scl_segment7 => p_scl_segment7
2923 ,p_scl_segment8 => p_scl_segment8
2924 ,p_scl_segment9 => p_scl_segment9
2925 ,p_scl_segment10 => p_scl_segment10
2926 ,p_scl_segment11 => p_scl_segment11
2927 ,p_scl_segment12 => p_scl_segment12
2928 ,p_scl_segment13 => p_scl_segment13
2929 ,p_scl_segment14 => p_scl_segment14
2930 ,p_scl_segment15 => p_scl_segment15
2931 ,p_scl_segment16 => p_scl_segment16
2932 ,p_scl_segment17 => p_scl_segment17
2933 ,p_scl_segment18 => p_scl_segment18
2934 ,p_scl_segment19 => p_scl_segment19
2935 ,p_scl_segment20 => p_scl_segment20
2936 ,p_scl_segment21 => p_scl_segment21
2937 ,p_scl_segment22 => p_scl_segment22
2938 ,p_scl_segment23 => p_scl_segment23
2939 ,p_scl_segment24 => p_scl_segment24
2940 ,p_scl_segment25 => p_scl_segment25
2941 ,p_scl_segment26 => p_scl_segment26
2942 ,p_scl_segment27 => p_scl_segment27
2943 ,p_scl_segment28 => p_scl_segment28
2944 ,p_scl_segment29 => p_scl_segment29
2945 ,p_scl_segment30 => p_scl_segment30
2946 ,p_projected_assignment_end => p_projected_assignment_end
2947 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2948 ,p_effective_start_date => l_effective_start_date
2949 ,p_effective_end_date => l_effective_end_date
2950 ,p_comment_id => l_comment_id
2951 ,p_no_managers_warning => l_no_managers_warning
2952 ,p_other_manager_warning => l_other_manager_warning
2953 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2954 ,p_concatenated_segments => l_concatenated_segments
2955 ,p_hourly_salaried_warning => l_hourly_salaried_warning);
2956 --
2957 hr_utility.set_location(l_proc, 20);
2958 --
2959 IF l_no_managers_warning = TRUE THEN
2960 --
2961 hr_utility.set_message(800,'HR_289214_NO_MANAGERS');
2962 l_message_text := 'WARNING: '||hr_utility.get_message;
2963 --
2964 Log_Error(p_type => 'WARNING',
2965 p_assignment_number => p_assignment_number,
2966 p_warning_message => l_message_text,
2967 p_already_errored => l_already_errored);
2968 --
2969 ELSIF l_other_manager_warning = TRUE THEN
2970 --
2971 hr_utility.set_message(800,'HR_289215_DUPLICATE_MANAGERS');
2972 l_message_text := 'WARNING: '||hr_utility.get_message;
2973 --
2974 Log_Error(p_type => 'WARNING',
2975 p_assignment_number => p_assignment_number,
2976 p_warning_message => l_message_text,
2977 p_already_errored => l_already_errored);
2978 --
2979 ELSIF l_hourly_salaried_warning = TRUE THEN
2980 --
2981 hr_utility.set_message(800,'HR_289648_CWK_HR_CODE_NOT_NULL');
2982 l_message_text := 'WARNING: '||hr_utility.get_message;
2983 --
2984 Log_Error(p_type => 'WARNING',
2985 p_assignment_number => p_assignment_number,
2986 p_warning_message => l_message_text,
2987 p_already_errored => l_already_errored);
2988 --
2989 END IF;
2990 --
2991 hr_utility.set_location(l_proc, 30);
2992 --
2993 END;
2994 --
2995 BEGIN
2996 --
2997 hr_assignment_api.update_cwk_asg_criteria
2998 (p_validate => p_validate
2999 ,p_effective_date => p_effective_date
3000 ,p_datetrack_update_mode => p_datetrack_update_mode
3001 ,p_assignment_id => p_assignment_id
3002 ,p_called_from_mass_update => TRUE
3003 ,p_object_version_number => l_object_version_number
3004 -- ,p_grade_id => p_grade_id
3005 ,p_position_id => p_position_id
3006 ,p_job_id => p_job_id
3007 ,p_location_id => p_location_id
3008 ,p_organization_id => p_organization_id
3009 --
3010 -- p_pay_basis_id for future phases of cwk
3011 --
3012 --,p_pay_basis_id => NULL
3013 ,p_segment1 => p_segment1
3014 ,p_segment2 => p_segment2
3015 ,p_segment3 => p_segment3
3016 ,p_segment4 => p_segment4
3017 ,p_segment5 => p_segment5
3018 ,p_segment6 => p_segment6
3019 ,p_segment7 => p_segment7
3020 ,p_segment8 => p_segment8
3021 ,p_segment9 => p_segment9
3022 ,p_segment10 => p_segment10
3023 ,p_segment11 => p_segment11
3024 ,p_segment12 => p_segment12
3025 ,p_segment13 => p_segment13
3026 ,p_segment14 => p_segment14
3027 ,p_segment15 => p_segment15
3028 ,p_segment16 => p_segment16
3029 ,p_segment17 => p_segment17
3030 ,p_segment18 => p_segment18
3031 ,p_segment19 => p_segment19
3032 ,p_segment20 => p_segment20
3033 ,p_segment21 => p_segment21
3034 ,p_segment22 => p_segment22
3035 ,p_segment23 => p_segment23
3036 ,p_segment24 => p_segment24
3037 ,p_segment25 => p_segment25
3038 ,p_segment26 => p_segment26
3039 ,p_segment27 => p_segment27
3040 ,p_segment28 => p_segment28
3041 ,p_segment29 => p_segment29
3042 ,p_segment30 => p_segment30
3043 ,p_concat_segments => l_scl_concat_segments
3044 ,p_people_group_name => l_people_group_name
3045 ,p_effective_start_date => l_effective_start_date
3046 ,p_effective_end_date => l_effective_end_date
3047 ,p_people_group_id => l_people_group_id
3048 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3049 ,p_other_manager_warning => l_other_manager_warning
3050 ,p_spp_delete_warning => l_spp_delete_warning
3051 ,p_entries_changed_warning => l_entries_changed_warning
3052 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
3053 --
3054 hr_utility.set_location(l_proc, 40);
3055 --
3056 IF l_org_now_no_manager_warning = TRUE THEN
3057 --
3058 hr_utility.set_message(800,'HR_289214_NO_MANAGERS');
3059 l_message_text := 'WARNING: '||hr_utility.get_message;
3060 --
3061 Log_Error(p_type => 'WARNING',
3062 p_assignment_number => p_assignment_number,
3063 p_warning_message => l_message_text,
3064 p_already_errored => l_already_errored);
3065 --
3066 ELSIF l_other_manager_warning = TRUE THEN
3067 --
3068 hr_utility.set_message(800,'HR_289215_DUPLICATE_MANAGERS');
3069 l_message_text := 'WARNING: '||hr_utility.get_message;
3070 --
3071 Log_Error(p_type => 'WARNING',
3072 p_assignment_number => p_assignment_number,
3073 p_warning_message => l_message_text,
3074 p_already_errored => l_already_errored);
3075 --
3076 ELSIF l_spp_delete_warning = TRUE THEN
3077 --
3078 hr_utility.set_message(800,'HR_289826_SPP_DELETE_WARN_API');
3079 l_message_text := 'WARNING: '||hr_utility.get_message;
3080 --
3081 Log_Error(p_type => 'WARNING',
3082 p_assignment_number => p_assignment_number,
3083 p_warning_message => l_message_text,
3084 p_already_errored => l_already_errored);
3085 --
3086 END IF;
3087 --
3088 hr_utility.set_location(l_proc, 50);
3089 --
3090 END;
3091 --
3092 -- When in validation only mode raise the Validate_Enabled exception
3093 --
3094 IF p_validate THEN
3095 --
3096 RAISE hr_api.validate_enabled;
3097 --
3098 END IF;
3099 --
3100 -- Set all out parameters
3101 --
3102 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
3103 p_effective_start_date := l_effective_start_date;
3104 p_effective_end_date := l_effective_end_date;
3105 p_comment_id := l_comment_id;
3106 p_no_managers_warning := l_no_managers_warning;
3107 p_other_manager_warning := l_other_manager_warning;
3108 p_soft_coding_keyflex_id := l_soft_coding_keyflex_id;
3109 p_concatenated_segments := l_concatenated_segments;
3110 p_hourly_salaried_warning := l_hourly_salaried_warning;
3111 p_scl_concat_segments := l_scl_concat_segments;
3112 p_people_group_name := l_people_group_name;
3113 p_people_group_id := l_people_group_id ;
3114 p_spp_delete_warning := l_spp_delete_warning;
3115 p_entries_changed_warning := l_entries_changed_warning;
3116 p_tax_district_changed_warning := l_tax_district_changed_warning;
3117 --
3118 hr_utility.set_location('Leaving : '||l_proc, 997);
3119 --
3120 EXCEPTION
3121 --
3122 WHEN hr_api.validate_enabled THEN
3123 --
3124 hr_utility.set_location('Leaving : '||l_proc, 998);
3125 --
3126 --
3127 -- As the Validate_Enabled exception has been raised
3128 -- we must rollback to the savepoint
3129 --
3130 ROLLBACK TO update_cwk_asg;
3131 --
3132 -- Only set output warning arguments
3133 -- (Any key or derived arguments must be set to null
3134 -- when validation only mode is being used.)
3135 --
3136 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
3137 p_effective_start_date := NULL;
3138 p_effective_end_date := NULL;
3139 p_comment_id := NULL;
3140 p_no_managers_warning := l_no_managers_warning;
3141 p_other_manager_warning := l_other_manager_warning;
3142 p_soft_coding_keyflex_id := NULL;
3143 p_concatenated_segments := NULL;
3144 p_hourly_salaried_warning := l_hourly_salaried_warning;
3145 p_scl_concat_segments := NULL;
3146 p_people_group_name := NULL;
3147 p_people_group_id := NULL;
3148 p_spp_delete_warning := l_spp_delete_warning;
3149 p_entries_changed_warning := l_entries_changed_warning;
3150 p_tax_district_changed_warning := l_tax_district_changed_warning;
3151 --
3152 WHEN OTHERS THEN
3153 --
3154 hr_utility.set_location('Leaving : '||l_proc, 999);
3155 --
3156 ROLLBACK TO update_cwk_asg;
3157 --
3158 -- Only set output warning arguments
3159 -- (Any key or derived arguments must be set to null
3160 -- when validation only mode is being used.)
3161 --
3162 p_org_now_no_manager_warning := NULL;
3163 p_effective_start_date := NULL;
3164 p_effective_end_date := NULL;
3165 p_comment_id := NULL;
3166 p_no_managers_warning := NULL;
3167 p_other_manager_warning := NULL;
3168 p_soft_coding_keyflex_id := NULL;
3169 p_concatenated_segments := NULL;
3170 p_hourly_salaried_warning := NULL;
3171 p_scl_concat_segments := NULL;
3172 p_people_group_name := NULL;
3173 p_people_group_id := NULL;
3174 p_spp_delete_warning := NULL;
3175 p_entries_changed_warning := NULL;
3176 p_tax_district_changed_warning := NULL;
3177 p_object_version_number := l_orig_ovn;
3178 --
3179 Log_Error(p_type => 'ERROR',
3180 p_assignment_number => p_assignment_number,
3181 p_warning_message => NULL,
3182 p_already_errored => l_already_errored);
3183 --
3184 RAISE;
3185 --
3186 END update_cwk_assignment;
3187 --
3188 -- ---------------------------------------------------------------------------+
3189 -- |-------------------------< UPDATE_ASSIGNMENT >----------------------------+
3190 -- ---------------------------------------------------------------------------+
3191 --
3192 PROCEDURE update_assignment
3193 (p_validate IN BOOLEAN DEFAULT FALSE
3194 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT hr_api.g_varchar2
3195 ,p_object_version_number IN OUT NOCOPY NUMBER
3196 ,p_ass_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
3197 ,p_ass_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3198 ,p_ass_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3199 ,p_ass_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3200 ,p_ass_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3201 ,p_ass_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3202 ,p_ass_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3203 ,p_ass_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3204 ,p_ass_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3205 ,p_ass_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3206 ,p_ass_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3207 ,p_ass_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3208 ,p_ass_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3209 ,p_ass_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3210 ,p_ass_attribute21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3211 ,p_ass_attribute22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3212 ,p_ass_attribute23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3213 ,p_ass_attribute24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3214 ,p_ass_attribute25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3215 ,p_ass_attribute26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3216 ,p_ass_attribute27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3217 ,p_ass_attribute28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3218 ,p_ass_attribute29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3219 ,p_ass_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3220 ,p_ass_attribute30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3221 ,p_ass_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3222 ,p_ass_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3223 ,p_ass_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3224 ,p_ass_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3225 ,p_ass_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3226 ,p_ass_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3227 ,p_assignment_id IN NUMBER DEFAULT hr_api.g_number
3228 ,p_assignment_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
3229 ,p_assignment_status_type_id IN NUMBER DEFAULT hr_api.g_number
3230 ,p_bargaining_unit_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
3231 ,p_cagr_id_flex_num IN NUMBER DEFAULT hr_api.g_number
3232 ,p_change_reason IN VARCHAR2 DEFAULT hr_api.g_varchar2
3233 ,p_collective_agreement_id IN NUMBER DEFAULT hr_api.g_number
3234 ,p_contract_id IN NUMBER DEFAULT hr_api.g_number
3235 ,p_date_probation_end IN DATE DEFAULT hr_api.g_date
3236 ,p_default_code_comb_id IN NUMBER DEFAULT hr_api.g_number
3237 ,p_establishment_id IN NUMBER DEFAULT hr_api.g_number
3238 ,p_employment_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
3239 ,p_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
3240 ,p_grade_id IN NUMBER DEFAULT hr_api.g_number
3241 ,p_hourly_salaried_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
3242 ,p_internal_address_line IN VARCHAR2 DEFAULT hr_api.g_varchar2
3243 ,p_job_id IN NUMBER DEFAULT hr_api.g_number
3244 ,p_labour_union_member_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
3245 ,p_location_id IN NUMBER DEFAULT hr_api.g_number
3246 ,p_manager_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
3247 ,p_normal_hours IN NUMBER DEFAULT hr_api.g_number
3248 ,p_pay_basis_id IN NUMBER DEFAULT hr_api.g_number
3249 ,p_payroll_id IN NUMBER DEFAULT hr_api.g_number
3250 ,p_perf_review_period IN NUMBER DEFAULT hr_api.g_number
3251 ,p_perf_review_period_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
3252 ,p_position_id IN NUMBER DEFAULT hr_api.g_number
3253 ,p_probation_period IN NUMBER DEFAULT hr_api.g_number
3254 ,p_probation_unit IN VARCHAR2 DEFAULT hr_api.g_varchar2
3255 ,p_sal_review_period IN NUMBER DEFAULT hr_api.g_number
3256 ,p_sal_review_period_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
3257 ,p_set_of_books_id IN NUMBER DEFAULT hr_api.g_number
3258 ,p_source_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
3259 ,p_supervisor_id IN NUMBER DEFAULT hr_api.g_number
3260 ,p_supervisor_assignment_id IN NUMBER DEFAULT hr_api.g_number
3261 ,p_time_normal_finish IN VARCHAR2 DEFAULT hr_api.g_varchar2
3262 ,p_time_normal_start IN VARCHAR2 DEFAULT hr_api.g_varchar2
3263 ,p_title IN VARCHAR2 DEFAULT hr_api.g_varchar2
3264 ,p_cag_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3265 ,p_cag_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3266 ,p_cag_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3267 ,p_cag_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3268 ,p_cag_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3269 ,p_cag_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3270 ,p_cag_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3271 ,p_cag_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3272 ,p_cag_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3273 ,p_cag_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3274 ,p_cag_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3275 ,p_cag_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3276 ,p_cag_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3277 ,p_cag_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3278 ,p_cag_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3279 ,p_cag_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3280 ,p_cag_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3281 ,p_cag_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3282 ,p_cag_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3283 ,p_cag_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3284 ,p_comments IN VARCHAR2 DEFAULT hr_api.g_varchar2
3285 ,p_concat_segments IN VARCHAR2 DEFAULT hr_api.g_varchar2
3286 ,p_effective_date IN DATE DEFAULT hr_api.g_date
3287 ,p_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3288 ,p_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3289 ,p_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3290 ,p_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3291 ,p_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3292 ,p_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3293 ,p_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3294 ,p_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3295 ,p_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3296 ,p_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3297 ,p_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3298 ,p_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3299 ,p_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3300 ,p_segment21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3301 ,p_segment22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3302 ,p_segment23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3303 ,p_segment24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3304 ,p_segment25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3305 ,p_segment26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3306 ,p_segment27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3307 ,p_segment28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3308 ,p_segment29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3309 ,p_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3310 ,p_segment30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3311 ,p_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3312 ,p_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3313 ,p_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3314 ,p_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3315 ,p_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3316 ,p_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3317 ,p_scl_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3318 ,p_scl_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3319 ,p_scl_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3320 ,p_scl_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3321 ,p_scl_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3322 ,p_scl_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3323 ,p_scl_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3324 ,p_scl_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3325 ,p_scl_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3326 ,p_scl_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3327 ,p_scl_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3328 ,p_scl_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3329 ,p_scl_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3330 ,p_scl_segment21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3331 ,p_scl_segment22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3332 ,p_scl_segment23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3333 ,p_scl_segment24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3334 ,p_scl_segment25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3335 ,p_scl_segment26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3336 ,p_scl_segment27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3337 ,p_scl_segment28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3338 ,p_scl_segment29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3339 ,p_scl_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3340 ,p_scl_segment30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3341 ,p_scl_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3342 ,p_scl_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3343 ,p_scl_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3344 ,p_scl_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3345 ,p_scl_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3346 ,p_scl_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
3347 ,p_grade_ladder_pgm_id IN NUMBER DEFAULT hr_api.g_number
3348 ,p_recruiter_id IN NUMBER DEFAULT hr_api.g_number
3349 ,p_person_referred_by_id IN NUMBER DEFAULT hr_api.g_number
3350 ,p_recruitment_activity_id IN NUMBER DEFAULT hr_api.g_number
3351 ,p_source_organization_id IN NUMBER DEFAULT hr_api.g_number
3352 ,p_vacancy_id IN NUMBER DEFAULT hr_api.g_number
3353 ,p_application_id IN NUMBER DEFAULT hr_api.g_number
3354 ,p_vendor_assignment_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
3355 ,p_vendor_employee_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
3356 ,p_vendor_id IN NUMBER DEFAULT hr_api.g_number
3357 ,p_vendor_site_id IN NUMBER DEFAULT hr_api.g_number
3358 ,p_project_title IN VARCHAR2 DEFAULT hr_api.g_varchar2
3359 ,p_projected_assignment_end IN DATE DEFAULT hr_api.g_date
3360 ,p_organization_id IN OUT NOCOPY NUMBER
3361 ,p_concatenated_segments IN OUT NOCOPY VARCHAR2
3362 ,p_special_ceiling_step_id IN OUT NOCOPY NUMBER
3363 ,p_cagr_grade_def_id IN OUT NOCOPY NUMBER
3364 ,p_comment_id OUT NOCOPY NUMBER
3365 ,p_cagr_concatenated_segments OUT NOCOPY VARCHAR2
3366 ,p_effective_end_date OUT NOCOPY DATE
3367 ,p_effective_start_date OUT NOCOPY DATE
3368 ,p_no_managers_warning OUT NOCOPY BOOLEAN
3369 ,p_other_manager_warning OUT NOCOPY BOOLEAN
3370 ,p_gsp_post_process_warning OUT NOCOPY VARCHAR2
3371 ,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
3372 ,p_entries_changed_warning OUT NOCOPY VARCHAR2
3373 ,p_group_name OUT NOCOPY VARCHAR2
3374 ,p_org_now_no_manager_warning OUT NOCOPY BOOLEAN
3375 ,p_people_group_id OUT NOCOPY NUMBER
3376 ,p_spp_delete_warning OUT NOCOPY BOOLEAN
3377 ,p_tax_district_changed_warning OUT NOCOPY BOOLEAN ) IS
3378 --
3379 l_log_message VARCHAR2(255);
3380 l_already_errored BOOLEAN := FALSE;
3381 l_pay_proposal_warning BOOLEAN := FALSE;
3382 l_proc VARCHAR2(72) := g_package||'update_assignment';
3383 l_effective_date DATE;
3384 l_assignment_status per_assignment_status_types.per_system_status%TYPE;
3385 --
3386 l_non_person_type_fields BOOLEAN := FALSE;
3387 l_message_text VARCHAR2(255);
3388 l_dummy_b BOOLEAN;
3389 l_dummy_n NUMBER := null;
3390 l_dummy_v VARCHAR2(4000);
3391 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
3392 --
3393 l_object_version_number NUMBER := p_object_version_number ;
3394 l_organization_id NUMBER := p_organization_id ;
3395 l_special_ceiling_step_id NUMBER := p_special_ceiling_step_id ;
3396 L_CAGR_GRADE_DEF_ID NUMBER := p_CAGR_GRADE_DEF_ID;
3397 l_concatenated_segments VARCHAR2(4000) := p_concatenated_segments ;
3398 l_concat_segments VARCHAR2(4000):= p_concat_segments;
3399 l_org_now_no_manager_warning BOOLEAN;
3400 l_effective_start_date DATE;
3401 l_effective_end_date DATE;
3402 l_comment_id NUMBER;
3403 l_no_managers_warning BOOLEAN;
3404 l_other_manager_warning BOOLEAN;
3405 l_soft_coding_keyflex_id NUMBER;
3406 l_hourly_salaried_warning BOOLEAN;
3407 l_scl_concat_segments VARCHAR2(4000);
3408 l_people_group_name VARCHAR2(240);
3409 l_people_group_id NUMBER;
3410 l_spp_delete_warning BOOLEAN;
3411 l_entries_changed_warning VARCHAR2(10);
3412 l_tax_district_changed_warning BOOLEAN;
3413 --
3414 CURSOR csr_assignment_type IS
3415 SELECT assignment_type
3416 FROM per_assignments_f paf
3417 WHERE paf.assignment_id = p_assignment_id
3418 AND l_effective_date BETWEEN paf.effective_start_Date
3419 AND paf.effective_end_date;
3420 --
3421 BEGIN
3422 --
3423 --hr_utility.trace_on(NULL,'ORACLE'); --ynegoro
3424 hr_utility.set_location('Entering : '|| l_proc, 10);
3425 --
3426 -- Issue a savepoint.
3427 --
3428 SAVEPOINT upd_asg;
3429 --
3430 l_effective_date := TRUNC(p_effective_date);
3431 --
3432 OPEN csr_assignment_type;
3433 FETCH csr_assignment_type INTO l_assignment_type;
3434 --
3435 IF csr_assignment_type%NOTFOUND THEN
3436 --
3437 CLOSE csr_assignment_type;
3438 --
3439 hr_utility.set_message(801,'HR_449903_INV_ASG_TYPE');
3440 hr_utility.raise_error;
3441 --
3442 END IF;
3443 --
3444 IF l_assignment_type = 'E' THEN
3445 --
3446 upd_asg
3447 (p_ass_attribute_category => p_ass_attribute_category
3448 ,p_ass_attribute1 => p_ass_attribute1
3449 ,p_ass_attribute10 => p_ass_attribute10
3450 ,p_ass_attribute11 => p_ass_attribute11
3451 ,p_ass_attribute12 => p_ass_attribute12
3452 ,p_ass_attribute13 => p_ass_attribute13
3453 ,p_ass_attribute14 => p_ass_attribute14
3454 ,p_ass_attribute15 => p_ass_attribute15
3455 ,p_ass_attribute16 => p_ass_attribute16
3456 ,p_ass_attribute17 => p_ass_attribute17
3457 ,p_ass_attribute18 => p_ass_attribute18
3458 ,p_ass_attribute19 => p_ass_attribute19
3459 ,p_ass_attribute2 => p_ass_attribute2
3460 ,p_ass_attribute20 => p_ass_attribute20
3461 ,p_ass_attribute21 => p_ass_attribute21
3462 ,p_ass_attribute22 => p_ass_attribute22
3463 ,p_ass_attribute23 => p_ass_attribute23
3464 ,p_ass_attribute24 => p_ass_attribute24
3465 ,p_ass_attribute25 => p_ass_attribute25
3466 ,p_ass_attribute26 => p_ass_attribute26
3467 ,p_ass_attribute27 => p_ass_attribute27
3468 ,p_ass_attribute28 => p_ass_attribute28
3469 ,p_ass_attribute29 => p_ass_attribute29
3470 ,p_ass_attribute3 => p_ass_attribute3
3471 ,p_ass_attribute30 => p_ass_attribute30
3472 ,p_ass_attribute4 => p_ass_attribute4
3473 ,p_ass_attribute5 => p_ass_attribute5
3474 ,p_ass_attribute6 => p_ass_attribute6
3475 ,p_ass_attribute7 => p_ass_attribute7
3476 ,p_ass_attribute8 => p_ass_attribute8
3477 ,p_ass_attribute9 => p_ass_attribute9
3478 ,p_assignment_id => p_assignment_id
3479 ,p_assignment_number => p_assignment_number
3480 ,p_assignment_status_type_id => p_assignment_status_type_id
3481 ,p_bargaining_unit_code => p_bargaining_unit_code
3482 ,p_cagr_id_flex_num => p_cagr_id_flex_num
3483 ,p_change_reason => p_change_reason
3484 ,p_collective_agreement_id => p_collective_agreement_id
3485 ,p_contract_id => p_contract_id
3486 ,p_date_probation_end => p_date_probation_end
3487 ,p_default_code_comb_id => p_default_code_comb_id
3488 ,p_establishment_id => p_establishment_id
3489 ,p_employment_category => p_employment_category
3490 ,p_frequency => p_frequency
3491 ,p_grade_id => p_grade_id
3492 ,p_hourly_salaried_code => p_hourly_salaried_code
3493 ,p_internal_address_line => p_internal_address_line
3494 ,p_job_id => p_job_id
3495 ,p_labour_union_member_flag => p_labour_union_member_flag
3496 ,p_location_id => p_location_id
3497 ,p_manager_flag => p_manager_flag
3498 ,p_normal_hours => p_normal_hours
3499 ,p_pay_basis_id => p_pay_basis_id
3500 ,p_payroll_id => p_payroll_id
3501 ,p_perf_review_period => p_perf_review_period
3502 ,p_perf_review_period_frequency => p_perf_review_period_frequency
3503 ,p_position_id => p_position_id
3504 ,p_probation_period => p_probation_period
3505 ,p_probation_unit => p_probation_unit
3506 ,p_sal_review_period => p_sal_review_period
3507 ,p_sal_review_period_frequency => p_sal_review_period_frequency
3508 ,p_set_of_books_id => p_set_of_books_id
3509 ,p_source_type => p_source_type
3510 ,p_supervisor_id => p_supervisor_id
3511 ,p_supervisor_assignment_id => p_supervisor_assignment_id
3512 ,p_time_normal_finish => p_time_normal_finish
3513 ,p_time_normal_start => p_time_normal_start
3514 ,p_title => p_title
3515 ,p_cag_segment1 => p_cag_segment1
3516 ,p_cag_segment10 => p_cag_segment10
3517 ,p_cag_segment11 => p_cag_segment11
3518 ,p_cag_segment12 => p_cag_segment12
3519 ,p_cag_segment13 => p_cag_segment13
3520 ,p_cag_segment14 => p_cag_segment14
3521 ,p_cag_segment15 => p_cag_segment15
3522 ,p_cag_segment16 => p_cag_segment16
3523 ,p_cag_segment17 => p_cag_segment17
3524 ,p_cag_segment18 => p_cag_segment18
3525 ,p_cag_segment19 => p_cag_segment19
3526 ,p_cag_segment2 => p_cag_segment2
3527 ,p_cag_segment20 => p_cag_segment20
3528 ,p_cag_segment3 => p_cag_segment3
3529 ,p_cag_segment4 => p_cag_segment4
3530 ,p_cag_segment5 => p_cag_segment5
3531 ,p_cag_segment6 => p_cag_segment6
3532 ,p_cag_segment7 => p_cag_segment7
3533 ,p_cag_segment8 => p_cag_segment8
3534 ,p_cag_segment9 => p_cag_segment9
3535 ,p_comments => p_comments
3536 ,p_concat_segments => p_concat_segments
3537 ,p_datetrack_update_mode => p_datetrack_update_mode
3538 ,p_effective_date => p_effective_date
3539 ,p_segment1 => p_segment1
3540 ,p_segment10 => p_segment10
3541 ,p_segment11 => p_segment11
3542 ,p_segment12 => p_segment12
3543 ,p_segment13 => p_segment13
3544 ,p_segment14 => p_segment14
3545 ,p_segment15 => p_segment15
3546 ,p_segment16 => p_segment16
3547 ,p_segment17 => p_segment17
3548 ,p_segment18 => p_segment18
3549 ,p_segment19 => p_segment19
3550 ,p_segment2 => p_segment2
3551 ,p_segment20 => p_segment20
3552 ,p_segment21 => p_segment21
3553 ,p_segment22 => p_segment22
3554 ,p_segment23 => p_segment23
3555 ,p_segment24 => p_segment24
3556 ,p_segment25 => p_segment25
3557 ,p_segment26 => p_segment26
3558 ,p_segment27 => p_segment27
3559 ,p_segment28 => p_segment28
3560 ,p_segment29 => p_segment29
3561 ,p_segment3 => p_segment3
3562 ,p_segment30 => p_segment30
3563 ,p_segment4 => p_segment4
3564 ,p_segment5 => p_segment5
3565 ,p_segment6 => p_segment6
3566 ,p_segment7 => p_segment7
3567 ,p_segment8 => p_segment8
3568 ,p_segment9 => p_segment9
3569 ,p_scl_segment1 => p_scl_segment1
3570 ,p_scl_segment10 => p_scl_segment10
3571 ,p_scl_segment11 => p_scl_segment11
3572 ,p_scl_segment12 => p_scl_segment12
3573 ,p_scl_segment13 => p_scl_segment13
3574 ,p_scl_segment14 => p_scl_segment14
3575 ,p_scl_segment15 => p_scl_segment15
3576 ,p_scl_segment16 => p_scl_segment16
3577 ,p_scl_segment17 => p_scl_segment17
3578 ,p_scl_segment18 => p_scl_segment18
3579 ,p_scl_segment19 => p_scl_segment19
3580 ,p_scl_segment2 => p_scl_segment2
3581 ,p_scl_segment20 => p_scl_segment20
3582 ,p_scl_segment21 => p_scl_segment21
3583 ,p_scl_segment22 => p_scl_segment22
3584 ,p_scl_segment23 => p_scl_segment23
3585 ,p_scl_segment24 => p_scl_segment24
3586 ,p_scl_segment25 => p_scl_segment25
3587 ,p_scl_segment26 => p_scl_segment26
3588 ,p_scl_segment27 => p_scl_segment27
3589 ,p_scl_segment28 => p_scl_segment28
3590 ,p_scl_segment29 => p_scl_segment29
3591 ,p_scl_segment3 => p_scl_segment3
3592 ,p_scl_segment30 => p_scl_segment30
3593 ,p_scl_segment4 => p_scl_segment4
3594 ,p_scl_segment5 => p_scl_segment5
3595 ,p_scl_segment6 => p_scl_segment6
3596 ,p_scl_segment7 => p_scl_segment7
3597 ,p_scl_segment8 => p_scl_segment8
3598 ,p_scl_segment9 => p_scl_segment9
3599 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
3600 ,p_validate => p_validate
3601 ,p_cagr_grade_def_id => l_cagr_grade_def_id
3602 ,p_concatenated_segments => l_concatenated_segments
3603 ,p_cagr_concatenated_segments => p_cagr_concatenated_segments
3604 ,p_effective_end_date => p_effective_end_date
3605 ,p_effective_start_date => p_effective_start_date
3606 ,p_no_managers_warning => p_no_managers_warning
3607 ,p_other_manager_warning => p_other_manager_warning
3608 ,p_gsp_post_process_warning => p_gsp_post_process_warning
3609 ,p_special_ceiling_step_id => l_special_ceiling_step_id
3610 ,p_entries_changed_warning => p_entries_changed_warning
3611 ,p_group_name => p_group_name
3612 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
3613 ,p_people_group_id => p_people_group_id
3614 ,p_spp_delete_warning => p_spp_delete_warning
3615 ,p_tax_district_changed_warning => p_tax_district_changed_warning
3616 ,p_comment_id => p_comment_id
3617 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
3618 ,p_object_version_number => l_object_version_number
3619 ,p_organization_id => l_organization_id);
3620 --
3621 l_non_person_type_fields :=
3622 chk_for_non_emp_fields
3623 (p_application_id => p_application_id
3624 ,p_person_referred_by_id => p_person_referred_by_id
3625 ,p_project_title => p_project_title
3626 ,p_recruiter_id => p_recruiter_id
3627 ,p_recruitment_activity_id => p_recruitment_activity_id
3628 ,p_source_organization_id => p_source_organization_id
3629 ,p_vacancy_id => p_vacancy_id
3630 ,p_vendor_assignment_number => p_vendor_assignment_number
3631 ,p_vendor_employee_number => p_vendor_employee_number
3632 ,p_vendor_id => p_vendor_id
3633 ,p_vendor_site_id => p_vendor_site_id
3634 ,p_projected_assignment_end => p_projected_assignment_end);
3635 --
3636 -- IF non EMP fields have been entered in the mass update
3637 -- then raise a Warning message so the user knows that non-EMP
3638 -- fields have been entered but will not get updated.
3639 --
3640 IF l_non_person_type_fields THEN
3641 --
3642 hr_utility.set_message(800,'HR_449904_NON_EMP_FIELDS');
3643 l_message_text := 'WARNING: '||hr_utility.get_message;
3644 --
3645 Log_Error(p_type => 'WARNING',
3646 p_assignment_number => p_assignment_number,
3647 p_warning_message => l_message_text,
3648 p_already_errored => l_already_errored);
3649 END IF;
3650 --
3651 /*
3652 --
3653 -- Removed as new template will now only cater for
3654 -- EMployees and Contingent Workers.
3655 --
3656 ELSIF l_assignment_type = 'A' THEN
3657 --
3658 update_applicant_asg
3659 (p_validate => p_validate
3660 ,p_effective_date => p_effective_date
3661 ,p_datetrack_update_mode => p_datetrack_update_mode
3662 ,p_assignment_id => p_assignment_id
3663 ,p_object_version_number => p_object_version_number
3664 ,p_recruiter_id => p_recruiter_id
3665 ,p_grade_id => p_grade_id
3666 ,p_position_id => p_position_id
3667 ,p_job_id => p_job_id
3668 ,p_payroll_id => p_payroll_id
3669 ,p_location_id => p_location_id
3670 ,p_person_referred_by_id => p_person_referred_by_id
3671 ,p_assignment_status_type_id => p_assignment_status_type_id
3672 ,p_supervisor_id => p_supervisor_id
3673 ,p_special_ceiling_step_id => p_special_ceiling_step_id
3674 ,p_recruitment_activity_id => p_recruitment_activity_id
3675 ,p_source_organization_id => p_source_organization_id
3676 ,p_organization_id => p_organization_id
3677 ,p_vacancy_id => p_vacancy_id
3678 ,p_pay_basis_id => p_pay_basis_id
3679 ,p_application_id => p_application_id
3680 ,p_change_reason => p_change_reason
3681 ,p_comments => p_comments
3682 ,p_date_probation_end => p_date_probation_end
3683 ,p_default_code_comb_id => p_default_code_comb_id
3684 ,p_employment_category => p_employment_category
3685 ,p_frequency => p_frequency
3686 ,p_internal_address_line => p_internal_address_line
3687 ,p_manager_flag => p_manager_flag
3688 ,p_normal_hours => p_normal_hours
3689 ,p_perf_review_period => p_perf_review_period
3690 ,p_perf_review_period_frequency => p_perf_review_period_frequency
3691 ,p_probation_period => p_probation_period
3692 ,p_probation_unit => p_probation_unit
3693 ,p_sal_review_period => p_sal_review_period
3694 ,p_sal_review_period_frequency => p_sal_review_period_frequency
3695 ,p_set_of_books_id => p_set_of_books_id
3696 ,p_source_type => p_source_type
3697 ,p_time_normal_finish => p_time_normal_finish
3698 ,p_time_normal_start => p_time_normal_start
3699 ,p_bargaining_unit_code => p_bargaining_unit_code
3700 ,p_ass_attribute_category => p_ass_attribute_category
3701 ,p_ass_attribute1 => p_ass_attribute1
3702 ,p_ass_attribute2 => p_ass_attribute2
3703 ,p_ass_attribute3 => p_ass_attribute3
3704 ,p_ass_attribute4 => p_ass_attribute4
3705 ,p_ass_attribute5 => p_ass_attribute5
3706 ,p_ass_attribute6 => p_ass_attribute6
3707 ,p_ass_attribute7 => p_ass_attribute7
3708 ,p_ass_attribute8 => p_ass_attribute8
3709 ,p_ass_attribute9 => p_ass_attribute9
3710 ,p_ass_attribute10 => p_ass_attribute10
3711 ,p_ass_attribute11 => p_ass_attribute11
3712 ,p_ass_attribute12 => p_ass_attribute12
3713 ,p_ass_attribute13 => p_ass_attribute13
3714 ,p_ass_attribute14 => p_ass_attribute14
3715 ,p_ass_attribute15 => p_ass_attribute15
3716 ,p_ass_attribute16 => p_ass_attribute16
3717 ,p_ass_attribute17 => p_ass_attribute17
3718 ,p_ass_attribute18 => p_ass_attribute18
3719 ,p_ass_attribute19 => p_ass_attribute19
3720 ,p_ass_attribute20 => p_ass_attribute20
3721 ,p_ass_attribute21 => p_ass_attribute21
3722 ,p_ass_attribute22 => p_ass_attribute22
3723 ,p_ass_attribute23 => p_ass_attribute23
3724 ,p_ass_attribute24 => p_ass_attribute24
3725 ,p_ass_attribute25 => p_ass_attribute25
3726 ,p_ass_attribute26 => p_ass_attribute26
3727 ,p_ass_attribute27 => p_ass_attribute27
3728 ,p_ass_attribute28 => p_ass_attribute28
3729 ,p_ass_attribute29 => p_ass_attribute29
3730 ,p_ass_attribute30 => p_ass_attribute30
3731 ,p_title => p_title
3732 ,p_scl_segment1 => p_scl_segment1
3733 ,p_scl_segment2 => p_scl_segment2
3734 ,p_scl_segment3 => p_scl_segment3
3735 ,p_scl_segment4 => p_scl_segment4
3736 ,p_scl_segment5 => p_scl_segment5
3737 ,p_scl_segment6 => p_scl_segment6
3738 ,p_scl_segment7 => p_scl_segment7
3739 ,p_scl_segment8 => p_scl_segment8
3740 ,p_scl_segment9 => p_scl_segment9
3741 ,p_scl_segment10 => p_scl_segment10
3742 ,p_scl_segment11 => p_scl_segment11
3743 ,p_scl_segment12 => p_scl_segment12
3744 ,p_scl_segment13 => p_scl_segment13
3745 ,p_scl_segment14 => p_scl_segment14
3746 ,p_scl_segment15 => p_scl_segment15
3747 ,p_scl_segment16 => p_scl_segment16
3748 ,p_scl_segment17 => p_scl_segment17
3749 ,p_scl_segment18 => p_scl_segment18
3750 ,p_scl_segment19 => p_scl_segment19
3751 ,p_scl_segment20 => p_scl_segment20
3752 ,p_scl_segment21 => p_scl_segment21
3753 ,p_scl_segment22 => p_scl_segment22
3754 ,p_scl_segment23 => p_scl_segment23
3755 ,p_scl_segment24 => p_scl_segment24
3756 ,p_scl_segment25 => p_scl_segment25
3757 ,p_scl_segment26 => p_scl_segment26
3758 ,p_scl_segment27 => p_scl_segment27
3759 ,p_scl_segment28 => p_scl_segment28
3760 ,p_scl_segment29 => p_scl_segment29
3761 ,p_scl_segment30 => p_scl_segment30
3762 ,p_concatenated_segments => p_concatenated_segments
3763 ,p_pgp_segment1 => p_segment1
3764 ,p_pgp_segment2 => p_segment2
3765 ,p_pgp_segment3 => p_segment3
3766 ,p_pgp_segment4 => p_segment4
3767 ,p_pgp_segment5 => p_segment5
3768 ,p_pgp_segment6 => p_segment6
3769 ,p_pgp_segment7 => p_segment7
3770 ,p_pgp_segment8 => p_segment8
3771 ,p_pgp_segment9 => p_segment9
3772 ,p_pgp_segment10 => p_segment10
3773 ,p_pgp_segment11 => p_segment11
3774 ,p_pgp_segment12 => p_segment12
3775 ,p_pgp_segment13 => p_segment13
3776 ,p_pgp_segment14 => p_segment14
3777 ,p_pgp_segment15 => p_segment15
3778 ,p_pgp_segment16 => p_segment16
3779 ,p_pgp_segment17 => p_segment17
3780 ,p_pgp_segment18 => p_segment18
3781 ,p_pgp_segment19 => p_segment19
3782 ,p_pgp_segment20 => p_segment20
3783 ,p_pgp_segment21 => p_segment21
3784 ,p_pgp_segment22 => p_segment22
3785 ,p_pgp_segment23 => p_segment23
3786 ,p_pgp_segment24 => p_segment24
3787 ,p_pgp_segment25 => p_segment25
3788 ,p_pgp_segment26 => p_segment26
3789 ,p_pgp_segment27 => p_segment27
3790 ,p_pgp_segment28 => p_segment28
3791 ,p_pgp_segment29 => p_segment29
3792 ,p_pgp_segment30 => p_segment30
3793 ,p_concat_segments => p_concat_segments
3794 ,p_contract_id => p_contract_id
3795 ,p_establishment_id => p_establishment_id
3796 ,p_collective_agreement_id => p_collective_agreement_id
3797 ,p_cagr_id_flex_num => p_cagr_id_flex_num
3798 ,p_cag_segment1 => p_cag_segment1
3799 ,p_cag_segment2 => p_cag_segment2
3800 ,p_cag_segment3 => p_cag_segment3
3801 ,p_cag_segment4 => p_cag_segment4
3802 ,p_cag_segment5 => p_cag_segment5
3803 ,p_cag_segment6 => p_cag_segment6
3804 ,p_cag_segment7 => p_cag_segment7
3805 ,p_cag_segment8 => p_cag_segment8
3806 ,p_cag_segment9 => p_cag_segment9
3807 ,p_cag_segment10 => p_cag_segment10
3808 ,p_cag_segment11 => p_cag_segment11
3809 ,p_cag_segment12 => p_cag_segment12
3810 ,p_cag_segment13 => p_cag_segment13
3811 ,p_cag_segment14 => p_cag_segment14
3812 ,p_cag_segment15 => p_cag_segment15
3813 ,p_cag_segment16 => p_cag_segment16
3814 ,p_cag_segment17 => p_cag_segment17
3815 ,p_cag_segment18 => p_cag_segment18
3816 ,p_cag_segment19 => p_cag_segment19
3817 ,p_cag_segment20 => p_cag_segment20
3818 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
3819 ,p_cagr_grade_def_id => l_cagr_grade_def_id
3820 ,p_cagr_concatenated_segments => p_cagr_concatenated_segments
3821 ,p_group_name => p_group_name
3822 ,p_comment_id => p_comment_id
3823 ,p_people_group_id => p_people_group_id
3824 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
3825 ,p_effective_start_date => p_effective_start_date
3826 ,p_effective_end_date => p_effective_end_date);
3827 --
3828 l_non_person_type_fields :=
3829 chk_for_non_apl_fields
3830 (p_hourly_salaried_code => p_hourly_salaried_code
3831 ,p_labour_union_member_flag => p_labour_union_member_flag
3832 ,p_project_title => p_project_title
3833 ,p_vendor_assignment_number => p_vendor_assignment_number
3834 ,p_vendor_employee_number => p_vendor_employee_number
3835 ,p_vendor_id => p_vendor_id
3836 ,p_vendor_site_id => p_vendor_site_id);
3837 --
3838 -- IF non APL fields have been entered in the mass update
3839 -- then raise a Warning message so the user knows that non-APL
3840 -- fields have been entered but will not get updated.
3841 --
3842 IF l_non_person_type_fields THEN
3843 --
3844 hr_utility.set_message(800,'HR_449906_NON_APL_FIELDS');
3845 l_message_text := 'WARNING: '||hr_utility.get_message;
3846 --
3847 Log_Error(p_type => 'WARNING',
3848 p_assignment_number => p_assignment_number,
3849 p_warning_message => l_message_text,
3850 p_already_errored => l_already_errored);
3851 END IF;
3852 -- */
3853 ELSIF l_assignment_type = 'C' THEN
3854 --
3855 update_cwk_assignment
3856 (p_validate => p_validate
3857 ,p_effective_date => p_effective_date
3858 ,p_datetrack_update_mode => p_datetrack_update_mode
3859 ,p_assignment_id => p_assignment_id
3860 ,p_object_version_number => l_object_version_number
3861 ,p_assignment_category => p_employment_category
3862 ,p_assignment_number => p_assignment_number
3863 ,p_change_reason => p_change_reason
3864 ,p_comments => p_comments
3865 ,p_default_code_comb_id => p_default_code_comb_id
3866 ,p_establishment_id => p_establishment_id
3867 ,p_frequency => p_frequency
3868 ,p_internal_address_line => p_internal_address_line
3869 ,p_labour_union_member_flag => p_labour_union_member_flag
3870 ,p_manager_flag => p_manager_flag
3871 ,p_normal_hours => p_normal_hours
3872 ,p_project_title => p_project_title
3873 ,p_set_of_books_id => p_set_of_books_id
3874 ,p_source_type => p_source_type
3875 ,p_supervisor_id => p_supervisor_id
3876 ,p_supervisor_assignment_id => p_supervisor_assignment_id
3877 ,p_time_normal_finish => p_time_normal_finish
3878 ,p_time_normal_start => p_time_normal_start
3879 ,p_title => p_title
3880 ,p_vendor_assignment_number => p_vendor_assignment_number
3881 ,p_vendor_employee_number => p_vendor_employee_number
3882 ,p_vendor_id => p_vendor_id
3883 ,p_vendor_site_id => p_vendor_site_id
3884 ,p_assignment_status_type_id => p_assignment_status_type_id
3885 ,p_concat_segments => p_concat_segments
3886 ,p_attribute_category => p_ass_attribute_category
3887 ,p_attribute1 => p_ass_attribute1
3888 ,p_attribute2 => p_ass_attribute2
3889 ,p_attribute3 => p_ass_attribute3
3890 ,p_attribute4 => p_ass_attribute4
3891 ,p_attribute5 => p_ass_attribute5
3892 ,p_attribute6 => p_ass_attribute6
3893 ,p_attribute7 => p_ass_attribute7
3894 ,p_attribute8 => p_ass_attribute8
3895 ,p_attribute9 => p_ass_attribute9
3896 ,p_attribute10 => p_ass_attribute10
3897 ,p_attribute11 => p_ass_attribute11
3898 ,p_attribute12 => p_ass_attribute12
3899 ,p_attribute13 => p_ass_attribute13
3900 ,p_attribute14 => p_ass_attribute14
3901 ,p_attribute15 => p_ass_attribute15
3902 ,p_attribute16 => p_ass_attribute16
3903 ,p_attribute17 => p_ass_attribute17
3904 ,p_attribute18 => p_ass_attribute18
3905 ,p_attribute19 => p_ass_attribute19
3906 ,p_attribute20 => p_ass_attribute20
3907 ,p_attribute21 => p_ass_attribute21
3908 ,p_attribute22 => p_ass_attribute22
3909 ,p_attribute23 => p_ass_attribute23
3910 ,p_attribute24 => p_ass_attribute24
3911 ,p_attribute25 => p_ass_attribute25
3912 ,p_attribute26 => p_ass_attribute26
3913 ,p_attribute27 => p_ass_attribute27
3914 ,p_attribute28 => p_ass_attribute28
3915 ,p_attribute29 => p_ass_attribute29
3916 ,p_attribute30 => p_ass_attribute30
3917 ,p_scl_segment1 => p_scl_segment1
3918 ,p_scl_segment2 => p_scl_segment2
3919 ,p_scl_segment3 => p_scl_segment3
3920 ,p_scl_segment4 => p_scl_segment4
3921 ,p_scl_segment5 => p_scl_segment5
3922 ,p_scl_segment6 => p_scl_segment6
3923 ,p_scl_segment7 => p_scl_segment7
3924 ,p_scl_segment8 => p_scl_segment8
3925 ,p_scl_segment9 => p_scl_segment9
3926 ,p_scl_segment10 => p_scl_segment10
3927 ,p_scl_segment11 => p_scl_segment11
3928 ,p_scl_segment12 => p_scl_segment12
3929 ,p_scl_segment13 => p_scl_segment13
3930 ,p_scl_segment14 => p_scl_segment14
3931 ,p_scl_segment15 => p_scl_segment15
3932 ,p_scl_segment16 => p_scl_segment16
3933 ,p_scl_segment17 => p_scl_segment17
3934 ,p_scl_segment18 => p_scl_segment18
3935 ,p_scl_segment19 => p_scl_segment19
3936 ,p_scl_segment20 => p_scl_segment20
3937 ,p_scl_segment21 => p_scl_segment21
3938 ,p_scl_segment22 => p_scl_segment22
3939 ,p_scl_segment23 => p_scl_segment23
3940 ,p_scl_segment24 => p_scl_segment24
3941 ,p_scl_segment25 => p_scl_segment25
3942 ,p_scl_segment26 => p_scl_segment26
3943 ,p_scl_segment27 => p_scl_segment27
3944 ,p_scl_segment28 => p_scl_segment28
3945 ,p_scl_segment29 => p_scl_segment29
3946 ,p_scl_segment30 => p_scl_segment30
3947 -- ,p_grade_id => p_grade_id
3948 ,p_position_id => p_position_id
3949 ,p_job_id => p_job_id
3950 ,p_location_id => p_location_id
3951 ,p_organization_id => p_organization_id
3952 ,p_segment1 => p_segment1
3953 ,p_segment2 => p_segment2
3954 ,p_segment3 => p_segment3
3955 ,p_segment4 => p_segment4
3956 ,p_segment5 => p_segment5
3957 ,p_segment6 => p_segment6
3958 ,p_segment7 => p_segment7
3959 ,p_segment8 => p_segment8
3960 ,p_segment9 => p_segment9
3961 ,p_segment10 => p_segment10
3962 ,p_segment11 => p_segment11
3963 ,p_segment12 => p_segment12
3964 ,p_segment13 => p_segment13
3965 ,p_segment14 => p_segment14
3966 ,p_segment15 => p_segment15
3967 ,p_segment16 => p_segment16
3968 ,p_segment17 => p_segment17
3969 ,p_segment18 => p_segment18
3970 ,p_segment19 => p_segment19
3971 ,p_segment20 => p_segment20
3972 ,p_segment21 => p_segment21
3973 ,p_segment22 => p_segment22
3974 ,p_segment23 => p_segment23
3975 ,p_segment24 => p_segment24
3976 ,p_segment25 => p_segment25
3977 ,p_segment26 => p_segment26
3978 ,p_segment27 => p_segment27
3979 ,p_segment28 => p_segment28
3980 ,p_segment29 => p_segment29
3981 ,p_segment30 => p_segment30
3982 ,p_projected_assignment_end => p_projected_assignment_end
3983 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3984 ,p_effective_start_date => l_effective_start_date
3985 ,p_effective_end_date => l_effective_end_date
3986 ,p_comment_id => l_comment_id
3987 ,p_no_managers_warning => l_no_managers_warning
3988 ,p_other_manager_warning => l_other_manager_warning
3989 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
3990 ,p_concatenated_segments => l_concatenated_segments
3991 ,p_hourly_salaried_warning => l_hourly_salaried_warning
3992 ,p_scl_concat_segments => l_scl_concat_segments
3993 ,p_people_group_name => l_people_group_name
3994 ,p_people_group_id => l_people_group_id
3995 ,p_spp_delete_warning => l_spp_delete_warning
3996 ,p_entries_changed_warning => l_entries_changed_warning
3997 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
3998 --
3999 l_non_person_type_fields :=
4000 chk_for_non_cwk_fields
4001 (p_application_id => p_application_id
4002 ,p_bargaining_unit_code => p_bargaining_unit_code
4003 ,p_cag_segment1 => p_cag_segment1
4004 ,p_cag_segment10 => p_cag_segment10
4005 ,p_cag_segment11 => p_cag_segment11
4006 ,p_cag_segment12 => p_cag_segment12
4007 ,p_cag_segment13 => p_cag_segment13
4008 ,p_cag_segment14 => p_cag_segment14
4009 ,p_cag_segment15 => p_cag_segment15
4010 ,p_cag_segment16 => p_cag_segment16
4011 ,p_cag_segment17 => p_cag_segment17
4012 ,p_cag_segment18 => p_cag_segment18
4013 ,p_cag_segment19 => p_cag_segment19
4014 ,p_cag_segment2 => p_cag_segment2
4015 ,p_cag_segment20 => p_cag_segment20
4016 ,p_cag_segment3 => p_cag_segment3
4017 ,p_cag_segment4 => p_cag_segment4
4018 ,p_cag_segment5 => p_cag_segment5
4019 ,p_cag_segment6 => p_cag_segment6
4020 ,p_cag_segment7 => p_cag_segment7
4021 ,p_cag_segment8 => p_cag_segment8
4022 ,p_cag_segment9 => p_cag_segment9
4023 ,p_cagr_id_flex_num => p_cagr_id_flex_num
4024 ,p_collective_agreement_id => p_collective_agreement_id
4025 ,p_contract_id => p_contract_id
4026 ,p_date_probation_end => p_date_probation_end
4027 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
4028 ,p_grade_id => p_grade_id
4029 ,p_hourly_salaried_code => p_hourly_salaried_code
4030 ,p_pay_basis_id => p_pay_basis_id
4031 ,p_payroll_id => p_payroll_id
4032 ,p_perf_review_period => p_perf_review_period
4033 ,p_perf_review_period_frequency => p_perf_review_period_frequency
4034 ,p_person_referred_by_id => p_person_referred_by_id
4035 ,p_probation_period => p_probation_period
4036 ,p_probation_unit => p_probation_unit
4037 ,p_recruiter_id => p_recruiter_id
4038 ,p_recruitment_activity_id => p_recruitment_activity_id
4039 ,p_sal_review_period => p_sal_review_period
4040 ,p_sal_review_period_frequency => p_sal_review_period_frequency
4041 ,p_source_organization_id => p_source_organization_id
4042 ,p_special_ceiling_step_id => p_special_ceiling_step_id
4043 ,p_vacancy_id => p_vacancy_id);
4044 --
4045 -- IF non CWK fields have been entered in the mass update
4046 -- then raise a Warning message so the user knows that non-cwk
4047 -- fields have been entered but will not get updated.
4048 --
4049 IF l_non_person_type_fields THEN
4050 --
4051 hr_utility.set_message(800,'HR_449905_NON_CWK_FIELDS');
4052 l_message_text := 'WARNING: '||hr_utility.get_message;
4053 --
4054 Log_Error(p_type => 'WARNING',
4055 p_assignment_number => p_assignment_number,
4056 p_warning_message => l_message_text,
4057 p_already_errored => l_already_errored);
4058 END IF;
4059 --
4060 -- If assignment type is not one of the above
4061 -- then raise an error.
4062 --
4063 ELSE
4064 --
4065 hr_utility.set_message(801,'HR_449903_INV_ASG_TYPE');
4066 hr_utility.raise_error;
4067 --
4068 END IF;
4069 --
4070 hr_utility.set_location('Leaving : '||l_proc, 999);
4071 --
4072 END update_assignment;
4073 --
4074 BEGIN
4075 g_txn_id := null;
4076 g_org_hierarchy_id := null;
4077 g_org_starting_node := null;
4078 g_org_hierarchy_root := null;
4079 g_pos_hierarchy_id := null;
4080 g_pos_starting_node := null;
4081 g_pos_hierarchy_root := null;
4082 END pqh_asg_wrapper;