[Home] [Help]
275: begin
276: --
277: hr_utility.set_location('hr_person.update_primary_assignment',1);
278: --
279: insert into per_assignments_f
280: (assignment_id
281: ,effective_start_date
282: ,effective_end_date
283: ,business_group_id
470: ,pa.COLLECTIVE_AGREEMENT_ID
471: ,pa.CAGR_ID_FLEX_NUM
472: ,pa.CAGR_GRADE_DEF_ID
473: ,pa.GRADE_LADDER_PGM_ID -- fix of bug 5513751
474: from per_assignments_f pa
475: where pa.person_id = p_person_id
476: and pa.business_group_id + 0 = p_business_group_id
477: and pa.primary_flag = 'Y'
478: and p_start_date between pa.effective_start_date
540: and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
541: --
542: -- #2264569
543: --
544: l_asg_rec per_assignments_f%ROWTYPE;
545: --
546: cursor ass_cur is
547: select pa.*
548: from per_assignments_f pa
544: l_asg_rec per_assignments_f%ROWTYPE;
545: --
546: cursor ass_cur is
547: select pa.*
548: from per_assignments_f pa
549: , per_assignment_status_types past
550: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
551: and nvl(past.legislation_code, p_legislation_code)
552: = p_legislation_code
569: --
570: /*fix for the bug 5498344 starts here
571: cursor csr_ass_cur_for_primary is
572: select pa.*
573: from per_assignments_f pa
574: , per_assignment_status_types past
575: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
576: and nvl(past.legislation_code, p_legislation_code)
577: = p_legislation_code
593: --
594:
595: cursor csr_ass_cur_for_nonprimary is
596: select pa.*
597: from per_assignments_f pa
598: , per_assignment_status_types past
599: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
600: and nvl(past.legislation_code, p_legislation_code)
601: = p_legislation_code
654: l_ass_attribute30 VARCHAR2(150);
655: --
656: --
657: -- Bug 401669 Created cursor to fetch non global columns
658: -- Cursor to fetch record from per_assignments_f
659: --
660: cursor get_application_column_name is
661: select application_column_name
662: from fnd_descr_flex_column_usages fdfcu,
715: set date_end = p_start_date -1,
716: successful_flag = 'Y'
717: where pap.person_id = p_person_id -- added for bug 5469726
718: and exists (select '1'
719: from per_assignments_f pa,
720: per_assignment_status_types past
721: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
722: and nvl(past.legislation_code, p_legislation_code)
723: = p_legislation_code
730: and p_start_date between pap.date_received and nvl(pap.date_end,p_start_date)
731: and p_start_date between pa.effective_start_date
732: and pa.effective_end_date)
733: and not exists (select '1'
734: from per_assignments_f pa,
735: per_assignment_status_types past
736: where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
737: and nvl(past.legislation_code, p_legislation_code)
738: = p_legislation_code
790: -- +--------------------------------------------------+
791: -- +--- End Date assignment type 'A' -----------------+
792: -- +--------------------------------------------------+
793: begin
794: insert into per_assignments_f
795: (assignment_id
796: ,effective_start_date
797: ,effective_end_date
798: ,business_group_id
1212: end;
1213: -- fix for 7120387
1214: --
1215: begin
1216: update per_assignments_f pa
1217: set pa.assignment_status_type_id = p_assignment_status_type_id
1218: , pa.assignment_type = 'E'
1219: , pa.effective_start_date = p_start_date
1220: , pa.effective_end_date = p_end_of_time
1323: --
1324: hr_utility.set_location('hrhirapl.make_secondary',5);
1325: l_chk_assg_end_dated := 'Y'; -- bug 6310975
1326: begin
1327: update per_assignments_f
1328: set effective_end_date = p_start_date -1
1329: where current of ass_cur;
1330: exception
1331: when others then
1465: -- +--------------------------------------------------+
1466: -- +--- End Date assignment type 'A' -----------------+
1467: -- +--------------------------------------------------+
1468: begin
1469: insert into per_assignments_f
1470: (assignment_id
1471: ,effective_start_date
1472: ,effective_end_date
1473: ,business_group_id
1833: ,p_assignment_number);
1834: --
1835: hr_utility.set_location('hrhirapl.make_secondary',402);
1836: begin
1837: update per_assignments_f pa
1838: set pa.assignment_status_type_id = p_assignment_status_type_id
1839: , pa.assignment_type = 'E'
1840: , pa.effective_start_date = p_start_date
1841: , pa.effective_end_date = p_end_of_time
1933: -- +--------------------------------------------------+
1934: --
1935: hr_utility.set_location('hrhirapl.make_secondary',5);
1936: begin
1937: update per_assignments_f
1938: set effective_end_date = p_start_date -1
1939: where current of csr_ass_cur_for_primary;
1940: exception
1941: when others then
2002: -- +--------------------------------------------------+
2003: -- +--- End Date assignment type 'A' -----------------+
2004: -- +--------------------------------------------------+
2005: begin
2006: insert into per_assignments_f
2007: (assignment_id
2008: ,effective_start_date
2009: ,effective_end_date
2010: ,business_group_id
2370: ,p_assignment_number);
2371: --
2372: hr_utility.set_location('hrhirapl.make_secondary',405);
2373: begin
2374: update per_assignments_f pa
2375: set pa.assignment_status_type_id = p_assignment_status_type_id
2376: , pa.assignment_type = 'E'
2377: , pa.effective_start_date = p_start_date
2378: , pa.effective_end_date = p_end_of_time
2470: -- +--------------------------------------------------+
2471: --
2472: hr_utility.set_location('hrhirapl.make_secondary',407);
2473: begin
2474: update per_assignments_f
2475: set effective_end_date = p_start_date -1
2476: where current of csr_ass_cur_for_nonprimary;
2477: exception
2478: when others then
2545: ) is
2546: --
2547: cursor fut_asg is
2548: select rowid, assignment_id, effective_start_date, effective_end_date
2549: from per_assignments_f
2550: where primary_flag = 'Y'
2551: and business_group_id + 0 = p_business_group_id
2552: and person_id = p_person_id
2553: and assignment_type = 'E'
2707: procedure Delete_future_assignments is
2708: --
2709: cursor fut_asg is
2710: select rowid, assignment_id, effective_start_date, effective_end_date
2711: from per_assignments_f
2712: where primary_flag = 'Y'
2713: and business_group_id + 0 = p_business_group_id
2714: and person_id = p_person_id
2715: and assignment_type = 'E'
2730: exit when fut_asg%NOTFOUND;
2731:
2732: begin
2733:
2734: delete from per_assignments_f
2735: where rowid = l_rowid;
2736:
2737: exception
2738: when others then
2766: begin
2767: -- #1769702
2768: Delete_future_assignments;
2769: --
2770: update per_assignments_f pa
2771: set pa.primary_flag = 'N'
2772: , pa.effective_start_date =p_start_date
2773: , pa.effective_end_date = p_end_of_time
2774: , pa.last_update_login = p_login_id
2879: cursor ass_cur is
2880: select assignment_id
2881: , rowid
2882: , effective_end_date -- #1769702
2883: from per_assignments_f pa
2884: where pa.primary_flag='Y'
2885: and pa.person_id = p_person_id
2886: and pa.business_group_id + 0 = p_business_group_id
2887: and p_start_date between pa.effective_start_date
2907: select assignment_id
2908: , rowid
2909: , effective_start_date
2910: , effective_end_date
2911: from per_assignments_f pa
2912: where pa.primary_flag = 'Y'
2913: and pa.business_group_id + 0 = p_business_group_id
2914: and pa.person_id = p_person_id
2915: and pa.assignment_type = 'E'
3038:
3039: --7120387
3040: --
3041:
3042: update per_assignments_f pa
3043: set pa.organization_id = apl_asg_rec.organization_id
3044: ,pa.effective_start_date = p_start_date
3045: ,pa.effective_end_date = p_end_of_time
3046: ,pa.recruiter_id = apl_asg_rec.recruiter_id
3517: --
3518: -- Changed the value for set_of_books_if from p_set_of_books_id to
3519: -- emp_asg_rec.set_of_books_id bug #2398327
3520:
3521: update per_assignments_f pa
3522: set pa.organization_id = emp_asg_rec.organization_id
3523: ,pa.effective_start_date = p_start_date
3524: --,pa.effective_end_date = p_end_of_time -- #1769702 what if future asg exist?
3525: ,pa.recruiter_id = emp_asg_rec.recruiter_id
3681:
3682: -- Changed the value for set_of_books_if from p_set_of_books_id to
3683: -- emp_asg_rec.set_of_books_id bug #2398327
3684:
3685: update per_assignments_f pa
3686: set pa.organization_id = emp_asg_rec.organization_id
3687: ,pa.recruiter_id = emp_asg_rec.recruiter_id
3688: ,pa.grade_id = emp_asg_rec.grade_id
3689: ,pa.position_id = emp_asg_rec.position_id
3905: -- +---- End Date chosen APL primary assignment id ------+
3906: begin
3907: declare cursor app_cur is
3908: select pa.rowid
3909: from per_assignments_f pa
3910: where pa.assignment_id = p_new_primary_id
3911: and p_start_date between pa.effective_start_date
3912: and pa.effective_end_date
3913: for update of pa.effective_end_date;
3918: loop
3919: fetch app_cur into p_rowid;
3920: exit when app_cur%notfound;
3921: hr_utility.set_location('hrhirapl.create_primary_assignment',23);
3922: update per_assignments_f
3923: set effective_end_date = p_start_date - 1
3924: where rowid = p_rowid;
3925: end loop;
3926: if app_cur%ROWCOUNT <1 then
4347: l_end_date DATE; -- Day before hire.
4348: --
4349: -- # 2264569
4350: --
4351: l_asgid per_assignments_f.assignment_id%TYPE;
4352: --
4353: cursor unacc_cur is
4354: select pa.assignment_id
4355: from per_assignments_f pa
4351: l_asgid per_assignments_f.assignment_id%TYPE;
4352: --
4353: cursor unacc_cur is
4354: select pa.assignment_id
4355: from per_assignments_f pa
4356: where pa.person_id = p_person_id
4357: and pa.business_group_id + 0 = p_business_group_id
4358: and pa.assignment_type = 'A'
4359: and p_end_date between pa.effective_start_date
4457: --
4458: cursor events is
4459: select pe.event_id
4460: from per_events pe
4461: , per_assignments_f a
4462: where pe.business_group_id +0 = a.business_group_id
4463: and a.business_group_id = p_business_group_id
4464: and pe.assignment_id = a.assignment_id
4465: and pe.date_start >=p_start_date