[Home] [Help]
PACKAGE BODY: APPS.PER_ASSIGNMENTS_F_PKG
Source
1 PACKAGE BODY PER_ASSIGNMENTS_F_PKG AS
2 /* $Header: peasg01t.pkb 120.50.12020000.8 2013/02/06 07:35:03 srannama ship $ */
3 -----------------------------------------------------------------------------
4 --
5 -- **** Standard ON-* procedures of base view. *****
6 --
7 -----------------------------------------------------------------------------
8 --
9 -- Standard Insert procedure
10 --
11 --bug no 6028006 starts here
12 --g_package varchar2(21) := 'PER_ASSIGNMENTS_F_PKG.';
13 g_package varchar2(22) := 'PER_ASSIGNMENTS_F_PKG.';
14 --bug no 6028006 ends here
15 g_debug boolean; -- debug flag
16 --
17 procedure insert_row(
18 p_row_id in out nocopy varchar2,
19 p_assignment_id in out nocopy number,
20 p_effective_start_date date,
21 p_effective_end_date date,
22 p_business_group_id number,
23 p_recruiter_id number,
24 p_grade_id number,
25 p_position_id number,
26 p_job_id number,
27 p_assignment_status_type_id number,
28 p_payroll_id number,
29 p_location_id number,
30 p_person_referred_by_id number,
31 p_supervisor_id number,
32 p_special_ceiling_step_id number,
33 p_person_id number,
34 p_recruitment_activity_id number,
35 p_source_organization_id number,
36 p_organization_id number,
37 p_people_group_id number,
38 p_soft_coding_keyflex_id number,
39 p_vacancy_id number,
40 p_assignment_sequence number,
41 p_assignment_type varchar2,
42 p_primary_flag varchar2,
43 p_application_id number,
44 p_assignment_number varchar2,
45 p_change_reason varchar2,
46 p_comment_id number,
47 p_date_probation_end date,
48 p_default_code_comb_id number,
49 p_frequency varchar2,
50 p_internal_address_line varchar2,
51 p_manager_flag varchar2,
52 p_normal_hours number,
53 p_period_of_service_id number,
54 p_probation_period number,
55 p_probation_unit varchar2,
56 p_set_of_books_id number,
57 p_source_type varchar2,
58 p_time_normal_finish varchar2,
59 p_time_normal_start varchar2,
60 p_request_id number,
61 p_program_application_id number,
62 p_program_id number,
63 p_program_update_date date,
64 p_ass_attribute_category varchar2,
65 p_ass_attribute1 varchar2,
66 p_ass_attribute2 varchar2,
67 p_ass_attribute3 varchar2,
68 p_ass_attribute4 varchar2,
69 p_ass_attribute5 varchar2,
70 p_ass_attribute6 varchar2,
71 p_ass_attribute7 varchar2,
72 p_ass_attribute8 varchar2,
73 p_ass_attribute9 varchar2,
74 p_ass_attribute10 varchar2,
75 p_ass_attribute11 varchar2,
76 p_ass_attribute12 varchar2,
77 p_ass_attribute13 varchar2,
78 p_ass_attribute14 varchar2,
79 p_ass_attribute15 varchar2,
80 p_ass_attribute16 varchar2,
81 p_ass_attribute17 varchar2,
82 p_ass_attribute18 varchar2,
83 p_ass_attribute19 varchar2,
84 p_ass_attribute20 varchar2,
85 p_ass_attribute21 varchar2,
86 p_ass_attribute22 varchar2,
87 p_ass_attribute23 varchar2,
88 p_ass_attribute24 varchar2,
89 p_ass_attribute25 varchar2,
90 p_ass_attribute26 varchar2,
91 p_ass_attribute27 varchar2,
92 p_ass_attribute28 varchar2,
93 p_ass_attribute29 varchar2,
94 p_ass_attribute30 varchar2,
95 p_sal_review_period number,
96 p_sal_review_period_frequency varchar2,
97 p_perf_review_period number,
98 p_perf_review_period_frequency varchar2,
99 p_pay_basis_id number,
100 p_employment_category varchar2,
101 p_bargaining_unit_code varchar2,
102 p_labour_union_member_flag varchar2,
103 p_hourly_salaried_code varchar2,
104 p_contract_id number default null,
105 p_cagr_id_flex_num number default null,
106 p_cagr_grade_def_id number default null,
107 p_establishment_id number default null,
108 p_collective_agreement_id number default null,
109 p_notice_period number default null,
110 p_notice_period_uom varchar2 default null,
111 p_employee_category varchar2 default null,
112 p_work_at_home varchar2 default null,
113 p_job_post_source_name varchar2 default null,
114 p_placement_date_start date default null,
115 p_vendor_id number default null,
116 p_vendor_employee_number varchar2 default null,
117 p_vendor_assignment_number varchar2 default null,
118 p_assignment_category varchar2 default null,
119 p_title varchar2 default null,
120 p_project_title varchar2 default null,
121 p_grade_ladder_pgm_id number default null,
122 p_supervisor_assignment_id number default null,
123 p_vendor_site_id number default null,
124 p_po_header_id number default null,
125 p_po_line_id number default null,
126 p_projected_assignment_end date default null
127 ) is
128 l_assignment_status_id number; --discards irc_status_type out params
129 l_object_version_number number;
130 --
131 l_work_at_home varchar2(1); --FIX FOR BUG 9215178
132 l_return_code number;
133 l_return_text varchar2(240);
134 l_session_date date;
135 l_proc varchar2(200):= 'PER_ASSIGNMENTS_F_PKG.INSERT_ROW';
136 l_labour_union_member_flag varchar2(1); -- bug fix 7698212
137 --
138 /*
139 --WWBUG 2130950 Begin hrwf synchronization --tpapired
140 l_asg_rec per_all_assignments_f%rowtype;
141 cursor l_asg_cur is
142 select *
143 from per_all_assignments_f
144 where assignment_id = P_ASSIGNMENT_ID
145 and effective_start_date = P_EFFECTIVE_START_DATE
146 and effective_end_date = P_EFFECTIVE_END_DATE;
147 --WWBUG 2130950 End hrwf synchronization -tpapired
148 --
149 */
150 -- Bug 1319140 fix begin: Added US legislation code check.
151 l_legislation_code varchar2(150);
152
153 cursor ac1 is
154 select legislation_code
155 from per_business_groups
156 where business_group_id = P_BUSINESS_GROUP_ID;
157 -- Bug 1319140 fix end: Added US legislation code check.
158
159
160 cursor c1 is
161 select per_assignments_s.nextval
162 from sys.dual;
163 cursor c2 is
164 select rowid
165 from per_assignments_f
166 where assignment_id = P_ASSIGNMENT_ID
167 and effective_start_date = P_EFFECTIVE_START_DATE
168 and effective_end_date = P_EFFECTIVE_END_DATE;
169 begin
170 open c1;
171 fetch c1 into P_ASSIGNMENT_ID;
172 close c1;
173 --
174 /*-- Start change for the bug 5854568 ----*/
175 hr_utility.set_location('Entering '||l_proc,20);
176 hr_utility.set_location('In the insert row',21);
177 hr_utility.set_location('Assgid= '||P_ASSIGNMENT_ID,22);
178 hr_utility.set_location('Pid= '||p_position_id,23);
179 hr_utility.set_location('Date= '||P_EFFECTIVE_START_DATE,24);
180
181 per_asg_bus1.chk_frozen_single_pos
182 (p_assignment_id => P_ASSIGNMENT_ID
183 ,p_position_id => p_position_id
184 ,p_effective_date => P_EFFECTIVE_START_DATE
185 ,p_assignment_type => p_assignment_type -- 6356978
186 );
187 /*-- End change for the bug 5854568 ----*/
188 l_labour_union_member_flag := nvl(p_labour_union_member_flag,'N'); -- bug fix 7698212
189
190
191 --- Fix For Bug # 10037392 Starts ---
192 open ac1;
193 fetch ac1 into l_legislation_code;
194 close ac1;
195
196 if l_legislation_code='DE'
197 then
198 hr_de_extra_assignment_checks.set_labour_union_flag(p_labour_union_member_flag=>l_labour_union_member_flag);
199 end if;
200
201 l_work_at_home := nvl(p_work_at_home,'N'); --FIX FOR BUG 9215178
202
203 --- Fix For Bug # 10037392 Ends ---
204
205 begin
206 insert into per_assignments_f (
207 assignment_id,
208 effective_start_date,
209 effective_end_date,
210 business_group_id,
211 recruiter_id,
212 grade_id,
213 position_id,
214 job_id,
215 assignment_status_type_id,
216 payroll_id,
217 location_id,
218 person_referred_by_id,
219 supervisor_id,
220 special_ceiling_step_id,
221 person_id,
222 recruitment_activity_id,
223 source_organization_id,
224 organization_id,
225 people_group_id,
226 soft_coding_keyflex_id,
227 vacancy_id,
228 assignment_sequence,
229 assignment_type,
230 primary_flag,
231 application_id,
232 assignment_number,
233 change_reason,
234 comment_id,
235 date_probation_end,
236 default_code_comb_id,
237 frequency,
238 internal_address_line,
239 manager_flag,
240 normal_hours,
241 period_of_service_id,
242 probation_period,
243 probation_unit,
244 set_of_books_id,
245 source_type,
246 time_normal_finish,
247 time_normal_start,
248 request_id,
249 program_application_id,
250 program_id,
251 program_update_date,
252 ass_attribute_category,
253 ass_attribute1,
254 ass_attribute2,
255 ass_attribute3,
256 ass_attribute4,
257 ass_attribute5,
258 ass_attribute6,
259 ass_attribute7,
260 ass_attribute8,
261 ass_attribute9,
262 ass_attribute10,
263 ass_attribute11,
264 ass_attribute12,
265 ass_attribute13,
266 ass_attribute14,
267 ass_attribute15,
268 ass_attribute16,
269 ass_attribute17,
270 ass_attribute18,
271 ass_attribute19,
272 ass_attribute20,
273 ass_attribute21,
274 ass_attribute22,
275 ass_attribute23,
276 ass_attribute24,
277 ass_attribute25,
278 ass_attribute26,
279 ass_attribute27,
280 ass_attribute28,
281 ass_attribute29,
282 ass_attribute30,
283 sal_review_period,
284 sal_review_period_frequency,
285 perf_review_period,
286 perf_review_period_frequency,
287 pay_basis_id,
288 employment_category,
289 bargaining_unit_code,
290 labour_union_member_flag,
291 hourly_salaried_code,
292 contract_id,
293 cagr_id_flex_num,
294 cagr_grade_def_id,
295 establishment_id,
296 collective_agreement_id,
297 notice_period,
298 notice_period_uom,
299 work_at_home,
300 employee_category,
301 job_post_source_name,
302 period_of_placement_date_start,
303 vendor_id,
304 vendor_employee_number,
305 vendor_assignment_number,
306 assignment_category,
307 title,
308 project_title,
309 grade_ladder_pgm_id,
310 supervisor_assignment_id,
311 vendor_site_id,
312 po_header_id,
313 po_line_id,
314 projected_assignment_end)
315 values (
316 p_assignment_id,
317 p_effective_start_date,
318 p_effective_end_date,
319 p_business_group_id,
320 p_recruiter_id,
321 p_grade_id,
322 p_position_id,
323 p_job_id,
324 p_assignment_status_type_id,
325 p_payroll_id,
326 p_location_id,
327 p_person_referred_by_id,
328 p_supervisor_id,
329 p_special_ceiling_step_id,
330 p_person_id,
331 p_recruitment_activity_id,
332 p_source_organization_id,
333 p_organization_id,
334 p_people_group_id,
335 p_soft_coding_keyflex_id,
336 p_vacancy_id,
337 p_assignment_sequence,
338 p_assignment_type,
339 p_primary_flag,
340 p_application_id,
341 p_assignment_number,
342 p_change_reason,
343 p_comment_id,
344 p_date_probation_end,
345 p_default_code_comb_id,
346 p_frequency,
347 p_internal_address_line,
348 p_manager_flag,
349 p_normal_hours,
350 p_period_of_service_id,
351 p_probation_period,
352 p_probation_unit,
353 p_set_of_books_id,
354 p_source_type,
355 p_time_normal_finish,
356 p_time_normal_start,
357 p_request_id,
358 p_program_application_id,
359 p_program_id,
360 p_program_update_date,
361 p_ass_attribute_category,
362 p_ass_attribute1,
363 p_ass_attribute2,
364 p_ass_attribute3,
365 p_ass_attribute4,
366 p_ass_attribute5,
367 p_ass_attribute6,
368 p_ass_attribute7,
369 p_ass_attribute8,
370 p_ass_attribute9,
371 p_ass_attribute10,
372 p_ass_attribute11,
373 p_ass_attribute12,
374 p_ass_attribute13,
375 p_ass_attribute14,
376 p_ass_attribute15,
377 p_ass_attribute16,
378 p_ass_attribute17,
379 p_ass_attribute18,
380 p_ass_attribute19,
381 p_ass_attribute20,
382 p_ass_attribute21,
383 p_ass_attribute22,
384 p_ass_attribute23,
385 p_ass_attribute24,
386 p_ass_attribute25,
387 p_ass_attribute26,
388 p_ass_attribute27,
389 p_ass_attribute28,
390 p_ass_attribute29,
391 p_ass_attribute30,
392 p_sal_review_period,
393 p_sal_review_period_frequency,
394 p_perf_review_period,
395 p_perf_review_period_frequency,
396 p_pay_basis_id,
397 p_employment_category,
398 p_bargaining_unit_code,
399 l_labour_union_member_flag, -- bug fix 7698212.
400 p_hourly_salaried_code,
401 p_contract_id,
402 p_cagr_id_flex_num,
403 p_cagr_grade_def_id,
404 p_establishment_id,
405 p_collective_agreement_id,
406 p_notice_period,
407 p_notice_period_uom,
408 nvl(l_work_at_home,'N'), --added for bug 9850204, modified for bug 9215178
409 p_employee_category,
410 p_job_post_source_name,
411 p_placement_date_start,
412 p_vendor_id,
413 p_vendor_employee_number,
414 p_vendor_assignment_number,
415 p_assignment_category,
416 p_title,
417 p_project_title,
418 p_grade_ladder_pgm_id,
419 p_supervisor_assignment_id,
420 p_vendor_site_id,
421 p_po_header_id,
422 p_po_line_id,
423 p_projected_assignment_end
424 );
425 end;
426 --
427 hr_utility.set_location( l_proc, 30);
428
429 -- Insert a row into irc_assignment_statuses for irecruitment
430 if p_assignment_type = 'A' then
431 IRC_ASG_STATUS_API.create_irc_asg_status
432 ( p_validate => FALSE
433 , p_assignment_id => p_assignment_id
434 , p_assignment_status_type_id => p_assignment_status_type_id
435 , p_status_change_reason => p_change_reason -- Bug 2676934
436 , p_status_change_date => p_effective_start_date
437 , p_assignment_status_id => l_assignment_status_id
438 , p_object_version_number => l_object_version_number
439 );
440 end if;
441 hr_utility.set_location( l_proc, 40);
442 open c2;
443 fetch c2 into P_ROW_ID;
444 close c2;
445 -- bug 1228430 Adding a call to default tax with validation for
446 -- new assignments.
447
448 l_session_date := p_effective_start_date;
449
450 -- Bug 1319140 fix begin: Added US legislation code check.
451 hr_utility.set_location( l_proc, 50);
452 open ac1;
453 fetch ac1 into l_legislation_code;
454 if ac1%notfound then
455 close ac1;
456 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
457 fnd_message.set_token('PROCEDURE',
458 'PER_ASSIGNMENTS_F1_PKG.INSERT_ROW');
459 fnd_message.set_token('STEP', '0');
460 fnd_message.raise_error;
461 end if;
462 close ac1;
463 hr_utility.set_location( l_proc, 60);
464
465 -- Start of fix 3634447
466 -- Add the person to the appropriate security lists
467 hr_security_internal.add_to_person_list(
468 p_effective_date => p_effective_start_date,
469 p_assignment_id => p_assignment_id);
470 -- End of fix 3634447
471 hr_security.add_assignment(p_person_id, p_assignment_id); -- Bug 4018555
472 --
473 hr_utility.set_location( l_proc, 70);
474
475 IF l_legislation_code = 'US' THEN
476 pay_us_emp_dt_tax_rules.default_tax_with_validation(
477 p_assignment_id => p_assignment_id,
478 p_person_id => p_person_id,
479 p_effective_start_date => p_effective_start_date,
480 p_effective_end_date => p_effective_end_date,
481 p_session_date => l_session_date,
482 p_business_group_id => p_business_group_id,
483 p_from_form => 'Assignment',
484 p_mode => NULL,
485 p_location_id => p_location_id,
486 p_return_code => l_return_code,
487 p_return_text => l_return_text);
488 END IF;
489 -- Bug 1319140 fix end: Added US legislation code check.
490 --
491 hr_utility.set_location( l_proc, 80);
492
493 -- Start changes for bug 10180700
494
495 /*
496 ben_dt_trgr_handle.assignment
497 (p_rowid => null
498 ,p_assignment_id => p_assignment_id
499 ,p_business_group_id => p_business_group_id
500 ,p_person_id => p_person_id
501 ,p_effective_start_date => p_effective_start_date
502 ,p_effective_end_date => p_effective_end_date
503 ,p_assignment_status_type_id => p_assignment_status_type_id
504 ,p_assignment_type => p_assignment_type
505 ,p_organization_id => p_organization_id
506 ,p_primary_flag => p_primary_flag
507 ,p_change_reason => p_change_reason
508 ,p_employment_category => p_employment_category
509 ,p_frequency => p_frequency
510 ,p_grade_id => p_grade_id
511 ,p_job_id => p_job_id
512 ,p_position_id => p_position_id
513 ,p_location_id => p_location_id
514 ,p_normal_hours => p_normal_hours
515 ,p_payroll_id => p_payroll_id
516 ,p_pay_basis_id => p_pay_basis_id
517 ,p_bargaining_unit_code => p_bargaining_unit_code
518 ,p_labour_union_member_flag => p_labour_union_member_flag
519 ,p_hourly_salaried_code => p_hourly_salaried_code
520 ,p_people_group_id => p_people_group_id
521 ,p_ass_attribute1 => p_ass_attribute1
522 ,p_ass_attribute2 => p_ass_attribute2
523 ,p_ass_attribute3 => p_ass_attribute3
524 ,p_ass_attribute4 => p_ass_attribute4
525 ,p_ass_attribute5 => p_ass_attribute5
526 ,p_ass_attribute6 => p_ass_attribute6
527 ,p_ass_attribute7 => p_ass_attribute7
528 ,p_ass_attribute8 => p_ass_attribute8
529 ,p_ass_attribute9 => p_ass_attribute9
530 ,p_ass_attribute10 => p_ass_attribute10
531 ,p_ass_attribute11 => p_ass_attribute11
532 ,p_ass_attribute12 => p_ass_attribute12
533 ,p_ass_attribute13 => p_ass_attribute13
534 ,p_ass_attribute14 => p_ass_attribute14
535 ,p_ass_attribute15 => p_ass_attribute15
536 ,p_ass_attribute16 => p_ass_attribute16
537 ,p_ass_attribute17 => p_ass_attribute17
538 ,p_ass_attribute18 => p_ass_attribute18
539 ,p_ass_attribute19 => p_ass_attribute19
540 ,p_ass_attribute20 => p_ass_attribute20
541 ,p_ass_attribute21 => p_ass_attribute21
542 ,p_ass_attribute22 => p_ass_attribute22
543 ,p_ass_attribute23 => p_ass_attribute23
544 ,p_ass_attribute24 => p_ass_attribute24
545 ,p_ass_attribute25 => p_ass_attribute25
546 ,p_ass_attribute26 => p_ass_attribute26
547 ,p_ass_attribute27 => p_ass_attribute27
548 ,p_ass_attribute28 => p_ass_attribute28
549 ,p_ass_attribute29 => p_ass_attribute29
550 ,p_ass_attribute30 => p_ass_attribute30
551 );*/
552
553 ben_dt_trgr_handle.assignment
554 (p_rowid => p_row_id
555 ,p_assignment_id => NULL
556 ,p_business_group_id => NULL
557 ,p_person_id => NULL
558 ,p_effective_start_date => NULL
559 ,p_effective_end_date => NULL
560 ,p_assignment_status_type_id => NULL
561 ,p_assignment_type => NULL
562 ,p_organization_id => NULL
563 ,p_primary_flag => NULL
564 ,p_change_reason => NULL
565 ,p_employment_category => NULL
566 ,p_frequency => NULL
567 ,p_grade_id => NULL
568 ,p_job_id => NULL
569 ,p_position_id => NULL
570 ,p_location_id => NULL
571 ,p_normal_hours => NULL
572 ,p_payroll_id => NULL
573 ,p_pay_basis_id => NULL
574 ,p_bargaining_unit_code => NULL
575 ,p_labour_union_member_flag => NULL
576 ,p_hourly_salaried_code => NULL
577 ,p_people_group_id => NULL
578 ,p_ass_attribute1 => NULL
579 ,p_ass_attribute2 => NULL
580 ,p_ass_attribute3 => NULL
581 ,p_ass_attribute4 => NULL
582 ,p_ass_attribute5 => NULL
583 ,p_ass_attribute6 => NULL
584 ,p_ass_attribute7 => NULL
585 ,p_ass_attribute8 => NULL
586 ,p_ass_attribute9 => NULL
587 ,p_ass_attribute10 => NULL
588 ,p_ass_attribute11 => NULL
589 ,p_ass_attribute12 => NULL
590 ,p_ass_attribute13 => NULL
591 ,p_ass_attribute14 => NULL
592 ,p_ass_attribute15 => NULL
593 ,p_ass_attribute16 => NULL
594 ,p_ass_attribute17 => NULL
595 ,p_ass_attribute18 => NULL
596 ,p_ass_attribute19 => NULL
597 ,p_ass_attribute20 => NULL
598 ,p_ass_attribute21 => NULL
599 ,p_ass_attribute22 => NULL
600 ,p_ass_attribute23 => NULL
601 ,p_ass_attribute24 => NULL
602 ,p_ass_attribute25 => NULL
603 ,p_ass_attribute26 => NULL
604 ,p_ass_attribute27 => NULL
605 ,p_ass_attribute28 => NULL
606 ,p_ass_attribute29 => NULL
607 ,p_ass_attribute30 => NULL
608 );
609
610 -- End changes for bug 10180700
611 --
612 /*
613 --WWBUG 2130950 Begin hrwf synchronization --tpapired
614 open l_asg_cur;
615 fetch l_asg_cur into l_asg_rec;
616 close l_asg_cur;
617 per_hrwf_synch.PER_ASG_WF(
618 p_rec => l_asg_rec,
619 p_action => 'INSERT');
620 --WWBUG 2130950 End hrwf synchronization --tpapired
621 */
622 --
623 --start changes for bug 6598795
624
625 hr_assignment.update_assgn_context_value (p_business_group_id,
626 p_person_id,
627 p_assignment_id,
628 p_effective_start_date);
629
630 --end changes for bug 6598795
631 --
632 end insert_row;
633 -----------------------------------------------------------------------------
634 --
635 -- Standard delete procedure
636 --
637 procedure delete_row(p_row_id varchar2) is
638 begin
639 delete from per_assignments_f a
640 where a.rowid = chartorowid(P_ROW_ID);
641 end delete_row;
642 -----------------------------------------------------------------------------
643 --
644 -- Standard lock procedure
645 --
646 procedure lock_row(
647 p_row_id varchar2,
648 p_assignment_id number,
649 p_effective_start_date date,
650 p_effective_end_date date,
651 p_business_group_id number,
652 p_recruiter_id number,
653 p_grade_id number,
654 p_position_id number,
655 p_job_id number,
656 p_assignment_status_type_id number,
657 p_payroll_id number,
658 p_location_id number,
659 p_person_referred_by_id number,
660 p_supervisor_id number,
661 p_special_ceiling_step_id number,
662 p_person_id number,
663 p_recruitment_activity_id number,
664 p_source_organization_id number,
665 p_organization_id number,
666 p_people_group_id number,
667 p_soft_coding_keyflex_id number,
668 p_vacancy_id number,
669 p_assignment_sequence number,
670 p_assignment_type varchar2,
671 p_primary_flag varchar2,
672 p_application_id number,
673 p_assignment_number varchar2,
674 p_change_reason varchar2,
675 p_comment_id number,
676 p_date_probation_end date,
677 p_default_code_comb_id number,
678 p_frequency varchar2,
679 p_internal_address_line varchar2,
680 p_manager_flag varchar2,
681 p_normal_hours number,
682 p_period_of_service_id number,
683 p_probation_period number,
684 p_probation_unit varchar2,
685 p_set_of_books_id number,
686 p_source_type varchar2,
687 p_time_normal_finish varchar2,
688 p_time_normal_start varchar2,
689 p_request_id number,
690 p_program_application_id number,
691 p_program_id number,
692 p_program_update_date date,
693 p_ass_attribute_category varchar2,
694 p_ass_attribute1 varchar2,
695 p_ass_attribute2 varchar2,
696 p_ass_attribute3 varchar2,
697 p_ass_attribute4 varchar2,
698 p_ass_attribute5 varchar2,
699 p_ass_attribute6 varchar2,
700 p_ass_attribute7 varchar2,
701 p_ass_attribute8 varchar2,
702 p_ass_attribute9 varchar2,
703 p_ass_attribute10 varchar2,
704 p_ass_attribute11 varchar2,
705 p_ass_attribute12 varchar2,
706 p_ass_attribute13 varchar2,
707 p_ass_attribute14 varchar2,
708 p_ass_attribute15 varchar2,
709 p_ass_attribute16 varchar2,
710 p_ass_attribute17 varchar2,
711 p_ass_attribute18 varchar2,
712 p_ass_attribute19 varchar2,
713 p_ass_attribute20 varchar2,
714 p_ass_attribute21 varchar2,
715 p_ass_attribute22 varchar2,
716 p_ass_attribute23 varchar2,
717 p_ass_attribute24 varchar2,
718 p_ass_attribute25 varchar2,
719 p_ass_attribute26 varchar2,
720 p_ass_attribute27 varchar2,
721 p_ass_attribute28 varchar2,
722 p_ass_attribute29 varchar2,
723 p_ass_attribute30 varchar2,
724 p_sal_review_period number,
725 p_sal_review_period_frequency varchar2,
726 p_perf_review_period number,
727 p_perf_review_period_frequency varchar2,
728 p_pay_basis_id number,
729 p_employment_category varchar2,
730 p_bargaining_unit_code varchar2,
731 p_labour_union_member_flag varchar2,
732 p_hourly_salaried_code varchar2,
733 p_contract_id number,
734 p_cagr_id_flex_num number,
735 p_cagr_grade_def_id number,
736 p_establishment_id number,
737 p_collective_agreement_id number,
738 p_notice_period number,
739 p_notice_period_uom varchar2,
740 p_employee_category varchar2,
741 p_work_at_home varchar2,
742 p_job_post_source_name varchar2,
743 p_placement_date_start date,
744 p_vendor_id number,
745 p_vendor_employee_number varchar2,
746 p_vendor_assignment_number varchar2,
747 p_assignment_category varchar2,
748 p_title varchar2,
749 p_project_title varchar2,
750 p_grade_ladder_pgm_id number,
751 p_supervisor_assignment_id number,
752 p_vendor_site_id number,
753 p_po_header_id number,
754 p_po_line_id number,
755 p_projected_assignment_end date
756 ) is
757 --
758 cursor ASS_CUR is
759 select *
760 from per_assignments_f a
761 where a.rowid = chartorowid(P_ROW_ID)
762 FOR UPDATE OF ASSIGNMENT_ID NOWAIT;
763 --
764 ass_rec ASS_CUR%rowtype;
765 --
766 begin
767 --
768 open ASS_CUR;
769 --
770 fetch ASS_CUR into ASS_REC;
771 --
772 if ASS_CUR%notfound then
773 close ASS_CUR;
774 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
775 fnd_message.set_token('PROCEDURE',
776 'PER_ASSIGNMENTS_F_PKG.LOCK_ROW');
777 fnd_message.set_token('STEP', '1');
778 fnd_message.raise_error;
779 end if;
780 close ASS_CUR;
781 --
782 ass_rec.assignment_type := rtrim(ass_rec.assignment_type);
783 ass_rec.primary_flag := rtrim(ass_rec.primary_flag);
784 ass_rec.assignment_number := rtrim(ass_rec.assignment_number);
785 ass_rec.change_reason := rtrim(ass_rec.change_reason);
786 ass_rec.employment_category := rtrim(ass_rec.employment_category);
787 ass_rec.frequency := rtrim(ass_rec.frequency);
788 ass_rec.internal_address_line := rtrim(ass_rec.internal_address_line);
789 ass_rec.manager_flag := rtrim(ass_rec.manager_flag);
790 ass_rec.perf_review_period_frequency :=
791 rtrim(ass_rec.perf_review_period_frequency);
792 ass_rec.probation_unit := rtrim(ass_rec.probation_unit);
793 ass_rec.sal_review_period_frequency :=
794 rtrim(ass_rec.sal_review_period_frequency);
795 ass_rec.source_type := rtrim(ass_rec.source_type);
796 ass_rec.time_normal_finish := rtrim(ass_rec.time_normal_finish);
797 ass_rec.time_normal_start := rtrim(ass_rec.time_normal_start);
798 ass_rec.ass_attribute_category := rtrim(ass_rec.ass_attribute_category);
799 ass_rec.ass_attribute1 := rtrim(ass_rec.ass_attribute1);
800 ass_rec.ass_attribute2 := rtrim(ass_rec.ass_attribute2);
801 ass_rec.ass_attribute3 := rtrim(ass_rec.ass_attribute3);
802 ass_rec.ass_attribute4 := rtrim(ass_rec.ass_attribute4);
803 ass_rec.ass_attribute5 := rtrim(ass_rec.ass_attribute5);
804 ass_rec.ass_attribute6 := rtrim(ass_rec.ass_attribute6);
805 ass_rec.ass_attribute7 := rtrim(ass_rec.ass_attribute7);
806 ass_rec.ass_attribute8 := rtrim(ass_rec.ass_attribute8);
807 ass_rec.ass_attribute9 := rtrim(ass_rec.ass_attribute9);
808 ass_rec.ass_attribute10 := rtrim(ass_rec.ass_attribute10);
809 ass_rec.ass_attribute11 := rtrim(ass_rec.ass_attribute11);
810 ass_rec.ass_attribute12 := rtrim(ass_rec.ass_attribute12);
811 ass_rec.ass_attribute13 := rtrim(ass_rec.ass_attribute13);
812 ass_rec.ass_attribute14 := rtrim(ass_rec.ass_attribute14);
813 ass_rec.ass_attribute15 := rtrim(ass_rec.ass_attribute15);
814 ass_rec.ass_attribute16 := rtrim(ass_rec.ass_attribute16);
815 ass_rec.ass_attribute17 := rtrim(ass_rec.ass_attribute17);
816 ass_rec.ass_attribute18 := rtrim(ass_rec.ass_attribute18);
817 ass_rec.ass_attribute19 := rtrim(ass_rec.ass_attribute19);
818 ass_rec.ass_attribute20 := rtrim(ass_rec.ass_attribute20);
819 ass_rec.ass_attribute21 := rtrim(ass_rec.ass_attribute21);
820 ass_rec.ass_attribute22 := rtrim(ass_rec.ass_attribute22);
821 ass_rec.ass_attribute23 := rtrim(ass_rec.ass_attribute23);
822 ass_rec.ass_attribute24 := rtrim(ass_rec.ass_attribute24);
823 ass_rec.ass_attribute25 := rtrim(ass_rec.ass_attribute25);
824 ass_rec.ass_attribute26 := rtrim(ass_rec.ass_attribute26);
825 ass_rec.ass_attribute27 := rtrim(ass_rec.ass_attribute27);
826 ass_rec.ass_attribute28 := rtrim(ass_rec.ass_attribute28);
827 ass_rec.ass_attribute29 := rtrim(ass_rec.ass_attribute29);
828 ass_rec.ass_attribute30 := rtrim(ass_rec.ass_attribute30);
829 ass_rec.bargaining_unit_code := rtrim(ass_rec.bargaining_unit_code);
830 ass_rec.labour_union_member_flag := rtrim(ass_rec.labour_union_member_flag);
831 ass_rec.hourly_salaried_code:= rtrim(ass_rec.hourly_salaried_code);
832 ass_rec.contract_id := rtrim(ass_rec.contract_id);
833 ass_rec.cagr_id_flex_num := rtrim(ass_rec.cagr_id_flex_num);
834 ass_rec.cagr_grade_def_id := rtrim(ass_rec.cagr_grade_def_id);
835 ass_rec.establishment_id := rtrim(ass_rec.establishment_id);
836 ass_rec.collective_agreement_id := rtrim(ass_rec.collective_agreement_id);
837 ass_rec.notice_period := rtrim(ass_rec.notice_period);
838 ass_rec.notice_period_uom := rtrim(ass_rec.notice_period_uom);
839 ass_rec.employee_category := rtrim(ass_rec.employee_category);
840 ass_rec.work_at_home := rtrim(ass_rec.work_at_home);
841 ass_rec.job_post_source_name := rtrim(ass_rec.job_post_source_name);
842 ass_rec.period_of_placement_date_start := rtrim(ass_rec.period_of_placement_date_start);
843 ass_rec.vendor_employee_number := rtrim(ass_rec.vendor_employee_number);
844 ass_rec.vendor_assignment_number := rtrim(ass_rec.vendor_assignment_number);
845 ass_rec.assignment_category := rtrim(ass_rec.assignment_category);
846 ass_rec.title := rtrim(ass_rec.title);
847 ass_rec.project_title := rtrim(ass_rec.project_title);
848
849 --
850 if ( ((ass_rec.assignment_id = p_assignment_id)
851 or (ass_rec.assignment_id is null
852 and (p_assignment_id is null)))
853 and ((ass_rec.notice_period = p_notice_period)
854 or (ass_rec.notice_period is null
855 and (p_notice_period is null)))
856 and ((ass_rec.notice_period_uom = p_notice_period_uom)
857 or (ass_rec.notice_period_uom is null
858 and (p_notice_period_uom is null)))
859 and ((ass_rec.work_at_home = p_work_at_home)
860 or (ass_rec.work_at_home is null
861 and (p_work_at_home is null)))
862 and ((ass_rec.employee_category = p_employee_category)
863 or (ass_rec.employee_category is null
864 and (p_employee_category is null)))
865 and ((ass_rec.job_post_source_name = p_job_post_source_name)
866 or (ass_rec.job_post_source_name is null
867 and (p_job_post_source_name is null)))
868 and ((ass_rec.contract_id = p_contract_id)
869 or (ass_rec.contract_id is null
870 and (p_contract_id is null)))
871 and ((ass_rec.collective_agreement_id = p_collective_agreement_id)
872 or (ass_rec.collective_agreement_id is null
873 and (p_collective_agreement_id is null)))
874 and ((ass_rec.establishment_id = p_establishment_id)
875 or (ass_rec.establishment_id is null
876 and (p_establishment_id is null)))
877 and ((ass_rec.cagr_grade_def_id = p_cagr_grade_def_id)
878 or (ass_rec.cagr_grade_def_id is null
879 and (p_cagr_grade_def_id is null)))
880 and ((ass_rec.cagr_id_flex_num = p_cagr_id_flex_num)
881 or (ass_rec.cagr_id_flex_num is null
882 and (p_cagr_id_flex_num is null)))
883 and ((ass_rec.effective_start_date = p_effective_start_date)
884 or (ass_rec.effective_start_date is null
885 and (p_effective_start_date is null)))
886 and ((ass_rec.effective_end_date = p_effective_end_date)
887 or (ass_rec.effective_end_date is null
888 and (p_effective_end_date is null)))
889 and ((ass_rec.business_group_id = p_business_group_id)
890 or (ass_rec.business_group_id is null
891 and (p_business_group_id is null)))
892 and ((ass_rec.recruiter_id = p_recruiter_id)
893 or (ass_rec.recruiter_id is null
894 and (p_recruiter_id is null)))
895 and ((ass_rec.grade_id = p_grade_id)
896 or (ass_rec.grade_id is null
897 and (p_grade_id is null)))
898 and ((ass_rec.position_id = p_position_id)
899 or (ass_rec.position_id is null
900 and (p_position_id is null)))
901 and ((ass_rec.job_id = p_job_id)
902 or (ass_rec.job_id is null
903 and (p_job_id is null)))
904 and ((ass_rec.assignment_status_type_id = p_assignment_status_type_id)
905 or (ass_rec.assignment_status_type_id is null
906 and (p_assignment_status_type_id is null)))
907 and ((ass_rec.payroll_id = p_payroll_id)
908 or (ass_rec.payroll_id is null
909 and (p_payroll_id is null)))
910 and ((ass_rec.location_id = p_location_id)
911 or (ass_rec.location_id is null
912 and (p_location_id is null)))
913 and ((ass_rec.person_referred_by_id = p_person_referred_by_id)
914 or (ass_rec.person_referred_by_id is null
915 and (p_person_referred_by_id is null)))
916 and ((ass_rec.supervisor_id = p_supervisor_id)
917 or (ass_rec.supervisor_id is null
918 and (p_supervisor_id is null)))
919 and ((ass_rec.special_ceiling_step_id = p_special_ceiling_step_id)
920 or (ass_rec.special_ceiling_step_id is null
921 and (p_special_ceiling_step_id is null)))
922 and ((ass_rec.person_id = p_person_id)
923 or (ass_rec.person_id is null
924 and (p_person_id is null)))
925 and ((ass_rec.recruitment_activity_id = p_recruitment_activity_id)
926 or (ass_rec.recruitment_activity_id is null
927 and (p_recruitment_activity_id is null)))
928 and ((ass_rec.source_organization_id = p_source_organization_id)
929 or (ass_rec.source_organization_id is null
930 and (p_source_organization_id is null)))
931 and ((ass_rec.organization_id = p_organization_id)
932 or (ass_rec.organization_id is null
933 and (p_organization_id is null)))
934 and ((ass_rec.people_group_id = p_people_group_id)
935 or (ass_rec.people_group_id is null
936 and (p_people_group_id is null)))
937 and ((ass_rec.soft_coding_keyflex_id = p_soft_coding_keyflex_id)
938 or (ass_rec.soft_coding_keyflex_id is null
939 and (p_soft_coding_keyflex_id is null)))
940 and ((ass_rec.vacancy_id = p_vacancy_id)
941 or (ass_rec.vacancy_id is null
942 and (p_vacancy_id is null)))
943 and ((ass_rec.assignment_sequence = p_assignment_sequence)
944 or (ass_rec.assignment_sequence is null
945 and (p_assignment_sequence is null)))
946 and ((ass_rec.assignment_type = p_assignment_type)
947 or (ass_rec.assignment_type is null
948 and (p_assignment_type is null)))
949 and ((ass_rec.primary_flag = p_primary_flag)
950 or (ass_rec.primary_flag is null
951 and (p_primary_flag is null)))
952 and ((ass_rec.application_id = p_application_id)
953 or (ass_rec.application_id is null
954 and (p_application_id is null)))
955 and ((ass_rec.assignment_number = p_assignment_number)
956 or (ass_rec.assignment_number is null
957 and (p_assignment_number is null)))
958 and ((ass_rec.change_reason = p_change_reason)
959 or (ass_rec.change_reason is null
960 and (p_change_reason is null)))
961 and ((ass_rec.comment_id = p_comment_id)
962 or (ass_rec.comment_id is null
963 and (p_comment_id is null)))
964 and ((ass_rec.date_probation_end = p_date_probation_end)
965 or (ass_rec.date_probation_end is null
966 and (p_date_probation_end is null)))
967 and ((ass_rec.default_code_comb_id = p_default_code_comb_id)
968 or (ass_rec.default_code_comb_id is null
969 and (p_default_code_comb_id is null)))
970 and ((ass_rec.frequency = p_frequency)
971 or (ass_rec.frequency is null
972 and (p_frequency is null)))
973 and ((ass_rec.internal_address_line = p_internal_address_line)
974 or (ass_rec.internal_address_line is null
975 and (p_internal_address_line is null)))
976 and ((ass_rec.manager_flag = p_manager_flag)
977 or (ass_rec.manager_flag is null
978 and (p_manager_flag is null)))
979 and ((ass_rec.normal_hours = p_normal_hours)
980 or (ass_rec.normal_hours is null
981 and (p_normal_hours is null)))
982 and ((ass_rec.period_of_service_id = p_period_of_service_id)
983 or (ass_rec.period_of_service_id is null
984 and (p_period_of_service_id is null)))
985 and ((ass_rec.probation_period = p_probation_period)
986 or (ass_rec.probation_period is null
987 and (p_probation_period is null)))
988 and ((ass_rec.probation_unit = p_probation_unit)
989 or (ass_rec.probation_unit is null
990 and (p_probation_unit is null)))
991 and ((ass_rec.set_of_books_id = p_set_of_books_id)
992 or (ass_rec.set_of_books_id is null
993 and (p_set_of_books_id is null)))
994 and ((ass_rec.source_type = p_source_type)
995 or (ass_rec.source_type is null
996 and (p_source_type is null)))
997 and ((ass_rec.time_normal_finish = p_time_normal_finish)
998 or (ass_rec.time_normal_finish is null
999 and (p_time_normal_finish is null)))
1000 and ((ass_rec.time_normal_start = p_time_normal_start)
1001 or (ass_rec.time_normal_start is null
1002 and (p_time_normal_start is null)))
1003 and ((ass_rec.request_id = p_request_id)
1004 or (ass_rec.request_id is null
1005 and (p_request_id is null)))
1006 and ((ass_rec.program_application_id = p_program_application_id)
1007 or (ass_rec.program_application_id is null
1008 and (p_program_application_id is null)))
1009 and ((ass_rec.program_id = p_program_id)
1010 or (ass_rec.program_id is null
1011 and (p_program_id is null)))
1012 and ((ass_rec.program_update_date = p_program_update_date)
1013 or (ass_rec.program_update_date is null
1014 and (p_program_update_date is null)))
1015 and ((ass_rec.sal_review_period = p_sal_review_period)
1016 or (ass_rec.sal_review_period is null
1017 and (p_sal_review_period is null)))
1018 and ((ass_rec.sal_review_period_frequency = p_sal_review_period_frequency)
1019 or (ass_rec.sal_review_period_frequency is null
1020 and (p_sal_review_period_frequency is null)))
1021 and ((ass_rec.perf_review_period = p_perf_review_period)
1022 or (ass_rec.perf_review_period is null
1023 and (p_perf_review_period is null)))
1024 and ((ass_rec.perf_review_period_frequency = p_perf_review_period_frequency)
1025 or (ass_rec.perf_review_period_frequency is null
1026 and (p_perf_review_period_frequency is null)))
1027 and ((ass_rec.pay_basis_id = p_pay_basis_id)
1028 or (ass_rec.pay_basis_id is null
1029 and (p_pay_basis_id is null)))) then
1030 if ( ((ass_rec.ass_attribute_category = p_ass_attribute_category)
1031 or (ass_rec.ass_attribute_category is null
1032 and (p_ass_attribute_category is null)))
1033 and ((ass_rec.ass_attribute1 = p_ass_attribute1)
1034 or (ass_rec.ass_attribute1 is null
1035 and (p_ass_attribute1 is null)))
1036 and ((ass_rec.ass_attribute2 = p_ass_attribute2)
1037 or (ass_rec.ass_attribute2 is null
1038 and (p_ass_attribute2 is null)))
1039 and ((ass_rec.ass_attribute3 = p_ass_attribute3)
1040 or (ass_rec.ass_attribute3 is null
1041 and (p_ass_attribute3 is null)))
1042 and ((ass_rec.ass_attribute4 = p_ass_attribute4)
1043 or (ass_rec.ass_attribute4 is null
1044 and (p_ass_attribute4 is null)))
1045 and ((ass_rec.ass_attribute5 = p_ass_attribute5)
1046 or (ass_rec.ass_attribute5 is null
1047 and (p_ass_attribute5 is null)))
1048 and ((ass_rec.ass_attribute6 = p_ass_attribute6)
1049 or (ass_rec.ass_attribute6 is null
1050 and (p_ass_attribute6 is null)))
1051 and ((ass_rec.ass_attribute7 = p_ass_attribute7)
1052 or (ass_rec.ass_attribute7 is null
1053 and (p_ass_attribute7 is null)))
1054 and ((ass_rec.ass_attribute8 = p_ass_attribute8)
1055 or (ass_rec.ass_attribute8 is null
1056 and (p_ass_attribute8 is null)))
1057 and ((ass_rec.ass_attribute9 = p_ass_attribute9)
1058 or (ass_rec.ass_attribute9 is null
1059 and (p_ass_attribute9 is null)))
1060 and ((ass_rec.ass_attribute10 = p_ass_attribute10)
1061 or (ass_rec.ass_attribute10 is null
1062 and (p_ass_attribute10 is null)))
1063 and ((ass_rec.ass_attribute11 = p_ass_attribute11)
1064 or (ass_rec.ass_attribute11 is null
1065 and (p_ass_attribute11 is null)))
1066 and ((ass_rec.ass_attribute12 = p_ass_attribute12)
1067 or (ass_rec.ass_attribute12 is null
1068 and (p_ass_attribute12 is null)))
1069 and ((ass_rec.ass_attribute13 = p_ass_attribute13)
1070 or (ass_rec.ass_attribute13 is null
1071 and (p_ass_attribute13 is null)))
1072 and ((ass_rec.ass_attribute14 = p_ass_attribute14)
1073 or (ass_rec.ass_attribute14 is null
1074 and (p_ass_attribute14 is null)))
1075 and ((ass_rec.ass_attribute15 = p_ass_attribute15)
1076 or (ass_rec.ass_attribute15 is null
1077 and (p_ass_attribute15 is null)))
1078 and ((ass_rec.ass_attribute16 = p_ass_attribute16)
1079 or (ass_rec.ass_attribute16 is null
1080 and (p_ass_attribute16 is null)))
1081 and ((ass_rec.ass_attribute17 = p_ass_attribute17)
1082 or (ass_rec.ass_attribute17 is null
1083 and (p_ass_attribute17 is null)))
1084 and ((ass_rec.ass_attribute18 = p_ass_attribute18)
1085 or (ass_rec.ass_attribute18 is null
1086 and (p_ass_attribute18 is null)))
1087 and ((ass_rec.ass_attribute19 = p_ass_attribute19)
1088 or (ass_rec.ass_attribute19 is null
1089 and (p_ass_attribute19 is null)))
1090 and ((ass_rec.ass_attribute20 = p_ass_attribute20)
1091 or (ass_rec.ass_attribute20 is null
1092 and (p_ass_attribute20 is null)))
1093 and ((ass_rec.ass_attribute21 = p_ass_attribute21)
1094 or (ass_rec.ass_attribute21 is null
1095 and (p_ass_attribute21 is null)))
1096 and ((ass_rec.ass_attribute22 = p_ass_attribute22)
1097 or (ass_rec.ass_attribute22 is null
1098 and (p_ass_attribute22 is null)))
1099 and ((ass_rec.ass_attribute23 = p_ass_attribute23)
1100 or (ass_rec.ass_attribute23 is null
1101 and (p_ass_attribute23 is null)))
1102 and ((ass_rec.ass_attribute24 = p_ass_attribute24)
1103 or (ass_rec.ass_attribute24 is null
1104 and (p_ass_attribute24 is null)))
1105 and ((ass_rec.ass_attribute25 = p_ass_attribute25)
1106 or (ass_rec.ass_attribute25 is null
1107 and (p_ass_attribute25 is null)))
1108 and ((ass_rec.ass_attribute26 = p_ass_attribute26)
1109 or (ass_rec.ass_attribute26 is null
1110 and (p_ass_attribute26 is null)))
1111 and ((ass_rec.ass_attribute27 = p_ass_attribute27)
1112 or (ass_rec.ass_attribute27 is null
1113 and (p_ass_attribute27 is null)))
1114 and ((ass_rec.ass_attribute28 = p_ass_attribute28)
1115 or (ass_rec.ass_attribute28 is null
1116 and (p_ass_attribute28 is null)))
1117 and ((ass_rec.ass_attribute29 = p_ass_attribute29)
1118 or (ass_rec.ass_attribute29 is null
1119 and (p_ass_attribute29 is null)))
1120 and ((ass_rec.ass_attribute30 = p_ass_attribute30)
1121 or (ass_rec.ass_attribute30 is null
1122 and (p_ass_attribute30 is null)))
1123 and ((ass_rec.employment_category = p_employment_category)
1124 or (ass_rec.employment_category is null
1125 and (p_employment_category is null)))
1126 and ((ass_rec.bargaining_unit_code = p_bargaining_unit_code)
1127 or (ass_rec.bargaining_unit_code is null
1128 and (p_bargaining_unit_code is null)))
1129 and ((ass_rec.labour_union_member_flag = p_labour_union_member_flag)
1130 or (ass_rec.labour_union_member_flag is null
1131 and (p_labour_union_member_flag is null)))
1132 and ((ass_rec.hourly_salaried_code = p_hourly_salaried_code)
1133 or (ass_rec.hourly_salaried_code is null
1134 and (p_hourly_salaried_code is null)))
1135 and ((ass_rec.vendor_id = p_vendor_id)
1136 or (ass_rec.vendor_id is null
1137 and (p_vendor_id is null)))
1138 and ((ass_rec.vendor_site_id = p_vendor_site_id)
1139 or (ass_rec.vendor_site_id is null
1140 and (p_vendor_site_id is null)))
1141 and ((ass_rec.po_header_id = p_po_header_id)
1142 or (ass_rec.po_header_id is null
1143 and (p_po_header_id is null)))
1144 and ((ass_rec.po_line_id = p_po_line_id)
1145 or (ass_rec.po_line_id is null
1146 and (p_po_line_id is null)))
1147 and ((ass_rec.projected_assignment_end = p_projected_assignment_end)
1148 or (ass_rec.projected_assignment_end is null
1149 and (p_projected_assignment_end is null)))
1150 ) then
1151 return; -- Row successfully locked, no changes
1152 end if;
1153 end if;
1154 --
1155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1156 app_exception.raise_exception;
1157 --
1158 end lock_row;
1159 -----------------------------------------------------------------------------
1160 --
1161 -- Standard update procedure
1162 --
1163 /****************************/
1164
1165 procedure update_row(
1166 p_row_id varchar2,
1167 p_assignment_id number,
1168 p_effective_start_date date,
1169 p_effective_end_date date,
1170 p_business_group_id number,
1171 p_recruiter_id number,
1172 p_grade_id number,
1173 p_position_id number,
1174 p_job_id number,
1175 p_assignment_status_type_id number,
1176 p_payroll_id number,
1177 p_location_id number,
1178 p_person_referred_by_id number,
1179 p_supervisor_id number,
1180 p_special_ceiling_step_id number,
1181 p_person_id number,
1182 p_recruitment_activity_id number,
1183 p_source_organization_id number,
1184 p_organization_id number,
1185 p_people_group_id number,
1186 p_soft_coding_keyflex_id number,
1187 p_vacancy_id number,
1188 p_assignment_sequence number,
1189 p_assignment_type varchar2,
1190 p_primary_flag varchar2,
1191 p_application_id number,
1192 p_assignment_number varchar2,
1193 p_change_reason varchar2,
1194 p_comment_id number,
1195 p_date_probation_end date,
1196 p_default_code_comb_id number,
1197 p_frequency varchar2,
1198 p_internal_address_line varchar2,
1199 p_manager_flag varchar2,
1200 p_normal_hours number,
1201 p_period_of_service_id number,
1202 p_probation_period number,
1203 p_probation_unit varchar2,
1204 p_set_of_books_id number,
1205 p_source_type varchar2,
1206 p_time_normal_finish varchar2,
1207 p_time_normal_start varchar2,
1208 p_request_id number,
1209 p_program_application_id number,
1210 p_program_id number,
1211 p_program_update_date date,
1212 p_ass_attribute_category varchar2,
1213 p_ass_attribute1 varchar2,
1214 p_ass_attribute2 varchar2,
1215 p_ass_attribute3 varchar2,
1216 p_ass_attribute4 varchar2,
1217 p_ass_attribute5 varchar2,
1218 p_ass_attribute6 varchar2,
1219 p_ass_attribute7 varchar2,
1220 p_ass_attribute8 varchar2,
1221 p_ass_attribute9 varchar2,
1222 p_ass_attribute10 varchar2,
1223 p_ass_attribute11 varchar2,
1224 p_ass_attribute12 varchar2,
1225 p_ass_attribute13 varchar2,
1226 p_ass_attribute14 varchar2,
1227 p_ass_attribute15 varchar2,
1228 p_ass_attribute16 varchar2,
1229 p_ass_attribute17 varchar2,
1230 p_ass_attribute18 varchar2,
1231 p_ass_attribute19 varchar2,
1232 p_ass_attribute20 varchar2,
1233 p_ass_attribute21 varchar2,
1234 p_ass_attribute22 varchar2,
1235 p_ass_attribute23 varchar2,
1236 p_ass_attribute24 varchar2,
1237 p_ass_attribute25 varchar2,
1238 p_ass_attribute26 varchar2,
1239 p_ass_attribute27 varchar2,
1240 p_ass_attribute28 varchar2,
1241 p_ass_attribute29 varchar2,
1242 p_ass_attribute30 varchar2,
1243 p_sal_review_period number,
1244 p_sal_review_period_frequency varchar2,
1245 p_perf_review_period number,
1246 p_perf_review_period_frequency varchar2,
1247 p_pay_basis_id number,
1248 p_employment_category varchar2,
1249 p_dt_update_mode varchar2,
1250 p_session_date date,
1251 p_bargaining_unit_code varchar2,
1252 p_labour_union_member_flag varchar2,
1253 p_hourly_salaried_code varchar2,
1254 p_contract_id number default null,
1255 p_cagr_id_flex_num number default null,
1256 p_cagr_grade_def_id number default null,
1257 p_establishment_id number default null,
1258 p_collective_agreement_id number default null,
1259 p_notice_period number default null,
1260 p_notice_period_uom varchar2 default null,
1261 p_employee_category varchar2 default null,
1262 p_work_at_home varchar2 default null,
1263 p_job_post_source_name varchar2 default null,
1264 p_placement_date_start date default null,
1265 p_vendor_id number default null,
1266 p_vendor_employee_number varchar2 default null,
1267 p_vendor_assignment_number varchar2 default null,
1268 p_assignment_category varchar2 default null,
1269 p_title varchar2 default null,
1270 p_project_title varchar2 default null,
1271 p_grade_ladder_pgm_id number default null,
1272 p_supervisor_assignment_id number default null,
1273 p_vendor_site_id number default null,
1274 p_po_header_id number default null,
1275 p_po_line_id number default null,
1276 p_projected_assignment_end date default null
1277 ) is
1278 l_assignment_status_id number; --discards out params from irc_asg_st
1279 l_asg_status_ovn number; --discards out params from irc_asg_st
1280 l_previous_asg_status number;
1281 l_organization_id number;
1282 l_legislation_code varchar2(150);
1283 l_return_code number;
1284 l_return_text varchar2(240);
1285 l_location_id number;
1286 l_loc_id number;
1287 l_location_id_changed number := 0;
1288 l_dt_update_mode varchar2(240);
1289 l_percent_time number;
1290 dummy date;
1291 --
1292 l_proc varchar2(10) := 'insert_row';
1293 --
1294
1295 --
1296 /*
1297 --WWBUG 2130950 Begin hrwf synchronization --tpapired
1298 l_asg_rec per_all_assignments_f%rowtype;
1299 cursor l_asg_cur is
1300 select *
1301 from per_all_assignments_f
1302 where assignment_id = P_ASSIGNMENT_ID
1303 and effective_start_date = P_EFFECTIVE_START_DATE
1304 and effective_end_date = P_EFFECTIVE_END_DATE;
1305 --WWBUG 2130950 End hrwf synchronization -tpapired
1306 */
1307 --
1308 -- DK 99/05/03
1309 -- Bug 883263 (rdbms 883275)
1310 -- The workaround gives a simpler and more efficient query
1311 -- Replace the form's view (per_assignments_v) with the secure view.
1312 --
1313
1314 cursor perav1(c_row_id rowid) is
1315 select pav.organization_id,
1316 pbg.legislation_code,
1317 pav.location_id
1318 from per_assignments_f pav,
1319 per_business_groups pbg
1320 where c_row_id = pav.rowid
1321 and pav.business_group_id = pbg.business_group_id;
1322 --
1323
1324 cursor csr_chk_loc_change is
1325 select paf.location_id
1326 from PER_ASSIGNMENTS_F paf
1327 where paf.assignment_id = p_assignment_id
1328 and p_session_date between paf.effective_start_date
1329 and paf.effective_end_date;
1330
1331 /* Need to check that US payroll is installed */
1332 /*
1333 CURSOR get_install_info IS
1334 SELECT status
1335 FROM fnd_product_installations
1336 WHERE application_id = 801
1337 AND p_primary_flag = 'Y';
1338 */
1339
1340 cursor c_pay_proposals is
1341 select min(change_date)
1342 from per_pay_proposals
1343 where assignment_id = p_assignment_id;
1344
1345 l_count_get_assign number default 0;
1346
1347 /* Cursor csr_get_assign added for bug#8232830 */
1348
1349 cursor csr_get_assign(csr_person_id number) is
1350 select assignment_id
1351 from per_all_assignments_f
1352 where person_id=csr_person_id
1353 and business_group_id=p_business_group_id
1354 and trunc(sysdate) between effective_start_date and effective_end_date
1355 and assignment_type not in ('B','O');
1356
1357 /*End of Cursor csr_get_assign added for bug#8232830 */
1358
1359 /* Cursor csr_get_assign1 added for bug#12972634 */
1360
1361 cursor csr_get_assign1(csr_person_id number) is
1362 select assignment_id
1363 from per_all_assignments_f
1364 where person_id=csr_person_id
1365 and business_group_id=p_business_group_id
1366 and p_effective_start_date between effective_start_date and effective_end_date
1367 and assignment_type not in ('B','O');
1368
1369 /*End of Cursor csr_get_assign1 added for bug#12972634 */
1370
1371 --start code for bug 6961562
1372 l_installed boolean;
1373 l_po_installed VARCHAR2(1);
1374 l_industry VARCHAR2(1);
1375 l_vendor_id number default null;
1376 l_vendor_site_id number default null;
1377
1378 cursor po_cwk is
1379 select vendor_id,vendor_site_id from
1380 per_all_assignments_f paf
1381 where paf.assignment_id = p_assignment_id
1382 and nvl(p_session_date,sysdate) between paf.effective_start_date
1383 and paf.effective_end_date;
1384 --end code for bug 6961562
1385 --
1386 --
1387 l_status VARCHAR2(50);
1388 l_change_date DATE;
1389 /* variable added for bug#8232830 */
1390 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1391 --
1392
1393 --- Fix For Bug # 8860141 Starts ---
1394 cursor csr_defaulting_date(p_assignment_id number) is
1395 select min(effective_start_date)
1396 from pay_us_emp_fed_tax_rules_f
1397 where assignment_id = p_assignment_id;
1398
1399 cursor csr_defaultpayrollremoved(p_assignment_id number,
1400 p_effective_date date) is
1401 select null
1402 from per_all_assignments_f paa,
1403 pay_element_entries_f pee,
1404 pay_element_links_f pel,
1405 pay_element_types_f pet
1406 where paa.assignment_id=p_assignment_id
1407 and paa.payroll_id is null
1408 and p_effective_date between paa.effective_start_date
1409 and paa.effective_end_date
1410 and pee.assignment_id=paa.assignment_id
1411 and p_effective_date between pee.effective_start_date
1412 and pee.effective_end_date
1413 and pee.element_link_id=pel.element_link_id
1414 and pel.link_to_all_payrolls_flag = 'Y'
1415 and p_effective_date between pel.effective_start_date
1416 and pel.effective_end_date
1417 and pel.element_type_id=pet.element_type_id
1418 and pet.element_name in ('VERTEX','Workers Compensation');
1419
1420 l_default_date DATE;
1421 l_temp_char VARCHAR2(10);
1422 --- Fix For Bug # 8860141 Ends ---
1423
1424 -- Start changes for bug 10180700
1425 --
1426 cursor ASS_CUR is
1427 select *
1428 from per_assignments_f a
1429 where a.rowid = chartorowid(P_ROW_ID);
1430 --
1431 ass_rec ASS_CUR%rowtype;
1432
1433 -- End changes for bug 10180700
1434
1435 begin
1436
1437 -- Start changes for bug 10180700
1438
1439 open ASS_CUR;
1440 fetch ASS_CUR into ass_rec;
1441 --
1442 if ASS_CUR%notfound then
1443 close ASS_CUR;
1444 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1445 fnd_message.set_token('PROCEDURE','PER_ASSIGNMENTS_F_PKG.LOCK_ROW');
1446 fnd_message.set_token('STEP', '1');
1447 fnd_message.raise_error;
1448 end if;
1449 --
1450 close ASS_CUR;
1451
1452 -- End changes for bug 10180700
1453
1454 --
1455 -- #294004 If the organization_id has been changed away from the default
1456 --business_group, an update to ins_per_list should be invoked, after the update
1457 --to per_assignments_f. This is because all secure users will be able to see
1458 --the person when assigned to the default business group and we want to
1459 --restrict access immediately the organization is entered, rather than waiting
1460 --for the next run of listgen.
1461 --
1462 hr_utility.set_location('per_assignments_f_pkg.update_row',1);
1463 open csr_chk_loc_change;
1464 fetch csr_chk_loc_change into l_loc_id;
1465
1466 if csr_chk_loc_change%NOTFOUND then
1467 close csr_chk_loc_change;
1468 fnd_message.set_name(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1469 fnd_message.set_token('PROCEDURE',
1470 'per_assignments_f_pkg.update_row');
1471 fnd_message.set_token('STEP','1');
1472 fnd_message.raise_error;
1473 else
1474 if l_loc_id <> p_location_id then
1475 l_location_id_changed := 1;
1476 close csr_chk_loc_change;
1477 end if;
1478 end if;
1479
1480 /**
1481
1482 -- Bug 2365872
1483 -- If records exists in per_pay_proposals for the assignment
1484 -- then salary basis cannot be nulled.
1485 --
1486 if p_pay_basis_id is null then
1487 open c_pay_proposals;
1488
1489 hr_utility.set_location('per_assignments_f_pkg.update_row',10);
1490 fetch c_pay_proposals into l_change_date;
1491 if c_pay_proposals%FOUND then
1492 if l_change_date < p_session_date then
1493 fnd_message.set_name('PER','HR_289767_SALARY_BASIS_IS_NULL');
1494 fnd_message.raise_error;
1495 end if;
1496 hr_utility.set_location('per_assignments_f_pkg.update_row',20);
1497 end if;
1498 hr_utility.set_location('per_assignments_f_pkg.update_row',30);
1499 end if;
1500
1501 -- End Bug 2365872
1502 **/
1503
1504 /************************************/
1505 hr_utility.set_location(g_package || l_proc, 20);
1506
1507 open perav1(chartorowid(p_row_id));
1508 fetch perav1 into l_organization_id, l_legislation_code,
1509 l_location_id;
1510 if perav1%notfound then
1511 close perav1;
1512 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1513 fnd_message.set_token('PROCEDURE',
1514 'PER_ASSIGNMENTS_F1_PKG.UPDATE_ROW');
1515 fnd_message.set_token('STEP', '1');
1516 fnd_message.raise_error;
1517 end if;
1518 close perav1;
1519
1520 -- start code for bug 6961562
1521 -- PO
1522 l_installed := fnd_installation.get(appl_id => 210
1523 ,dep_appl_id => 210
1524 ,status => l_po_installed
1525 ,industry => l_industry);
1526
1527 if l_po_installed <> 'N' then
1528 open po_cwk;
1529 fetch po_cwk into l_vendor_id,l_vendor_site_id;
1530 if po_cwk%found then
1531 if (l_vendor_id <> p_vendor_id)
1532 or (l_vendor_site_id <> p_vendor_site_id) then
1533 PO_HR_INTERFACE_PVT.is_Supplier_Updatable( p_assignment_id => p_assignment_id,
1534 p_effective_date => p_session_date );
1535 end if;
1536 end if;
1537 close po_cwk;
1538 end if;
1539 -- end code for bug 6961562
1540
1541 --
1542 -- changed p_rowid => null to p_rowid => p_row_id
1543 --
1544
1545 hr_utility.set_location(g_package || l_proc, 30);
1546
1547 -- Start changes for bug 10180700
1548 /*
1549 ben_dt_trgr_handle.assignment
1550 (p_rowid => p_row_id
1551 ,p_assignment_id => p_assignment_id
1552 ,p_business_group_id => p_business_group_id
1553 ,p_person_id => p_person_id
1554 ,p_effective_start_date => p_effective_start_date
1555 ,p_effective_end_date => p_effective_end_date
1556 ,p_assignment_status_type_id => p_assignment_status_type_id
1557 ,p_assignment_type => p_assignment_type
1558 ,p_organization_id => p_organization_id
1559 ,p_primary_flag => p_primary_flag
1560 ,p_change_reason => p_change_reason
1561 ,p_employment_category => p_employment_category
1562 ,p_frequency => p_frequency
1563 ,p_grade_id => p_grade_id
1564 ,p_job_id => p_job_id
1565 ,p_position_id => p_position_id
1566 ,p_location_id => p_location_id
1567 ,p_normal_hours => p_normal_hours
1568 ,p_payroll_id => p_payroll_id
1569 ,p_pay_basis_id => p_pay_basis_id
1570 ,p_bargaining_unit_code => p_bargaining_unit_code
1571 ,p_labour_union_member_flag => p_labour_union_member_flag
1572 ,p_hourly_salaried_code => p_hourly_salaried_code
1573 ,p_people_group_id => p_people_group_id
1574 ,p_ass_attribute1 => p_ass_attribute1
1575 ,p_ass_attribute2 => p_ass_attribute2
1576 ,p_ass_attribute3 => p_ass_attribute3
1577 ,p_ass_attribute4 => p_ass_attribute4
1578 ,p_ass_attribute5 => p_ass_attribute5
1579 ,p_ass_attribute6 => p_ass_attribute6
1580 ,p_ass_attribute7 => p_ass_attribute7
1581 ,p_ass_attribute8 => p_ass_attribute8
1582 ,p_ass_attribute9 => p_ass_attribute9
1583 ,p_ass_attribute10 => p_ass_attribute10
1584 ,p_ass_attribute11 => p_ass_attribute11
1585 ,p_ass_attribute12 => p_ass_attribute12
1586 ,p_ass_attribute13 => p_ass_attribute13
1587 ,p_ass_attribute14 => p_ass_attribute14
1588 ,p_ass_attribute15 => p_ass_attribute15
1589 ,p_ass_attribute16 => p_ass_attribute16
1590 ,p_ass_attribute17 => p_ass_attribute17
1591 ,p_ass_attribute18 => p_ass_attribute18
1592 ,p_ass_attribute19 => p_ass_attribute19
1593 ,p_ass_attribute20 => p_ass_attribute20
1594 ,p_ass_attribute21 => p_ass_attribute21
1595 ,p_ass_attribute22 => p_ass_attribute22
1596 ,p_ass_attribute23 => p_ass_attribute23
1597 ,p_ass_attribute24 => p_ass_attribute24
1598 ,p_ass_attribute25 => p_ass_attribute25
1599 ,p_ass_attribute26 => p_ass_attribute26
1600 ,p_ass_attribute27 => p_ass_attribute27
1601 ,p_ass_attribute28 => p_ass_attribute28
1602 ,p_ass_attribute29 => p_ass_attribute29
1603 ,p_ass_attribute30 => p_ass_attribute30
1604 );
1605 */
1606 -- Start changes for bug 10180700
1607 --
1608 hr_utility.set_location(g_package || l_proc, 40);
1609 -- Retrieve Previous assignment_status_id
1610 select assignment_status_type_id into l_previous_asg_status
1611 from per_assignments_f
1612 where rowid = chartorowid(P_ROW_ID);
1613
1614 hr_utility.set_location(g_package || l_proc, 45);
1615
1616 if l_previous_asg_status <> p_assignment_status_type_id
1617 and p_assignment_type = 'A' then
1618 IRC_ASG_STATUS_API.create_irc_asg_status
1619 (p_assignment_id => p_assignment_id
1620 , p_assignment_status_type_id => p_assignment_status_type_id
1621 , p_status_change_date => p_effective_start_date
1622 , p_status_change_reason => p_change_reason -- Bug 2676934
1623 , p_assignment_status_id => l_assignment_status_id
1624 , p_object_version_number => l_asg_status_ovn);
1625 end if;
1626
1627 hr_utility.set_location(g_package || l_proc, 50);
1628 update per_assignments_f a
1629 set a.assignment_id = P_ASSIGNMENT_ID,
1630 a.effective_start_date = P_EFFECTIVE_START_DATE,
1631 a.effective_end_date = P_EFFECTIVE_END_DATE,
1632 a.business_group_id = P_BUSINESS_GROUP_ID,
1633 a.recruiter_id = P_RECRUITER_ID,
1634 a.grade_id = P_GRADE_ID,
1635 a.position_id = P_POSITION_ID,
1636 a.job_id = P_JOB_ID,
1637 a.assignment_status_type_id = P_ASSIGNMENT_STATUS_TYPE_ID,
1638 a.payroll_id = P_PAYROLL_ID,
1639 a.location_id = P_LOCATION_ID,
1640 a.person_referred_by_id = P_PERSON_REFERRED_BY_ID,
1641 a.supervisor_id = P_SUPERVISOR_ID,
1642 a.special_ceiling_step_id = P_SPECIAL_CEILING_STEP_ID,
1643 a.person_id = P_PERSON_ID,
1644 a.recruitment_activity_id = P_RECRUITMENT_ACTIVITY_ID,
1645 a.source_organization_id = P_SOURCE_ORGANIZATION_ID,
1646 a.organization_id = P_ORGANIZATION_ID,
1647 a.people_group_id = P_PEOPLE_GROUP_ID,
1648 a.soft_coding_keyflex_id = P_SOFT_CODING_KEYFLEX_ID,
1649 a.vacancy_id = P_VACANCY_ID,
1650 a.assignment_sequence = P_ASSIGNMENT_SEQUENCE,
1651 a.assignment_type = P_ASSIGNMENT_TYPE,
1652 -- a.primary_flag = P_PRIMARY_FLAG, --commented for bug 9644377
1653 a.application_id = P_APPLICATION_ID,
1654 a.assignment_number = P_ASSIGNMENT_NUMBER,
1655 a.change_reason = P_CHANGE_REASON,
1656 a.comment_id = P_COMMENT_ID,
1657 a.date_probation_end = P_DATE_PROBATION_END,
1658 a.default_code_comb_id = P_DEFAULT_CODE_COMB_ID,
1659 a.frequency = P_FREQUENCY,
1660 a.internal_address_line = P_INTERNAL_ADDRESS_LINE,
1661 a.manager_flag = P_MANAGER_FLAG,
1662 a.normal_hours = P_NORMAL_HOURS,
1663 a.period_of_service_id = P_PERIOD_OF_SERVICE_ID,
1664 a.probation_period = P_PROBATION_PERIOD,
1665 a.probation_unit = P_PROBATION_UNIT,
1666 a.set_of_books_id = P_SET_OF_BOOKS_ID,
1667 a.source_type = P_SOURCE_TYPE,
1668 a.time_normal_finish = P_TIME_NORMAL_FINISH,
1669 a.time_normal_start = P_TIME_NORMAL_START,
1670 a.request_id = P_REQUEST_ID,
1671 a.program_application_id = P_PROGRAM_APPLICATION_ID,
1672 a.program_id = P_PROGRAM_ID,
1673 a.program_update_date = P_PROGRAM_UPDATE_DATE,
1674 a.ass_attribute_category = P_ASS_ATTRIBUTE_CATEGORY,
1675 a.ass_attribute1 = P_ASS_ATTRIBUTE1,
1676 a.ass_attribute2 = P_ASS_ATTRIBUTE2,
1677 a.ass_attribute3 = P_ASS_ATTRIBUTE3,
1678 a.ass_attribute4 = P_ASS_ATTRIBUTE4,
1679 a.ass_attribute5 = P_ASS_ATTRIBUTE5,
1680 a.ass_attribute6 = P_ASS_ATTRIBUTE6,
1681 a.ass_attribute7 = P_ASS_ATTRIBUTE7,
1682 a.ass_attribute8 = P_ASS_ATTRIBUTE8,
1683 a.ass_attribute9 = P_ASS_ATTRIBUTE9,
1684 a.ass_attribute10 = P_ASS_ATTRIBUTE10,
1685 a.ass_attribute11 = P_ASS_ATTRIBUTE11,
1686 a.ass_attribute12 = P_ASS_ATTRIBUTE12,
1687 a.ass_attribute13 = P_ASS_ATTRIBUTE13,
1688 a.ass_attribute14 = P_ASS_ATTRIBUTE14,
1689 a.ass_attribute15 = P_ASS_ATTRIBUTE15,
1690 a.ass_attribute16 = P_ASS_ATTRIBUTE16,
1691 a.ass_attribute17 = P_ASS_ATTRIBUTE17,
1692 a.ass_attribute18 = P_ASS_ATTRIBUTE18,
1693 a.ass_attribute19 = P_ASS_ATTRIBUTE19,
1694 a.ass_attribute20 = P_ASS_ATTRIBUTE20,
1695 a.ass_attribute21 = P_ASS_ATTRIBUTE21,
1696 a.ass_attribute22 = P_ASS_ATTRIBUTE22,
1697 a.ass_attribute23 = P_ASS_ATTRIBUTE23,
1698 a.ass_attribute24 = P_ASS_ATTRIBUTE24,
1699 a.ass_attribute25 = P_ASS_ATTRIBUTE25,
1700 a.ass_attribute26 = P_ASS_ATTRIBUTE26,
1701 a.ass_attribute27 = P_ASS_ATTRIBUTE27,
1702 a.ass_attribute28 = P_ASS_ATTRIBUTE28,
1703 a.ass_attribute29 = P_ASS_ATTRIBUTE29,
1704 a.ass_attribute30 = P_ASS_ATTRIBUTE30,
1705 a.sal_review_period = P_SAL_REVIEW_PERIOD,
1706 a.sal_review_period_frequency = P_SAL_REVIEW_PERIOD_FREQUENCY,
1707 a.perf_review_period = P_PERF_REVIEW_PERIOD,
1708 a.perf_review_period_frequency = P_PERF_REVIEW_PERIOD_FREQUENCY,
1709 a.pay_basis_id = P_PAY_BASIS_ID,
1710 a.employment_category = P_EMPLOYMENT_CATEGORY,
1711 a.bargaining_unit_code = P_BARGAINING_UNIT_CODE,
1712 a.labour_union_member_flag = P_LABOUR_UNION_MEMBER_FLAG,
1713 a.hourly_salaried_code = P_HOURLY_SALARIED_CODE,
1714 a.collective_agreement_id = P_COLLECTIVE_AGREEMENT_ID,
1715 a.cagr_grade_def_id = P_CAGR_GRADE_DEF_ID,
1716 a.establishment_id = P_ESTABLISHMENT_ID,
1717 a.contract_id = P_CONTRACT_ID,
1718 a.cagr_id_flex_num = P_CAGR_ID_FLEX_NUM,
1719 a.notice_period = P_NOTICE_PERIOD,
1720 a.notice_period_uom = P_NOTICE_PERIOD_UOM,
1721 a.work_at_home = P_WORK_AT_HOME,
1722 a.employee_category = P_EMPLOYEE_CATEGORY,
1723 a.job_post_source_name = P_JOB_POST_SOURCE_NAME,
1724 a.period_of_placement_date_start = p_placement_date_start,
1725 a.vendor_id = p_vendor_id,
1726 a.vendor_employee_number = p_vendor_employee_number,
1727 a.vendor_assignment_number = p_vendor_assignment_number,
1728 a.assignment_category = p_assignment_category,
1729 a.title = p_title,
1730 a.project_title = p_project_title,
1731 a.grade_ladder_pgm_id = p_grade_ladder_pgm_id,
1732 a.supervisor_assignment_id = p_supervisor_assignment_id,
1733 a.vendor_site_id = p_vendor_site_id,
1734 a.po_header_id = p_po_header_id,
1735 a.po_line_id = p_po_line_id,
1736 a.projected_assignment_end = p_projected_assignment_end
1737 where a.rowid = chartorowid(P_ROW_ID);
1738 --
1739 -- Start of fix 3815024
1740 hr_utility.set_location(g_package || l_proc, 55);
1741
1742 -- Start changes for bug 10180700
1743 ben_dt_trgr_handle.assignment
1744 (p_rowid => p_row_id
1745 ,p_assignment_id => ass_rec.assignment_id
1746 ,p_business_group_id => ass_rec.business_group_id
1747 ,p_person_id => ass_rec.person_id
1748 ,p_effective_start_date => ass_rec.effective_start_date
1749 ,p_effective_end_date => ass_rec.effective_end_date
1750 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
1751 ,p_assignment_type => ass_rec.assignment_type
1752 ,p_organization_id => ass_rec.organization_id
1753 ,p_primary_flag => ass_rec.primary_flag
1754 ,p_change_reason => ass_rec.change_reason
1755 ,p_employment_category => ass_rec.employment_category
1756 ,p_frequency => ass_rec.frequency
1757 ,p_grade_id => ass_rec.grade_id
1758 ,p_job_id => ass_rec.job_id
1759 ,p_position_id => ass_rec.position_id
1760 ,p_location_id => ass_rec.location_id
1761 ,p_normal_hours => ass_rec.normal_hours
1762 ,p_payroll_id => ass_rec.payroll_id
1763 ,p_pay_basis_id => ass_rec.pay_basis_id
1764 ,p_bargaining_unit_code => ass_rec.bargaining_unit_code
1765 ,p_labour_union_member_flag => ass_rec.labour_union_member_flag
1766 ,p_hourly_salaried_code => ass_rec.hourly_salaried_code
1767 ,p_people_group_id => ass_rec.people_group_id
1768 ,p_ass_attribute1 => ass_rec.ass_attribute1
1769 ,p_ass_attribute2 => ass_rec.ass_attribute2
1770 ,p_ass_attribute3 => ass_rec.ass_attribute3
1771 ,p_ass_attribute4 => ass_rec.ass_attribute4
1772 ,p_ass_attribute5 => ass_rec.ass_attribute5
1773 ,p_ass_attribute6 => ass_rec.ass_attribute6
1774 ,p_ass_attribute7 => ass_rec.ass_attribute7
1775 ,p_ass_attribute8 => ass_rec.ass_attribute8
1776 ,p_ass_attribute9 => ass_rec.ass_attribute9
1777 ,p_ass_attribute10 => ass_rec.ass_attribute10
1778 ,p_ass_attribute11 => ass_rec.ass_attribute11
1779 ,p_ass_attribute12 => ass_rec.ass_attribute12
1780 ,p_ass_attribute13 => ass_rec.ass_attribute13
1781 ,p_ass_attribute14 => ass_rec.ass_attribute14
1782 ,p_ass_attribute15 => ass_rec.ass_attribute15
1783 ,p_ass_attribute16 => ass_rec.ass_attribute16
1784 ,p_ass_attribute17 => ass_rec.ass_attribute17
1785 ,p_ass_attribute18 => ass_rec.ass_attribute18
1786 ,p_ass_attribute19 => ass_rec.ass_attribute19
1787 ,p_ass_attribute20 => ass_rec.ass_attribute20
1788 ,p_ass_attribute21 => ass_rec.ass_attribute21
1789 ,p_ass_attribute22 => ass_rec.ass_attribute22
1790 ,p_ass_attribute23 => ass_rec.ass_attribute23
1791 ,p_ass_attribute24 => ass_rec.ass_attribute24
1792 ,p_ass_attribute25 => ass_rec.ass_attribute25
1793 ,p_ass_attribute26 => ass_rec.ass_attribute26
1794 ,p_ass_attribute27 => ass_rec.ass_attribute27
1795 ,p_ass_attribute28 => ass_rec.ass_attribute28
1796 ,p_ass_attribute29 => ass_rec.ass_attribute29
1797 ,p_ass_attribute30 => ass_rec.ass_attribute30
1798 );
1799 -- End changes for bug 10180700
1800
1801 /*if l_organization_id = p_business_group_id and
1802 p_organization_id <> p_business_group_id then*/ -- Commented for bug 6167879
1803
1804 --
1805 -- if (p_effective_start_date <= trunc(sysdate)) then -- Added for bug#10078890
1806
1807 /* -- commented the code for fixing Bug#13496901
1808 select count(*) into l_count_get_assign
1809 from per_all_assignments_f
1810 where person_id= p_person_id
1811 and business_group_id=p_business_group_id
1812 and trunc(sysdate) between effective_start_date and effective_end_date
1813 and assignment_type not in ('B','O');
1814
1815
1816 if (l_count_get_assign > 0 ) then
1817 */
1818 hr_security_internal.clear_from_person_list(
1819 p_person_id => p_person_id);
1820 --
1821 /*end if;*/ -- Commented for 6167879
1822 --
1823 hr_utility.set_location(g_package || l_proc, 70);
1824
1825 -- Start of fix 8232830
1826 -- Start of fix 12972634
1827 if p_effective_start_date < trunc(sysdate) then
1828 hr_utility.set_location('p_eff_strt_date:'||p_effective_start_date,66);
1829 hr_utility.set_location('sysdate:'||trunc(sysdate),67);
1830 open csr_get_assign(p_person_id);
1831 loop
1832 fetch csr_get_assign into l_assignment_id;
1833 exit when csr_get_assign%NOTFOUND;
1834 hr_security_internal.add_to_person_list( p_effective_date => trunc(sysdate), -- for bug14592133
1835 p_assignment_id => l_assignment_id);
1836 end loop;
1837 else
1838 hr_utility.set_location('p_eff_strt_date:'||p_effective_start_date,68);
1839 open csr_get_assign1(p_person_id);
1840 loop
1841 fetch csr_get_assign1 into l_assignment_id;
1842 exit when csr_get_assign1%NOTFOUND;
1843 hr_security_internal.add_to_person_list(
1844 p_effective_date => p_effective_start_date,
1845 p_assignment_id => l_assignment_id);
1846 end loop;
1847 end if;
1848 --end if; -- fix of bug#13496901
1849 -- End of fix 8232830
1850 -- End of fix 12972634
1851
1852
1853 -- End of fix 8232830
1854 -- End of fix 3815024
1855 --
1856 /* OPEN get_install_info;
1857 FETCH get_install_info INTO l_status;
1858 CLOSE get_install_info; */
1859
1860 /* Added call to maintain the denormalized table, For 420029 lwthomps */
1861 /* Added call to create tax records, 268389 lwthomps */
1862 /* Taken out nocopy comment from if legislation_code = 'US', Bug: 1196833 */
1863 IF l_legislation_code = 'US' then
1864 -- AND l_status = 'I'
1865 -- AND p_assignment_type = 'E' THEN
1866
1867 IF p_assignment_type = 'E' THEN
1868 --
1869
1870 --- Fix For Bug # 8860141 Starts ---
1871 OPEN csr_defaulting_date(p_assignment_id);
1872 FETCH csr_defaulting_date INTO l_default_date;
1873 IF csr_defaulting_date%FOUND THEN
1874
1875 OPEN csr_defaultpayrollremoved(p_assignment_id,p_effective_start_date);
1876 FETCH csr_defaultpayrollremoved INTO l_temp_char;
1877
1878 IF csr_defaultpayrollremoved%FOUND then
1879 close csr_defaulting_date;
1880 close csr_defaultpayrollremoved;
1881 hr_utility.set_message(801,'PAY_75264_US_PAYROLL_REMOVAL');
1882 hr_utility.raise_error;
1883 END IF;
1884 CLOSE csr_defaultpayrollremoved;
1885
1886 END IF;
1887 CLOSE csr_defaulting_date;
1888 --- Fix For Bug # 8860141 Ends ---
1889
1890 IF p_payroll_id IS NOT NULL AND p_pay_basis_id IS NOT NULL THEN
1891 --
1892 --
1893 if l_location_id_changed <> 1 then
1894 l_location_id := NULL;
1895 l_dt_update_mode := NULL;
1896 else
1897 l_location_id := p_location_id;
1898 l_dt_update_mode := p_dt_update_mode;
1899 end if;
1900
1901 hr_utility.set_location(g_package || l_proc, 80);
1902
1903 pay_us_emp_dt_tax_rules.default_tax_with_validation(
1904 p_assignment_id => p_assignment_id,
1905 p_person_id => p_person_id,
1906 p_effective_start_date => p_effective_start_date,
1907 p_effective_end_date => p_effective_end_date,
1908 p_session_date => p_session_date,
1909 p_business_group_id => p_business_group_id,
1910 p_from_form => 'Assignment',
1911 p_mode => l_dt_update_mode,
1912 p_location_id => l_location_id,
1913 p_return_code => l_return_code,
1914 p_return_text => l_return_text);
1915 END IF;
1916 --
1917 pay_asg_geo_pkg.pay_us_asg_rpt( p_assignment_id );
1918 --
1919 END IF;
1920 END IF; /* End of legislation code check */
1921 /*
1922 --WWBUG 2130950 Begin hrwf synchronization --tpapired
1923 open l_asg_cur;
1924 fetch l_asg_cur into l_asg_rec;
1925 close l_asg_cur;
1926 per_hrwf_synch.PER_ASG_WF(
1927 p_rec => l_asg_rec,
1928 p_action => 'UPDATE');
1929 --WWBUG 2130950 End hrwf synchronization --tpapired
1930 */
1931 --
1932 end update_row;
1933 /****************************/
1934 --
1935 procedure dml_promotion(
1936 p_assignment_id number,
1937 p_promotion_date date,
1938 p_prom_flag char) is
1939 --
1940 cursor c_promotion_exists is
1941 select assignment_extra_info_id, object_version_number
1942 from per_assignment_extra_info
1943 where assignment_id = p_assignment_id
1944 and information_type = 'PROMOTION'
1945 and fnd_date.canonical_to_date(aei_information1) = p_promotion_date;
1946 --
1947 l_promotion_rec c_promotion_exists%rowtype;
1948 l_assignment_extra_info_id number;
1949 l_object_version_number number;
1950 --
1951 l_proc varchar2(13) := 'dml_promotion';
1952 --
1953 begin
1954 -- g_debug := hr_utility.debug_enabled; -- get debug status
1955 IF g_debug THEN
1956 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1957 END IF;
1958
1959 open c_promotion_exists;
1960 fetch c_promotion_exists into l_promotion_rec;
1961 if p_prom_flag = 'Y' then
1962 if c_promotion_exists%notfound then
1963 hr_assignment_extra_info_api.create_assignment_extra_info(
1964 p_assignment_id => p_assignment_id
1965 ,p_information_type => 'PROMOTION'
1966 ,p_aei_information_category => 'PROMOTION' -- #1965910
1967 ,p_aei_information1 => p_promotion_date
1968 ,p_assignment_extra_info_id => l_assignment_extra_info_id
1969 ,p_object_version_number => l_object_version_number
1970 );
1971 end if;
1972 else
1973 if c_promotion_exists%found then
1974 hr_assignment_extra_info_api.delete_assignment_extra_info(
1975 p_assignment_extra_info_id => l_promotion_rec.assignment_extra_info_id
1976 ,p_object_version_number => l_promotion_rec.object_version_number
1977 );
1978 end if;
1979 end if;
1980 --
1981 close c_promotion_exists;
1982 --
1983 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
1984 --
1985 end dml_promotion;
1986 -----------------------------------------------------------------------------
1987 --
1988 -- **** End of standard ON-* checks. ****
1989 --
1990 -----------------------------------------------------------------------------
1991 --
1992 END PER_ASSIGNMENTS_F_PKG;