[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.
379: --
380: hr_utility.set_location('hr_assignment.gen_probation_end',4);
381: select v_date_probation_end - min(effective_start_date)
382: into check_date
383: from per_assignments_f
384: where assignment_id = p_assignment_id
385: and assignment_type = 'E';
386: --
387: EXCEPTION
421: --
422: hr_utility.set_location('hr_assignment.gen_new_ass_sequence',1);
423: select nvl(max(assignment_sequence),0) +1
424: into p_assignment_sequence
425: from per_assignments_f
426: where person_id = p_person_id
427: and assignment_type = p_assignment_type;
428: --
429: end gen_new_ass_sequence; ------------------------------------------
516: from sys.dual
517: where exists
518: ( select 'Y'
519: from per_all_assignments_f
520: -- from per_assignments_f commented for bug 4232539
521: where ((p_assignment_id is null)
522: or
523: ( p_assignment_id is not null
524: and assignment_id <> p_assignment_id))
792: hr_utility.set_location('hr_assignment.check_term',3);
793: --
794: select max(effective_end_date)
795: into p_ass_end_date
796: from per_assignments_f
797: where assignment_id = p_assignment_id;
798: --
799: -------------------------------------
800: -- Get the Start Date of the First TERM_ASSIGN status.
806: hr_utility.set_location('hr_assignment.check_term',4);
807: --
808: select min(a.effective_start_date)
809: into p_first_term_date
810: from per_assignments_f a
811: where a.assignment_id = p_assignment_id
812: and exists ( select null
813: from per_assignment_status_types s
814: where s.assignment_status_type_id
859: hr_utility.set_location('hr_assignment.check_term',7);
860: --
861: select 'Y'
862: into p_flag
863: from per_assignments_f a
864: where a.assignment_id = p_assignment_id
865: and a.effective_start_date = p_atd + 1
866: and exists
867: (select null
980: , a.effective_end_date
981: into p_flag
982: , p_next_eff_start_date
983: , p_next_eff_end_date
984: from per_assignments_f a
985: where a.assignment_id = p_assignment_id
986: and a.effective_start_date = p_end_date + 1
987: and exists
988: (select null
1081: into p_term_found
1082: from sys.dual
1083: where exists
1084: (select null
1085: from per_assignments_f a
1086: , per_assignment_status_types s
1087: where a.assignment_id = p_assignment_id
1088: and a.effective_start_date
1089: > p_effective_start_date
1340: into p_change_flag
1341: from sys.dual
1342: where exists
1343: (select null
1344: from per_assignments_f
1345: where assignment_id = p_assignment_id
1346: and primary_flag <> p_new_primary_flag
1347: and effective_start_date >
1348: decode(p_mode,'ZAP',effective_start_date-1,p_start_date)
1362: hr_utility.set_location('hr_assignment.check_future_primary',3);
1363: --
1364: select min(effective_start_date)
1365: into p_primary_date_from_d
1366: from per_assignments_f
1367: where assignment_id = p_assignment_id
1368: and primary_flag = 'Y';
1369: END IF;
1370: --
1465: hr_utility.set_location('hr_assignment.check_ass_for_primary',2);
1466: --
1467: select max(effective_end_date)
1468: into p_ass_end_date
1469: from per_assignments_f
1470: where assignment_id = p_assignment_id;
1471: --
1472: -- If the end date of the assignment is not on or after the
1473: -- period of service final process date then ERROR
1493: if l_assignment_type <> 'C' then
1494:
1495: select min(a.effective_start_date)
1496: into p_first_term_date
1497: from per_assignments_f a
1498: where a.assignment_id = p_assignment_id
1499: and exists ( select null
1500: from per_assignment_status_types s
1501: where s.assignment_status_type_id
1534: l_proc VARCHAR2(72) := g_package||'update_primary_cwk';
1535: --
1536: CURSOR get_future_primary_assignments IS
1537: SELECT assignment_id
1538: FROM per_assignments_f
1539: WHERE assignment_id NOT IN (p_assignment_id,p_new_primary_ass_id)
1540: AND person_id = p_person_id
1541: AND period_of_placement_date_start = p_pop_date_start
1542: AND effective_end_date >= l_start_date;
1659: p_start_date DATE;
1660: --
1661: CURSOR get_future_primary_assignments IS
1662: select assignment_id
1663: from per_assignments_f
1664: where assignment_id not in (P_ASSIGNMENT_ID,P_NEW_PRIMARY_ASS_ID)
1665: and period_of_service_id = P_PERIOD_OF_SERVICE_ID
1666: and effective_end_date >= P_START_DATE;
1667: --
1759: p_start_date DATE;
1760: --
1761: CURSOR select_ass_for_update IS
1762: select *
1763: from per_assignments_f
1764: where assignment_id = P_ASSIGNMENT_ID
1765: and ((p_current_ass <> 'Y'
1766: and (P_START_DATE
1767: between effective_start_date
1819: NULL;
1820: ELSE
1821: hr_utility.set_location('hr_assignment.do_primary_update',3);
1822: --
1823: update per_assignments_f
1824: set primary_flag = P_PRIMARY_FLAG
1825: , last_updated_by = P_LAST_UPDATED_BY
1826: , last_update_login = P_LAST_UPDATE_LOGIN
1827: , last_update_date = sysdate
1839: ELSE
1840: --
1841: hr_utility.set_location('hr_assignment.do_primary_update',5);
1842: --
1843: insert into per_assignments_f
1844: (
1845: ASSIGNMENT_ID
1846: ,EFFECTIVE_START_DATE
1847: ,EFFECTIVE_END_DATE
2057: ,VENDOR_EMPLOYEE_NUMBER
2058: ,VENDOR_ASSIGNMENT_NUMBER
2059: ,ASSIGNMENT_CATEGORY
2060: ,PROJECT_TITLE
2061: from per_assignments_f
2062: where assignment_id = P_ASSIGNMENT_ID
2063: and P_START_DATE
2064: between effective_start_date and effective_end_date
2065: and primary_flag <> P_PRIMARY_FLAG ;
2068: NULL; -- This Assignment Start in the Future
2069: ELSE
2070: hr_utility.set_location('hr_assignment.do_primary_update',6);
2071: --
2072: update per_assignments_f
2073: set effective_start_date = P_START_DATE
2074: , primary_flag = P_PRIMARY_FLAG
2075: , last_updated_by = P_LAST_UPDATED_BY
2076: , last_update_login = P_LAST_UPDATE_LOGIN
2092: END IF; -- (p_current_ass = 'Y')
2093: --
2094: hr_utility.set_location('hr_assignment.do_primary_update',8);
2095: --
2096: update per_assignments_f
2097: set primary_flag = P_PRIMARY_FLAG
2098: , last_updated_by = P_LAST_UPDATED_BY
2099: , last_update_login = P_LAST_UPDATE_LOGIN
2100: , last_update_date = sysdate
2184: -- assignment is not possible for contingent workers.
2185: -------------------------------------
2186: CURSOR get_candidate_primary_ass IS
2187: select to_char(a.assignment_id)
2188: from per_assignments_f a,
2189: per_assignment_status_types ast
2190: where assignment_id <> p_assignment_id
2191: and a.effective_start_date <= p_start_date
2192: and a.effective_end_date >= p_start_date
2199: a.assignment_type = 'C' and
2200: ast.per_system_status = 'ACTIVE_CWK'))
2201: and exists
2202: (select null
2203: from per_assignments_f a2
2204: where a2.assignment_id = a.assignment_id
2205: and a2.effective_end_date >= p_fpd)
2206: and not exists
2207: (select null
2204: where a2.assignment_id = a.assignment_id
2205: and a2.effective_end_date >= p_fpd)
2206: and not exists
2207: (select null
2208: from per_assignments_f a3
2209: where a3.assignment_id = a.assignment_id
2210: and a3.effective_start_date between p_start_date and p_atd
2211: and exists
2212: (select null
2418: into p_del_flag
2419: from sys.dual
2420: where exists (
2421: select null
2422: from PER_ASSIGNMENTS_F A
2423: , FND_SESSIONS S
2424: where a.assignment_id = p_assignment_id
2425: and a.assignment_type = 'E'
2426: and effective_date
2427: between a.effective_start_date and a.effective_end_date
2428: and session_id = userenv('SESSIONID')
2429: and exists
2430: (select null
2431: from PER_ASSIGNMENTS_F B
2432: where b.assignment_id = p_assignment_id
2433: and b.assignment_type = 'A'
2434: and b.effective_end_date < a.effective_start_date)
2435: );
2767: --
2768: BEGIN
2769:
2770: /* In the select below, added the join to
2771: per_assignments_f to check for the primary assignment. If
2772: it is not a primary assignment then let it get purged.
2773: This has beeen changed for Bug# 787633 */
2774:
2775: select 'Y'
2777: from sys.dual
2778: where exists (
2779: select null
2780: from PAY_US_EMP_FED_TAX_RULES_F pef,
2781: per_assignments_f paf
2782: where pef.assignment_id = p_assignment_id
2783: and paf.assignment_id = pef.assignment_id
2784: and paf.primary_flag = 'Y'
2785: and (p_mode = 'ZAP'
4107: into term_exists
4108: from sys.dual
4109: where exists
4110: (select null
4111: from per_assignments_f a
4112: , per_assignment_status_types s
4113: where a.assignment_id = p_assignment_id
4114: and a.effective_start_date < p_start_date
4115: and a.assignment_status_type_id = s.assignment_status_type_id
4160: hr_utility.set_location('peassign.call_terminate_entries',5);
4161: BEGIN
4162: select tp.end_date
4163: into p_last_standard_date
4164: from per_assignments_f a
4165: , per_time_periods tp
4166: where a.assignment_id = p_assignment_id
4167: and a.effective_end_date = p_start_date - 1
4168: and a.payroll_id = tp.payroll_id
4216: return boolean is
4217: begin
4218: select min(a.effective_start_date)
4219: into l_leading_date
4220: from per_assignments_f a
4221: , per_assignment_status_types s
4222: where a.assignment_id = p_assignment_id
4223: and (l_ignore_val_start_date = 'N' or
4224: (l_ignore_val_start_date = 'Y' and
4235: l_new_lspd := l_default_lspd;
4236: begin
4237: select tp.end_date
4238: into l_new_lspd
4239: from per_assignments_f a
4240: , per_time_periods tp
4241: where a.assignment_id = p_assignment_id
4242: and a.effective_end_date = l_default_lspd
4243: and a.payroll_id = tp.payroll_id
4364: if p_mode = 'DELETE_NEXT_CHANGE' then
4365: begin
4366: select min(a.effective_start_date)
4367: into l_new_leading_date
4368: from per_assignments_f a
4369: , per_assignment_status_types s
4370: where a.assignment_id = p_assignment_id
4371: and effective_start_date > p_validation_end_date
4372: and a.assignment_status_type_id = s.assignment_status_type_id
4426: from per_cobra_cov_enrollments e
4427: where e.assignment_id = p_assignment_id
4428: and exists
4429: (select null
4430: from per_assignments_f a
4431: where a.assignment_id = p_assignment_id
4432: and a.effective_start_date between p_sdate and p_edate
4433: and exists ( select null
4434: from per_assignment_status_types s