[Home] [Help]
25: Description : This package defines procedures required to
26: INSERT, UPDATE and DELETE assignments and all
27: associated tables :
28:
29: PER_ASSIGNMENTS_F
30: PER_SECONDARY_ASSIGNMENT_STATUSES
31: PER_ASSIGNMENT_BUDGET_VALUES_F
32:
33:
174:
175: 110.7 22-DEC-1998 bgoodsel 679966 Removed restriction in not exists... sub-query that causes
176: duplicate rows in some circumstances
177: 110.8 07-MAY-1999 achauhan For Bug# 787633, in del_ref_int_check, added the join to
178: per_assignments_f to check for the primary assignment. If
179: it is not a primary assignment then let it get purged.
180: For Bug# 785427,if the federal tax record exists then the
181: state, county and city tax records also exist (due to the
182: defaulting of tax records). So, delete from all 4 table.
268: 115.42 13-Jun-05 hsajja Changed cursors c0, c1, c2 in procedure
269: load_assignment_allocation
270: 115.43 25-Jan-06 bshukla 4946199 Modified DEL_REF_INT_CHECK()
271: 115.44 02-Mar-06 risgupta 4232539 used per_all_assignments_f in place of
272: per_assignments_f in the function
273: validate_ass_number of procedure
274: gen_new_ass_number.
275: 115.47 12-Sep-06 ghshanka 5498344 Modified the procedure gen_new_ass_number.
276: 115.48 26-Oct-06 agolechh 5619940 Modified the procedure gen_probation_end.
392: --
393: hr_utility.set_location('hr_assignment.gen_probation_end',4);
394: select v_date_probation_end - min(effective_start_date)
395: into check_date
396: from per_assignments_f
397: where assignment_id = p_assignment_id
398: and assignment_type = 'E';
399: --
400: EXCEPTION
434: --
435: hr_utility.set_location('hr_assignment.gen_new_ass_sequence',1);
436: select nvl(max(assignment_sequence),0) +1
437: into p_assignment_sequence
438: from per_assignments_f
439: where person_id = p_person_id
440: and assignment_type = p_assignment_type;
441: --
442: end gen_new_ass_sequence; ------------------------------------------
529: from sys.dual
530: where exists
531: ( select 'Y'
532: from per_all_assignments_f
533: -- from per_assignments_f commented for bug 4232539
534: where ((p_assignment_id is null)
535: or
536: ( p_assignment_id is not null
537: and assignment_id <> p_assignment_id))
822: hr_utility.set_location('hr_assignment.check_term',3);
823: --
824: select max(effective_end_date)
825: into p_ass_end_date
826: from per_assignments_f
827: where assignment_id = p_assignment_id;
828: --
829: -------------------------------------
830: -- Get the Start Date of the First TERM_ASSIGN status.
836: hr_utility.set_location('hr_assignment.check_term',4);
837: --
838: select min(a.effective_start_date)
839: into p_first_term_date
840: from per_assignments_f a
841: where a.assignment_id = p_assignment_id
842: and exists ( select null
843: from per_assignment_status_types s
844: where s.assignment_status_type_id
889: hr_utility.set_location('hr_assignment.check_term',7);
890: --
891: select 'Y'
892: into p_flag
893: from per_assignments_f a
894: where a.assignment_id = p_assignment_id
895: and a.effective_start_date = p_atd + 1
896: and exists
897: (select null
1010: , a.effective_end_date
1011: into p_flag
1012: , p_next_eff_start_date
1013: , p_next_eff_end_date
1014: from per_assignments_f a
1015: where a.assignment_id = p_assignment_id
1016: and a.effective_start_date = p_end_date + 1
1017: and exists
1018: (select null
1111: into p_term_found
1112: from sys.dual
1113: where exists
1114: (select null
1115: from per_assignments_f a
1116: , per_assignment_status_types s
1117: where a.assignment_id = p_assignment_id
1118: and a.effective_start_date
1119: > p_effective_start_date
1370: into p_change_flag
1371: from sys.dual
1372: where exists
1373: (select null
1374: from per_assignments_f
1375: where assignment_id = p_assignment_id
1376: and primary_flag <> p_new_primary_flag
1377: and effective_start_date >
1378: decode(p_mode,'ZAP',effective_start_date-1,p_start_date)
1392: hr_utility.set_location('hr_assignment.check_future_primary',3);
1393: --
1394: select min(effective_start_date)
1395: into p_primary_date_from_d
1396: from per_assignments_f
1397: where assignment_id = p_assignment_id
1398: and primary_flag = 'Y';
1399: END IF;
1400: --
1495: hr_utility.set_location('hr_assignment.check_ass_for_primary',2);
1496: --
1497: select max(effective_end_date)
1498: into p_ass_end_date
1499: from per_assignments_f
1500: where assignment_id = p_assignment_id;
1501: --
1502: -- If the end date of the assignment is not on or after the
1503: -- period of service final process date then ERROR
1523: if l_assignment_type <> 'C' then
1524:
1525: select min(a.effective_start_date)
1526: into p_first_term_date
1527: from per_assignments_f a
1528: where a.assignment_id = p_assignment_id
1529: and exists ( select null
1530: from per_assignment_status_types s
1531: where s.assignment_status_type_id
1564: l_proc VARCHAR2(72) := g_package||'update_primary_cwk';
1565: --
1566: CURSOR get_future_primary_assignments IS
1567: SELECT assignment_id
1568: FROM per_assignments_f
1569: WHERE assignment_id NOT IN (p_assignment_id,p_new_primary_ass_id)
1570: AND person_id = p_person_id
1571: AND period_of_placement_date_start = p_pop_date_start
1572: AND effective_end_date >= l_start_date;
1689: p_start_date DATE;
1690: --
1691: CURSOR get_future_primary_assignments IS
1692: select assignment_id
1693: from per_assignments_f
1694: where assignment_id not in (P_ASSIGNMENT_ID,P_NEW_PRIMARY_ASS_ID)
1695: and period_of_service_id = P_PERIOD_OF_SERVICE_ID
1696: and effective_end_date >= P_START_DATE;
1697: --
1789: p_start_date DATE;
1790: --
1791: CURSOR select_ass_for_update IS
1792: select *
1793: from per_assignments_f
1794: where assignment_id = P_ASSIGNMENT_ID
1795: and ((p_current_ass <> 'Y'
1796: and (P_START_DATE
1797: between effective_start_date
1849: NULL;
1850: ELSE
1851: hr_utility.set_location('hr_assignment.do_primary_update',3);
1852: --
1853: update per_assignments_f
1854: set primary_flag = P_PRIMARY_FLAG
1855: , last_updated_by = P_LAST_UPDATED_BY
1856: , last_update_login = P_LAST_UPDATE_LOGIN
1857: , last_update_date = sysdate
1869: ELSE
1870: --
1871: hr_utility.set_location('hr_assignment.do_primary_update',5);
1872: --
1873: insert into per_assignments_f
1874: (
1875: ASSIGNMENT_ID
1876: ,EFFECTIVE_START_DATE
1877: ,EFFECTIVE_END_DATE
2093: ,PROJECT_TITLE
2094: -- Bug 12820636
2095: ,GRADE_LADDER_PGM_ID
2096: ,SUPERVISOR_ASSIGNMENT_ID
2097: from per_assignments_f
2098: where assignment_id = P_ASSIGNMENT_ID
2099: and P_START_DATE
2100: between effective_start_date and effective_end_date
2101: and primary_flag <> P_PRIMARY_FLAG ;
2104: NULL; -- This Assignment Start in the Future
2105: ELSE
2106: hr_utility.set_location('hr_assignment.do_primary_update',6);
2107: --
2108: update per_assignments_f
2109: set effective_start_date = P_START_DATE
2110: , primary_flag = P_PRIMARY_FLAG
2111: , last_updated_by = P_LAST_UPDATED_BY
2112: , last_update_login = P_LAST_UPDATE_LOGIN
2129: END IF; -- (p_current_ass = 'Y')
2130: --
2131: hr_utility.set_location('hr_assignment.do_primary_update',8);
2132: --
2133: update per_assignments_f
2134: set primary_flag = P_PRIMARY_FLAG
2135: , last_updated_by = P_LAST_UPDATED_BY
2136: , last_update_login = P_LAST_UPDATE_LOGIN
2137: , last_update_date = sysdate
2221: -- assignment is not possible for contingent workers.
2222: -------------------------------------
2223: CURSOR get_candidate_primary_ass IS
2224: select to_char(a.assignment_id)
2225: from per_assignments_f a,
2226: per_assignment_status_types ast
2227: where assignment_id <> p_assignment_id
2228: and a.effective_start_date <= p_start_date
2229: and a.effective_end_date >= p_start_date
2236: a.assignment_type = 'C' and
2237: ast.per_system_status = 'ACTIVE_CWK'))
2238: and exists
2239: (select null
2240: from per_assignments_f a2
2241: where a2.assignment_id = a.assignment_id
2242: and a2.effective_end_date >= p_fpd)
2243: and not exists
2244: (select null
2241: where a2.assignment_id = a.assignment_id
2242: and a2.effective_end_date >= p_fpd)
2243: and not exists
2244: (select null
2245: from per_assignments_f a3
2246: where a3.assignment_id = a.assignment_id
2247: and a3.effective_start_date between p_start_date and p_atd
2248: and exists
2249: (select null
2455: into p_del_flag
2456: from sys.dual
2457: where exists (
2458: select null
2459: from PER_ASSIGNMENTS_F A
2460: , FND_SESSIONS S
2461: where a.assignment_id = p_assignment_id
2462: and a.assignment_type = 'E'
2463: and effective_date
2464: between a.effective_start_date and a.effective_end_date
2465: and session_id = userenv('SESSIONID')
2466: and exists
2467: (select null
2468: from PER_ASSIGNMENTS_F B
2469: where b.assignment_id = p_assignment_id
2470: and b.assignment_type = 'A'
2471: and b.effective_end_date < a.effective_start_date)
2472: );
2804: --
2805: BEGIN
2806:
2807: /* In the select below, added the join to
2808: per_assignments_f to check for the primary assignment. If
2809: it is not a primary assignment then let it get purged.
2810: This has beeen changed for Bug# 787633 */
2811:
2812: select 'Y'
2814: from sys.dual
2815: where exists (
2816: select null
2817: from PAY_US_EMP_FED_TAX_RULES_F pef,
2818: per_assignments_f paf
2819: where pef.assignment_id = p_assignment_id
2820: and paf.assignment_id = pef.assignment_id
2821: and paf.primary_flag = 'Y'
2822: and (p_mode = 'ZAP'
4147: into term_exists
4148: from sys.dual
4149: where exists
4150: (select null
4151: from per_assignments_f a
4152: , per_assignment_status_types s
4153: where a.assignment_id = p_assignment_id
4154: and a.effective_start_date < p_start_date
4155: and a.assignment_status_type_id = s.assignment_status_type_id
4200: hr_utility.set_location('peassign.call_terminate_entries',5);
4201: BEGIN
4202: select tp.end_date
4203: into p_last_standard_date
4204: from per_assignments_f a
4205: , per_time_periods tp
4206: where a.assignment_id = p_assignment_id
4207: and a.effective_end_date = p_start_date - 1
4208: and a.payroll_id = tp.payroll_id
4256: return boolean is
4257: begin
4258: select min(a.effective_start_date)
4259: into l_leading_date
4260: from per_assignments_f a
4261: , per_assignment_status_types s
4262: where a.assignment_id = p_assignment_id
4263: and (l_ignore_val_start_date = 'N' or
4264: (l_ignore_val_start_date = 'Y' and
4275: l_new_lspd := l_default_lspd;
4276: begin
4277: select tp.end_date
4278: into l_new_lspd
4279: from per_assignments_f a
4280: , per_time_periods tp
4281: where a.assignment_id = p_assignment_id
4282: and a.effective_end_date = l_default_lspd
4283: and a.payroll_id = tp.payroll_id
4404: if p_mode = 'DELETE_NEXT_CHANGE' then
4405: begin
4406: select min(a.effective_start_date)
4407: into l_new_leading_date
4408: from per_assignments_f a
4409: , per_assignment_status_types s
4410: where a.assignment_id = p_assignment_id
4411: and effective_start_date > p_validation_end_date
4412: and a.assignment_status_type_id = s.assignment_status_type_id
4466: from per_cobra_cov_enrollments e
4467: where e.assignment_id = p_assignment_id
4468: and exists
4469: (select null
4470: from per_assignments_f a
4471: where a.assignment_id = p_assignment_id
4472: and a.effective_start_date between p_sdate and p_edate
4473: and exists ( select null
4474: from per_assignment_status_types s