[Home] [Help]
17: --
18: cursor supervisor
19: is
20: select 'Y'
21: from per_assignments_f paf
22: where paf.assignment_type in ('E','C')
23: and paf.supervisor_id = p_person_id
24: and p_new_start_date > paf.effective_start_date
25: and paf.effective_end_date >= p_old_start_date ;
273: select 'y' from dual where exists
274: (SELECT NULL
275: FROM pay_payroll_actions pac,
276: pay_assignment_actions act,
277: per_assignments_f asg
278: WHERE asg.person_id = p_person_id
279: AND act.assignment_id = asg.assignment_id
280: AND asg.assignment_type = p_type
281: AND pac.payroll_action_id = act.payroll_action_id
378: -- only need to test the first assignment row
379: --
380: cursor assignment_pds is
381: select a.payroll_id , a.supervisor_id
382: from per_assignments_f a,
383: per_periods_of_service p
384: where a.period_of_service_id = p.period_of_service_id
385: and p.period_of_service_id = p_pds_or_pdp_id
386: and p_type= 'E'
387: and p.date_start = a.effective_start_date;
388: --
389: cursor assignment_pdp is
390: select a.payroll_id , a.supervisor_id
391: from per_assignments_f a,
392: per_periods_of_placement p
393: where a.period_of_placement_date_start = p.date_start
394: and a.person_id = p.person_id
395: and p.period_of_placement_id = p_pds_or_pdp_id
455: select 'x' from dual where exists
456: (select 1
457: from per_spinal_point_placements_f sp,
458: per_periods_of_service p,
459: per_assignments_f a
460: where a.person_id = p_person_id
461: and a.period_of_service_id = p.period_of_service_id
462: and p.period_of_service_id = p_pds_or_pdp_id
463: and p_type = 'E'
470: select 'x' from dual where exists
471: (select 1
472: from per_spinal_point_placements_f sp,
473: per_periods_of_placement p,
474: per_assignments_f a
475: where a.person_id = p_person_id
476: and a.period_of_placement_date_start = p.date_start
477: and p.period_of_placement_id = p_pds_or_pdp_id
478: and p_type = 'C'
521: select 'x' from dual where exists
522: (select 1
523: from pay_grade_rules_f pgr,
524: per_periods_of_placement p,
525: per_assignments_f a
526: where a.person_id = p_person_id
527: and a.period_of_placement_date_start = p.date_start
528: and p.period_of_placement_id = p_pds_or_pdp_id
529: and a.assignment_type = p_type
563: select 'x' from dual where exists
564: (select 1
565: from PAY_COST_ALLOCATIONS_F ca,
566: per_periods_of_service p,
567: per_assignments_f a
568: where a.person_id = p_person_id
569: and a.period_of_service_id = p.period_of_service_id
570: and p.period_of_service_id = p_pds_or_pdp_id
571: and p_type = 'E'
577: select 'x' from dual where exists
578: (select 1
579: from PAY_COST_ALLOCATIONS_F ca,
580: per_periods_of_placement p,
581: per_assignments_f a
582: where a.person_id = p_person_id
583: and a.period_of_placement_date_start = p.date_start
584: and p.period_of_placement_id = p_pds_or_pdp_id
585: and p_type = 'C'
628: select 'x' from dual where exists
629: (select 1
630: from per_assignment_budget_values_f bud,
631: per_periods_of_service p,
632: per_assignments_f a
633: where a.person_id = p_person_id
634: and a.period_of_service_id = p.period_of_service_id
635: and p.period_of_service_id = p_pds_or_pdp_id
636: and p_type = 'E'
642: select 'x' from dual where exists
643: (select 1
644: from per_assignment_budget_values_f bud,
645: per_periods_of_placement p,
646: per_assignments_f a
647: where a.person_id = p_person_id
648: and a.period_of_placement_date_start = p.date_start
649: and p.period_of_placement_id = p_pds_or_pdp_id
650: and p_type = 'C'
764: cursor csr_asg_status is
765: select 'x' from dual where exists
766: (select 1
767: from per_assignments a
768: ,per_assignments_f f
769: where f.effective_start_date between p_earlier_date and p_later_date
770: and f.effective_start_date <> p_old_start_date
771: and f.assignment_id = a.assignment_id
772: and a.assignment_type = p_type
799: ,p_old_start_date DATE) is
800: cursor csr_asg_change is
801: select 'x' from dual where exists
802: (select 1
803: from per_assignments_f f
804: where f.effective_start_date between p_earlier_date and p_later_date
805: and f.effective_start_date <> p_old_start_date
806: and f.person_id = p_person_id);
807: --
830: ,p_new_start_date DATE) is
831: cursor csr_prev_asg is
832: select 'x' from dual where exists
833: (select 1
834: from per_assignments_f f
835: where f.effective_start_date >= p_new_start_date
836: and f.effective_start_date < p_old_start_date
837: and f.assignment_type =p_type
838: and f.person_id = p_person_id);
884: pay_element_links_f el,
885: pay_element_types_f et
886: where ee.assignment_id in
887: (select assignment_id
888: from per_assignments_f asg
889: where asg.person_id = p_person_id
890: and asg.effective_start_date between l_earlier_date and l_later_date)
891: and ee.element_link_id = el.element_link_id
892: and el.element_type_id = et.element_type_id
1012: cursor csr_ssp is
1013: select placement_id
1014: from per_spinal_point_placements_f sp
1015: where assignment_id in (select a.assignment_id
1016: from per_assignments_f a
1017: where person_id = p_person_id
1018: and a.assignment_type = p_type
1019: and a.effective_start_date = p_old_start_date)
1020: and sp.effective_start_date = p_old_start_date;
1050: cursor csr_rate is
1051: select grade_rule_id
1052: from pay_grade_rules_f pgr
1053: where grade_or_spinal_point_id in (select a.assignment_id
1054: from per_assignments_f a
1055: where person_id = p_person_id
1056: and a.assignment_type = p_type
1057: and a.effective_start_date = p_old_start_date)
1058: and pgr.rate_type = 'A'
1089: cursor csr_cost is
1090: select COST_ALLOCATION_ID
1091: from PAY_COST_ALLOCATIONS_F pca
1092: where assignment_id in (select a.assignment_id
1093: from per_assignments_f a
1094: where person_id = p_person_id
1095: and a.assignment_type = p_type
1096: and a.effective_start_date = p_old_start_date)
1097: and pca.effective_start_date = p_old_start_date;
1127: cursor csr_abv is
1128: select abv.assignment_budget_value_id
1129: from per_assignment_budget_values_f abv
1130: where assignment_id in (select a.assignment_id
1131: from per_assignments_f a
1132: where person_id = p_person_id
1133: and a.assignment_type = p_type
1134: and a.effective_start_date = p_old_start_date)
1135: and abv.effective_start_date = p_old_start_date;
1214: open csr_apl_asg;
1215: loop
1216: fetch csr_apl_asg into l_assignment_id;
1217: exit when csr_apl_asg%NOTFOUND;
1218: update per_assignments_f a
1219: set a.effective_end_date = p_new_start_date - 1
1220: where a.effective_end_date =
1221: (select max(a2.effective_end_date)
1222: from per_assignments_f a2
1218: update per_assignments_f a
1219: set a.effective_end_date = p_new_start_date - 1
1220: where a.effective_end_date =
1221: (select max(a2.effective_end_date)
1222: from per_assignments_f a2
1223: where a2.assignment_id = a.assignment_id
1224: and a2.assignment_type = 'A')
1225: and a.assignment_id = l_assignment_id;
1226: if sql%rowcount <1 then
1287: select pay_proposal_id
1288: from per_pay_proposals
1289: where change_date = p_old_start_date
1290: and exists (select 1
1291: from per_assignments_f
1292: where person_id = p_person_id
1293: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1294: -- and primary_flag = 'Y'
1295: and effective_start_date = p_new_start_date
1289: where change_date = p_old_start_date
1290: and exists (select 1
1291: from per_assignments_f
1292: where person_id = p_person_id
1293: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1294: -- and primary_flag = 'Y'
1295: and effective_start_date = p_new_start_date
1296: and assignment_type = p_type);
1297: --
1301: from per_pay_proposals
1302: where change_date <= p_new_start_date
1303: and assignment_id =
1304: (select assignment_id
1305: from per_assignments_f
1306: where person_id = p_person_id
1307: and primary_flag = 'Y'
1308: and effective_start_date = p_new_start_date
1309: and assignment_type = p_type);
1353: where change_date = p_old_start_date
1354: and pay_proposal_id = l_pay_proposal_id;
1355: /* and assignment_id =
1356: (select assignment_id
1357: from per_assignments_f
1358: where person_id = p_person_id
1359: and primary_flag = 'Y'
1360: and effective_start_date = p_new_start_date
1361: and assignment_type = p_type);
1487: -- select all the assignments for the person_id starting on the Hire date.
1488: --
1489: Cursor csr_assignments(p_person_id number, p_new_effective_date date) IS
1490: select distinct paf.assignment_id
1491: from per_assignments_f paf
1492: where paf.person_id = p_person_id
1493: and paf.date_probation_end is not null
1494: and paf.effective_start_date = p_new_effective_date;
1495: --
1496: -- select any datetrack updations on given Assignment_id.
1497: --
1498: Cursor csr_asg_updates(p_assignment_id number, p_new_effective_date date) IS
1499: select paf.effective_start_date
1500: from per_assignments_f paf
1501: where paf.assignment_id = p_assignment_id
1502: and paf.effective_start_date >= p_new_effective_date
1503: order by paf.effective_start_date;
1504: --
1507: Cursor csr_probation_details(p_assignment_id number, p_effective_start_date date) IS
1508: select paf.date_probation_end
1509: ,paf.probation_period
1510: ,paf.probation_unit
1511: from per_assignments_f paf
1512: where paf.assignment_id = p_assignment_id
1513: and paf.effective_start_date = p_effective_start_date;
1514: --
1515: -- local variables.
1588: --
1589: else
1590: --
1591: hr_utility.set_location('date probation end is updated', 60);
1592: update per_assignments_f paf
1593: set paf.date_probation_end = l_new_date_probation_end
1594: where paf.assignment_id = l_assignment_id
1595: and paf.effective_start_date = l_effective_start_date;
1596: --
1621: --
1622: cursor csr_extra_details IS
1623: select 'Y'
1624: from pqp_assignment_attributes_f paa
1625: ,per_assignments_f paf
1626: where paf.person_id = p_person_id
1627: and paa.assignment_id = paf.assignment_id
1628: and paf.effective_start_date = trunc(p_old_start_date)
1629: and ( paa.effective_start_date between p_old_start_date+1 and p_new_start_date
2400: where p.effective_start_date = l_old_start_date
2401: and p.effective_end_date >= l_new_start_date
2402: and exists
2403: (select 1
2404: from per_assignments_f a
2405: where p.assignment_id = a.assignment_id
2406: and a.assignment_type = p_update_type
2407: and a.person_id = p_person_id);
2408: --
2434: --
2435: --first update ALL matching cases of the period_of_placement_date_start for integrity
2436: --
2437: if p_update_type='C' then
2438: update per_assignments_f a
2439: set a.period_of_placement_date_start = l_new_start_date
2440: where a.period_of_placement_date_start = l_old_start_date
2441: and a.assignment_type = p_update_type
2442: and a.person_id = p_person_id;
2446: hr_utility.raise_error;
2447: end if;
2448: end if;
2449: --
2450: update per_assignments_f a
2451: set a.effective_start_date = l_new_start_date
2452: where a.effective_start_date = l_old_start_date
2453: and a.assignment_type = p_update_type
2454: and a.person_id = p_person_id;
2461: -- 115.32 115.34 (START)
2462: --
2463: -- Update the EED for ASG records immediately before the updated ESD
2464: --
2465: UPDATE per_assignments_f A
2466: SET A.effective_end_date = (l_new_start_date - 1)
2467: WHERE A.effective_end_date = (l_old_start_date - 1)
2468: AND (l_new_start_date - 1) >= A.effective_start_date
2469: AND A.assignment_type = p_update_type
2491: set paa.effective_start_date = l_new_start_date
2492: where paa.effective_start_date = l_old_start_date
2493: and paa.assignment_id in
2494: (select paf.assignment_id
2495: from per_assignments_f paf
2496: where paf.effective_start_date = l_new_start_date
2497: and paf.person_id = p_person_id);
2498: --
2499: -- Fix for bug 3738058 ends here.