[Home] [Help]
204: select rowid
205: from pay_personal_payment_methods ppm
206: where ppm.business_group_id = p_business_group_id
207: and exists (select 'exists'
208: from per_all_assignments_f paf
209: where paf.business_group_id +0= p_business_group_id
210: and paf.person_id = p_person_id
211: and paf.assignment_id = ppm.assignment_id
212: --start bug 5987416
556: select rowid
557: from pay_personal_payment_methods ppm
558: where ppm.business_group_id = p_business_group_id
559: and exists (select 'exists'
560: from per_all_assignments_f paf
561: where paf.business_group_id +0= p_business_group_id
562: and paf.person_id = p_person_id
563: and paf.assignment_id = ppm.assignment_id
564: and paf.period_of_service_id = p_primary_id
814: -- Cursor to seelct assignment actions on or after the hire date.
815: --
816: cursor csr_assign_actions_exist is
817: select 'Y'
818: from per_all_assignments_f a
819: where a.person_id = p_person_id
820: --
821: -- 115.51 (START)
822: --
826: --
827: AND ((a.effective_start_date = p_primary_date
828: and a.primary_flag <> 'Y'
829: and not exists ( select b.assignment_id
830: from per_all_assignments_f b
831: where nvl(b.effective_end_date,hr_api.g_eot)
832: = (p_primary_date-1)
833: and b.assignment_id = a.assignment_id) )
834: OR a.effective_start_date > p_primary_date );
916: select rowid
917: from per_assignment_budget_values_f pab
918: where pab.business_group_id = p_business_group_id and
919: exists (select 'budget_values exist'
920: from per_all_assignments_f paf
921: where pab.business_group_id +0= paf.business_group_id + 0
922: and paf.business_group_id +0= p_business_group_id
923: and pab.assignment_id = paf.assignment_id
924: and paf.person_id = p_person_id
936: select rowid
937: from pay_personal_payment_methods ppm
938: where ppm.business_group_id = p_business_group_id
939: and exists (select 'exists'
940: from per_all_assignments_f paf
941: where paf.business_group_id +0= p_business_group_id
942: and paf.person_id = p_person_id
943: and paf.assignment_id = ppm.assignment_id
944: and paf.period_of_service_id = p_primary_id
1398: l_assignment_id NUMBER;
1399: l_person_type_id NUMBER;
1400: l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
1401: l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
1402: l_asg_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1403: l_dummy varchar2(10);
1404:
1405: cursor budget_values1 is
1406: select rowid
1407: from per_assignment_budget_values_f pab
1408: where pab.business_group_id = p_business_group_id and
1409:
1410: exists (select 'budget_values exist'
1411: from per_all_assignments_f paf
1412: where pab.business_group_id +0= paf.business_group_id + 0
1413: and paf.business_group_id +0= p_business_group_id
1414: and pab.assignment_id = paf.assignment_id
1415: and paf.person_id = p_person_id
1426: select rowid
1427: from pay_personal_payment_methods ppm
1428: where ppm.business_group_id = p_business_group_id
1429: and exists (select 'exists'
1430: from per_all_assignments_f paf
1431: where paf.business_group_id +0= p_business_group_id
1432: and paf.person_id = p_person_id
1433: and paf.assignment_id = ppm.assignment_id
1434: and paf.period_of_service_id = l_period_of_service_id
1441:
1442: cursor assignments is
1443:
1444: select rowid, assignment_id, assignment_status_type_id
1445: from per_all_assignments_f paf1
1446: where paf1.business_group_id +0 = p_business_group_id
1447: and paf1.person_id = p_person_id
1448: and paf1.assignment_type ='A'
1449: and paf1.effective_end_date = p_date_start -1
1447: and paf1.person_id = p_person_id
1448: and paf1.assignment_type ='A'
1449: and paf1.effective_end_date = p_date_start -1
1450: and exists ( select '1' from
1451: per_all_assignments_f paf2
1452: where paf2.business_group_id +0= p_business_group_id
1453: and paf2.person_id = p_person_id
1454: and PAF2.assignment_type ='E'
1455: and paf2.effective_start_date = p_date_start
1471: --
1472: --
1473: cursor ass1 is
1474: select assignment_id,effective_start_date
1475: from per_all_assignments_f papf1
1476: where papf1.person_id = p_person_id
1477: and papf1.business_group_id +0 = p_business_group_id
1478: and papf1.period_of_service_id is NULL
1479: and papf1.assignment_type in ('A')
1477: and papf1.business_group_id +0 = p_business_group_id
1478: and papf1.period_of_service_id is NULL
1479: and papf1.assignment_type in ('A')
1480: and (p_date_start -1) =( select max(effective_end_date)
1481: from per_all_assignments_f papf2
1482: where papf1.assignment_id =papf2.assignment_id);
1483:
1484: cursor ass2 is
1485: select assignment_id,effective_start_date,ROWID
1482: where papf1.assignment_id =papf2.assignment_id);
1483:
1484: cursor ass2 is
1485: select assignment_id,effective_start_date,ROWID
1486: from per_all_assignments_f
1487: where person_id = p_person_id
1488: and business_group_id +0 = p_business_group_id
1489: and period_of_service_id is NULL
1490: and assignment_type in ('A')
1492:
1493: -- primary assignment update
1494: cursor csr_prm_asg is
1495: select assignment_id
1496: from per_all_assignments_f
1497: where person_id= p_person_id
1498: and business_group_id= p_business_group_id
1499: and assignment_type='E'
1500: and primary_flag='Y'
1502: and period_of_service_id=l_period_of_service_id;
1503: --
1504: cursor c_assignments is
1505: select paf.assignment_id, paf.effective_start_date
1506: from per_all_assignments_f paf
1507: where paf.person_id = p_person_id
1508: and paf.business_group_id +0 = p_business_group_id
1509: and paf.assignment_type not in ('B','O'); -- issue 3 raised .
1510:
1514: cursor applications is
1515: select rowid
1516: from per_applications pap
1517: where exists (select 'row exists'
1518: from per_all_assignments_f paf
1519: where paf.person_id = p_person_id
1520: and paf.business_group_id +0 = p_business_group_id
1521: and paf.period_of_service_id is NULL
1522: and paf.effective_end_date = p_date_start - 1
1585: -- Cursor to seelct assignment actions on or after the hire date.
1586: --
1587: cursor csr_assign_actions_exist is
1588: select 'Y'
1589: from per_all_assignments_f a
1590: where a.person_id = p_person_id
1591:
1592: AND a.period_of_service_id = l_period_of_service_id
1593: AND ((a.effective_start_date = p_date_start
1592: AND a.period_of_service_id = l_period_of_service_id
1593: AND ((a.effective_start_date = p_date_start
1594: and a.primary_flag <> 'Y'
1595: and not exists ( select b.assignment_id
1596: from per_all_assignments_f b
1597: where nvl(b.effective_end_date,hr_api.g_eot)
1598: = (p_date_start-1)
1599: and b.assignment_id = a.assignment_id) )
1600: OR a.effective_start_date > p_date_start );
1793:
1794:
1795: hr_utility.set_location('cancel_emp_apl_hire ',110);
1796:
1797: delete from per_all_assignments_f paf
1798: where paf.assignment_id=l_assignment_id
1799: and paf.person_id=p_person_id
1800: and paf.effective_start_date=p_date_start;
1801:
1801:
1802: hr_utility.set_location('cancel_emp_apl_hire ',120);
1803:
1804:
1805: update per_all_assignments_f
1806: set effective_end_date = p_end_of_time
1807: where assignment_id=l_assignment_id
1808: and person_id=p_person_id
1809: and effective_end_date= p_date_start -1;
1874: exit when ass1%NOTFOUND;
1875: hr_utility.set_location('p_assignment_id '||p_assignment_id,160);
1876: hr_utility.set_location('p_date_start1 '||p_date_start1,160);
1877:
1878: update per_all_assignments_f
1879: set effective_end_date = p_end_of_time
1880: where assignment_id=p_assignment_id
1881: and person_id=p_person_id
1882: and effective_end_date= p_date_start -1;
1897: open csr_prm_asg;
1898: fetch csr_prm_asg into l_asg;
1899: close csr_prm_asg;
1900:
1901: delete from per_all_assignments_f
1902: where person_id= p_person_id
1903: and assignment_id = l_asg
1904: and effective_start_date > = p_date_start;
1905:
1902: where person_id= p_person_id
1903: and assignment_id = l_asg
1904: and effective_start_date > = p_date_start;
1905:
1906: update per_all_assignments_f
1907: set effective_end_date = p_end_of_time
1908:
1909: WHERE person_id= p_person_id
1910: and assignment_id = l_asg
1979:
1980: update per_applications pap
1981: set pap.date_end = NULL
1982: where application_id in ( select distinct (application_id)
1983: from per_all_assignments_f
1984: where person_id= p_person_id
1985: and assignment_type = 'A'
1986: and sysdate between effective_start_date and effective_end_date
1987: );
2087: l_person_type_id NUMBER;
2088: -- Start of fix 3564129
2089: l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
2090: l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
2091: l_asg_status_type_id per_all_assignments_f.assignment_status_type_id%type;
2092: -- End of fix 3564129
2093: --
2094:
2095: l_system_person_type per_person_types.system_person_type%type; --added for bug 8405711
2177: select rowid
2178: from per_assignment_budget_values_f pab
2179: where pab.business_group_id = p_business_group_id and
2180: exists (select 'budget_values exist'
2181: from per_all_assignments_f paf
2182: where pab.business_group_id +0= paf.business_group_id + 0
2183: and paf.business_group_id +0= p_business_group_id
2184: and pab.assignment_id = paf.assignment_id
2185: and paf.person_id = p_person_id
2197: select rowid
2198: from pay_personal_payment_methods ppm
2199: where ppm.business_group_id = p_business_group_id
2200: and exists (select 'exists'
2201: from per_all_assignments_f paf
2202: where paf.business_group_id +0= p_business_group_id
2203: and paf.person_id = p_person_id
2204: and paf.assignment_id = ppm.assignment_id
2205: and paf.period_of_service_id = l_period_of_service_id
2214: cursor comments1 is
2215: select rowid
2216: from hr_comments h
2217: where exists (select 'comments exist'
2218: from per_all_assignments_f paf
2219: where h.comment_id = paf.comment_id
2220: and paf.business_group_id +0= p_business_group_id
2221: and paf.person_id = p_person_id
2222: and paf.period_of_service_id = l_period_of_service_id);
2225: --
2226: -- VT #438579 03/05/79 added assignment_id
2227: cursor assignments is
2228: select rowid, assignment_id, assignment_status_type_id -- Bug 3564129
2229: from per_all_assignments_f paf
2230: where paf.business_group_id +0= p_business_group_id
2231: and paf.person_id = p_person_id
2232: and paf.period_of_service_id = l_period_of_service_id;
2233: --
2248: --Added the assignment type condition of 'A' for fix of #3390818
2249: --
2250: cursor ass1 is
2251: select assignment_id,effective_start_date,ROWID
2252: from per_all_assignments_f
2253: where person_id = p_person_id
2254: and business_group_id +0 = p_business_group_id
2255: and period_of_service_id is NULL
2256: and assignment_type in ('E','A') -- 3194314
2259: --
2260: --
2261: cursor csr_alus is -- fix for the bug 7578210
2262: select assignment_id,effective_start_date,ROWID
2263: from per_all_assignments_f
2264: where person_id = p_person_id
2265: and business_group_id +0 = p_business_group_id
2266: and period_of_service_id = l_period_of_service_id
2267: and assignment_type in ('E');
2268: --
2269: --
2270: cursor c_assignments is
2271: select paf.assignment_id, paf.effective_start_date
2272: from per_all_assignments_f paf
2273: where paf.person_id = p_person_id
2274: and paf.business_group_id +0 = p_business_group_id
2275: and assignment_type not in ('B','O'); --modified for bug #6449599 and bug # 7572514
2276: --
2279: cursor applications is
2280: select rowid
2281: from per_applications pap
2282: where exists (select 'row exists'
2283: from per_all_assignments_f paf
2284: where paf.person_id = p_person_id
2285: and paf.business_group_id +0 = p_business_group_id
2286: and paf.period_of_service_id is NULL
2287: and paf.effective_end_date = p_date_start - 1
2428: fetch supervisor1 into p_rowid;
2429: exit when supervisor1%NOTFOUND;
2430: --
2431: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
2432: update per_all_assignments_f paf
2433: set paf.supervisor_id = NULL
2434: where paf.rowid = p_rowid;
2435: --
2436: if sql%notfound then
2449: fetch recruiter1 into p_rowid;
2450: exit when recruiter1%NOTFOUND;
2451: --
2452: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
2453: update per_all_assignments_f paf
2454: set paf.recruiter_id = NULL
2455: where paf.rowid = p_rowid;
2456: --
2457: if sql%notfound then
2686: -- fix for bug 5005157 ends here.
2687:
2688:
2689: -- End of fix 3564129
2690: delete from per_all_assignments_f paf
2691: where paf.rowid = p_rowid;
2692: if sql%notfound then
2693: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2723: loop
2724: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
2725: fetch ass1 into p_assignment_id,p_start_date,p_rowid;
2726: exit when ass1%NOTFOUND;
2727: update per_all_assignments_f paf
2728: set paf.effective_end_date = p_end_of_time
2729: where paf.rowid = p_rowid;
2730:
2731:
3344: --
3345: -- Cursor to re-evaluate the security.
3346: cursor csr_asg_sec is
3347: select paf.assignment_id, paf.effective_start_date
3348: from per_all_assignments_f paf
3349: where paf.person_id = p_person_id
3350: and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913
3351: -- and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
3352: --
4092: SELECT ROWID
4093: FROM per_assignment_budget_values_f pab
4094: WHERE pab.business_group_id = p_business_group_id and
4095: EXISTS (SELECT 'budget_values exist'
4096: FROM per_all_assignments_f paf
4097: WHERE pab.business_group_id +0= paf.business_group_id + 0
4098: AND paf.business_group_id +0= p_business_group_id
4099: AND pab.assignment_id = paf.assignment_id
4100: AND paf.person_id = p_person_id
4113: SELECT ROWID
4114: FROM pay_personal_payment_methods ppm
4115: WHERE ppm.business_group_id = p_business_group_id
4116: AND EXISTS (SELECT 'exists'
4117: FROM per_all_assignments_f paf
4118: WHERE paf.business_group_id +0= p_business_group_id
4119: AND paf.person_id = p_person_id
4120: AND paf.assignment_id = ppm.assignment_id
4121: AND paf.period_of_placement_date_start = p_date_start
4528: SELECT ROWID
4529: FROM per_assignment_budget_values_f pab
4530: WHERE pab.business_group_id = p_business_group_id and
4531: EXISTS (SELECT 'budget_values exist'
4532: FROM per_all_assignments_f paf
4533: WHERE pab.business_group_id +0= paf.business_group_id + 0
4534: AND paf.business_group_id +0= p_business_group_id
4535: AND pab.assignment_id = paf.assignment_id
4536: AND paf.person_id = p_person_id
4548: SELECT ROWID
4549: FROM pay_personal_payment_methods ppm
4550: WHERE ppm.business_group_id = p_business_group_id
4551: AND EXISTS (SELECT 'exists'
4552: FROM per_all_assignments_f paf
4553: WHERE paf.business_group_id +0= p_business_group_id
4554: AND paf.person_id = p_person_id
4555: AND paf.assignment_id = ppm.assignment_id
4556: AND paf.period_of_placement_date_start = p_date_start
4605: CURSOR csr_comments IS
4606: SELECT ROWID
4607: FROM hr_comments h
4608: WHERE EXISTS (SELECT 'comments exist'
4609: FROM per_all_assignments_f paf
4610: WHERE h.comment_id = paf.comment_id
4611: AND paf.business_group_id +0 = p_business_group_id
4612: AND paf.person_id = p_person_id
4613: AND paf.period_of_placement_date_start = p_date_start);
4618: --
4619: CURSOR csr_assignments1 IS
4620: SELECT ROWID,
4621: assignment_id
4622: FROM per_all_assignments_f paf
4623: WHERE paf.business_group_id +0 = p_business_group_id
4624: AND paf.person_id = p_person_id
4625: AND paf.period_of_placement_date_start = p_date_start;
4626: --
4629: CURSOR csr_assignments2 IS
4630: SELECT assignment_id,
4631: effective_start_date,
4632: ROWID
4633: FROM per_all_assignments_f
4634: WHERE person_id = p_person_id
4635: AND business_group_id +0 = p_business_group_id
4636: AND effective_end_date = p_date_start - 1
4637: AND assignment_type = 'C' -- 3194314
4642: CURSOR csr_applications IS
4643: SELECT ROWID
4644: FROM per_applications pap
4645: WHERE EXISTS (SELECT 'row exists'
4646: FROM per_all_assignments_f paf
4647: WHERE paf.person_id = p_person_id
4648: AND paf.business_group_id +0 = p_business_group_id
4649: AND paf.effective_end_date = p_date_start - 1
4650: AND pap.application_id = paf.application_id);
4784: -- Changes end for the bug 13772471
4785: --
4786: cursor csr_asg_sec is
4787: select paf.assignment_id, paf.effective_start_date
4788: from per_all_assignments_f paf
4789: where paf.person_id = p_person_id
4790: and paf.business_group_id +0 = p_business_group_id
4791: and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913
4792: -- and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
4805: FOR supervisor_rec IN csr_supervisor LOOP
4806: --
4807: hr_utility.set_location(l_proc,60);
4808: --
4809: UPDATE per_all_assignments_f paf
4810: SET paf.supervisor_id = NULL
4811: WHERE paf.rowid = supervisor_rec.rowid;
4812: --
4813: IF sql%notfound THEN
4826: FOR recruiter_rec IN csr_recruiter LOOP
4827: --
4828: hr_utility.set_location(l_proc,80);
4829: --
4830: UPDATE per_all_assignments_f paf
4831: SET paf.recruiter_id = NULL
4832: WHERE paf.rowid = recruiter_rec.rowid;
4833: --
4834: IF SQL%NOTFOUND THEN
4990: WHERE pca.assignment_id = assignment1_rec.assignment_id;
4991: --
4992: hr_utility.set_location(l_proc,260);
4993: --
4994: DELETE FROM per_all_assignments_f paf
4995: WHERE paf.rowid = assignment1_rec.rowid;
4996: --
4997: IF SQL%NOTFOUND THEN
4998: --
5151: FOR assignment2_rec IN csr_assignments2 LOOP
5152: --
5153: hr_utility.set_location(l_proc,300);
5154: --
5155: UPDATE per_all_assignments_f paf
5156: SET paf.effective_end_date = l_end_of_time
5157: WHERE paf.rowid = assignment2_rec.rowid;
5158: --
5159: hr_utility.set_location(l_proc,310);