[Home] [Help]
295: begin
296: --
297: hr_utility.set_location('hr_person.update_primary_assignment',1);
298: --
299: insert into per_assignments_f
300: (assignment_id
301: ,effective_start_date
302: ,effective_end_date
303: ,business_group_id
493: ,pa.CAGR_ID_FLEX_NUM
494: ,pa.CAGR_GRADE_DEF_ID
495: ,pa.GRADE_LADDER_PGM_ID -- fix of bug 5513751
496: ,pa.contract_id -- fix of bug 14784589
497: from per_assignments_f pa
498: where pa.person_id = p_person_id
499: and pa.business_group_id + 0 = p_business_group_id
500: and pa.primary_flag = 'Y'
501: and p_start_date between pa.effective_start_date
563: and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
564: --
565: -- #2264569
566: --
567: l_asg_rec per_assignments_f%ROWTYPE;
568: --
569: cursor ass_cur is
570: select pa.*
571: from per_assignments_f pa
567: l_asg_rec per_assignments_f%ROWTYPE;
568: --
569: cursor ass_cur is
570: select pa.*
571: from per_assignments_f pa
572: , per_assignment_status_types past
573: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
574: and nvl(past.legislation_code, p_legislation_code)
575: = p_legislation_code
592: --
593: /*fix for the bug 5498344 starts here
594: cursor csr_ass_cur_for_primary is
595: select pa.*
596: from per_assignments_f pa
597: , per_assignment_status_types past
598: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
599: and nvl(past.legislation_code, p_legislation_code)
600: = p_legislation_code
616: --
617:
618: cursor csr_ass_cur_for_nonprimary is
619: select pa.*
620: from per_assignments_f pa
621: , per_assignment_status_types past
622: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
623: and nvl(past.legislation_code, p_legislation_code)
624: = p_legislation_code
677: l_ass_attribute30 VARCHAR2(150);
678: --
679: --
680: -- Bug 401669 Created cursor to fetch non global columns
681: -- Cursor to fetch record from per_assignments_f
682: --
683: cursor get_application_column_name is
684: select application_column_name
685: from fnd_descr_flex_column_usages fdfcu,
745: set date_end = p_start_date -1,
746: successful_flag = 'Y'
747: where pap.person_id = p_person_id -- added for bug 5469726
748: and exists (select '1'
749: from per_assignments_f pa,
750: per_assignment_status_types past
751: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
752: and nvl(past.legislation_code, p_legislation_code)
753: = p_legislation_code
760: and p_start_date between pap.date_received and nvl(pap.date_end,p_start_date)
761: and p_start_date between pa.effective_start_date
762: and pa.effective_end_date)
763: and not exists (select '1'
764: from per_assignments_f pa,
765: per_assignment_status_types past
766: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
767: and nvl(past.legislation_code, p_legislation_code)
768: = p_legislation_code
820: -- +--------------------------------------------------+
821: -- +--- End Date assignment type 'A' -----------------+
822: -- +--------------------------------------------------+
823: begin
824: insert into per_assignments_f
825: (assignment_id
826: ,effective_start_date
827: ,effective_end_date
828: ,business_group_id
1242: end;
1243: -- fix for 7120387
1244: --
1245: begin
1246: update per_assignments_f pa
1247: set pa.assignment_status_type_id = p_assignment_status_type_id
1248: , pa.assignment_type = 'E'
1249: , pa.effective_start_date = p_start_date
1250: , pa.effective_end_date = p_end_of_time
1421: --
1422: hr_utility.set_location('hrhirapl.make_secondary',5);
1423: l_chk_assg_end_dated := 'Y'; -- bug 6310975
1424: begin
1425: update per_assignments_f
1426: set effective_end_date = p_start_date -1
1427: where current of ass_cur;
1428:
1429: if per_otherbg_apl_api.isMultiRegVac(l_asg_rec.assignment_id) then
1567: -- +--------------------------------------------------+
1568: -- +--- End Date assignment type 'A' -----------------+
1569: -- +--------------------------------------------------+
1570: begin
1571: insert into per_assignments_f
1572: (assignment_id
1573: ,effective_start_date
1574: ,effective_end_date
1575: ,business_group_id
1935: ,p_assignment_number);
1936: --
1937: hr_utility.set_location('hrhirapl.make_secondary',402);
1938: begin
1939: update per_assignments_f pa
1940: set pa.assignment_status_type_id = p_assignment_status_type_id
1941: , pa.assignment_type = 'E'
1942: , pa.effective_start_date = p_start_date
1943: , pa.effective_end_date = p_end_of_time
2037: -- +--------------------------------------------------+
2038: --
2039: hr_utility.set_location('hrhirapl.make_secondary',5);
2040: begin
2041: update per_assignments_f
2042: set effective_end_date = p_start_date -1
2043: where current of csr_ass_cur_for_primary;
2044: exception
2045: when others then
2106: -- +--------------------------------------------------+
2107: -- +--- End Date assignment type 'A' -----------------+
2108: -- +--------------------------------------------------+
2109: begin
2110: insert into per_assignments_f
2111: (assignment_id
2112: ,effective_start_date
2113: ,effective_end_date
2114: ,business_group_id
2474: ,p_assignment_number);
2475: --
2476: hr_utility.set_location('hrhirapl.make_secondary',405);
2477: begin
2478: update per_assignments_f pa
2479: set pa.assignment_status_type_id = p_assignment_status_type_id
2480: , pa.assignment_type = 'E'
2481: , pa.effective_start_date = p_start_date
2482: , pa.effective_end_date = p_end_of_time
2574: -- +--------------------------------------------------+
2575: --
2576: hr_utility.set_location('hrhirapl.make_secondary',407);
2577: begin
2578: update per_assignments_f
2579: set effective_end_date = p_start_date -1
2580: where current of csr_ass_cur_for_nonprimary;
2581: exception
2582: when others then
2649: ) is
2650: --
2651: cursor fut_asg is
2652: select rowid, assignment_id, effective_start_date, effective_end_date
2653: from per_assignments_f
2654: where primary_flag = 'Y'
2655: and business_group_id + 0 = p_business_group_id
2656: and person_id = p_person_id
2657: and assignment_type = 'E'
2814: where exists
2815: (SELECT NULL
2816: FROM pay_payroll_actions pac,
2817: pay_assignment_actions act,
2818: per_assignments_f asg
2819: WHERE asg.person_id = p_person_id
2820: AND act.assignment_id = asg.assignment_id
2821: AND pac.payroll_action_id = act.payroll_action_id
2822: AND asg.assignment_id = lp_assignment_id
2894: procedure Delete_future_assignments(p_assignment_id number) is -- added for bug 9644377
2895: --
2896: cursor fut_asg is
2897: select rowid, assignment_id, effective_start_date, effective_end_date
2898: from per_assignments_f
2899: where primary_flag = 'Y'
2900: and business_group_id + 0 = p_business_group_id
2901: and person_id = p_person_id
2902: and assignment_type = 'E'
2918: exit when fut_asg%NOTFOUND;
2919:
2920: begin
2921:
2922: delete from per_assignments_f
2923: where rowid = l_rowid;
2924:
2925: exception
2926: when others then
2992: -- End changes for bug 9644377
2993:
2994: Delete_future_assignments(p_assignment_id => emp_asg_rec.assignment_id);
2995: --
2996: update per_assignments_f pa
2997: set -- pa.primary_flag = 'N', Changed for bug 9644377
2998: pa.effective_start_date =p_start_date
2999: , pa.effective_end_date = p_end_of_time
3000: , pa.last_update_login = p_login_id
3227: cursor ass_cur is
3228: select assignment_id
3229: , rowid
3230: , effective_end_date -- #1769702
3231: from per_assignments_f pa
3232: where pa.primary_flag='Y'
3233: and pa.person_id = p_person_id
3234: and pa.business_group_id + 0 = p_business_group_id
3235: and p_start_date between pa.effective_start_date
3255: select assignment_id
3256: , rowid
3257: , effective_start_date
3258: , effective_end_date
3259: from per_assignments_f pa
3260: where pa.primary_flag = 'Y'
3261: and pa.business_group_id + 0 = p_business_group_id
3262: and pa.person_id = p_person_id
3263: and pa.assignment_type = 'E'
3267:
3268: -- Start changes for bug 13059935
3269: cursor ASS_CUR1(p_row_id rowid) is
3270: select *
3271: from per_assignments_f a
3272: where a.rowid = P_ROW_ID;
3273: --
3274: ass_rec ASS_CUR1%rowtype;
3275: -- End changes for bug 13059935
3406:
3407: --7120387
3408: --
3409:
3410: update per_assignments_f pa
3411: set pa.organization_id = apl_asg_rec.organization_id
3412: ,pa.effective_start_date = p_start_date
3413: --,pa.effective_end_date = p_end_of_time -- commented for bug 9644377
3414: ,pa.recruiter_id = apl_asg_rec.recruiter_id
4017: --
4018: -- Changed the value for set_of_books_if from p_set_of_books_id to
4019: -- emp_asg_rec.set_of_books_id bug #2398327
4020:
4021: update per_assignments_f pa
4022: set pa.organization_id = emp_asg_rec.organization_id
4023: ,pa.effective_start_date = p_start_date
4024: --,pa.effective_end_date = p_end_of_time -- #1769702 what if future asg exist?
4025: ,pa.recruiter_id = emp_asg_rec.recruiter_id
4246:
4247: -- Changed the value for set_of_books_if from p_set_of_books_id to
4248: -- emp_asg_rec.set_of_books_id bug #2398327
4249:
4250: update per_assignments_f pa
4251: set pa.organization_id = emp_asg_rec.organization_id
4252: ,pa.recruiter_id = emp_asg_rec.recruiter_id
4253: ,pa.grade_id = emp_asg_rec.grade_id
4254: ,pa.position_id = emp_asg_rec.position_id
4475: -- +---- End Date chosen APL primary assignment id ------+
4476: begin
4477: declare cursor app_cur is
4478: select pa.rowid
4479: from per_assignments_f pa
4480: where pa.assignment_id = p_new_primary_id
4481: and p_start_date between pa.effective_start_date
4482: and pa.effective_end_date
4483: for update of pa.effective_end_date;
4488: loop
4489: fetch app_cur into p_rowid;
4490: exit when app_cur%notfound;
4491: hr_utility.set_location('hrhirapl.create_primary_assignment',23);
4492: update per_assignments_f
4493: set effective_end_date = p_start_date - 1
4494: where rowid = p_rowid;
4495: end loop;
4496: if app_cur%ROWCOUNT <1 then
4952: l_end_date DATE; -- Day before hire.
4953: --
4954: -- # 2264569
4955: --
4956: l_asgid per_assignments_f.assignment_id%TYPE;
4957: --
4958: cursor unacc_cur is
4959: select pa.assignment_id
4960: from per_assignments_f pa
4956: l_asgid per_assignments_f.assignment_id%TYPE;
4957: --
4958: cursor unacc_cur is
4959: select pa.assignment_id
4960: from per_assignments_f pa
4961: where pa.person_id = p_person_id
4962: and pa.business_group_id + 0 = p_business_group_id
4963: and pa.assignment_type = 'A'
4964: and p_end_date between pa.effective_start_date
5066: --
5067: cursor events is
5068: select pe.event_id
5069: from per_events pe
5070: , per_assignments_f a
5071: where pe.business_group_id +0 = a.business_group_id
5072: and a.business_group_id = p_business_group_id
5073: and pe.assignment_id = a.assignment_id
5074: and pe.date_start >=p_start_date