[Home] [Help]
615: cursor csr_get_locations (passignment number, pdefault_date date) is
616: select paf1.location_id,
617: paf1.effective_start_date,
618: paf1.effective_start_date - 1
619: from per_assignments_f paf1
620: where paf1.assignment_id = passignment
621: and paf1.effective_start_date >= pdefault_date
622: order by 2;
623:
3438: p_fed_information30 in varchar2 ) is
3439:
3440: cursor csr_asg_rec is
3441: select assignment_id
3442: from PER_ASSIGNMENTS_F
3443: where assignment_id = p_assignment_id
3444: and p_effective_start_date between effective_start_date
3445: and effective_end_date
3446: for update of assignment_id nowait;
3925: p_sta_information30 in varchar2 ) is
3926:
3927: cursor csr_asg_rec is
3928: select assignment_id
3929: from PER_ASSIGNMENTS_F
3930: where assignment_id = p_assignment_id
3931: and p_effective_start_date between effective_start_date
3932: and effective_end_date
3933: for update of assignment_id nowait;
4406: p_cnt_information30 in varchar2 ) is
4407:
4408: cursor csr_asg_rec is
4409: select assignment_id
4410: from PER_ASSIGNMENTS_F
4411: where assignment_id = p_assignment_id
4412: and p_effective_start_date between effective_start_date
4413: and effective_end_date
4414: for update of assignment_id nowait;
4872:
4873:
4874: cursor csr_asg_rec is
4875: select assignment_id
4876: from PER_ASSIGNMENTS_F
4877: where assignment_id = p_assignment_id
4878: and p_effective_start_date between effective_start_date
4879: and effective_end_date
4880: for update of assignment_id nowait;
5488:
5489: cursor csr_get_asg_gre (p_assignment number)is
5490: select hsck.segment1
5491: from hr_soft_coding_keyflex hsck,
5492: per_assignments_f paf
5493: where paf.assignment_id = p_assignment
5494: and p_effective_start_date between paf.effective_start_date
5495: and paf.effective_end_date
5496: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
6140: cursor c_tax_unit_id(p_assignment_id in number,
6141: p_session_date in date) is
6142: select to_number(segment1) tax_unit_id
6143: from hr_soft_coding_keyflex a,
6144: per_assignments_f b
6145: where b.assignment_id = p_assignment_id
6146: and b.soft_coding_keyflex_id = a.soft_coding_keyflex_id
6147: and p_session_date between b.effective_start_date and b.effective_end_date ;
6148:
6156: and UE.legislation_code = 'US' ;
6157:
6158: cursor c_max_asg_eff_st_date(p_assignment_id in number) is
6159: select max(effective_start_date)
6160: from per_assignments_f paf,
6161: per_assignment_status_types past
6162: where paf.assignment_id = p_assignment_id
6163: and paf.assignment_status_type_id = past.assignment_status_type_id
6164: and past.per_system_status = 'ACTIVE_ASSIGN'
6168: and past.legislation_code = 'US')
6169: OR (past.legislation_code is null
6170: and exists
6171: (select 'x'
6172: from per_assignments_f paf_i
6173: where paf_i.assignment_id = p_assignment_id
6174: and paf_i.business_group_id = past.business_group_id)
6175: )
6176: )
6188: p_session_date in date) is
6189: select max(effective_date)
6190: from pay_payroll_actions ppa,
6191: pay_assignment_actions paa,
6192: per_assignments_f paf
6193: where paf.assignment_id = p_assignment_id
6194: and paf.assignment_id = paa.assignment_id
6195: and paa.payroll_action_id = ppa.payroll_action_id
6196: and ppa.action_type in ('R','Q','B','V','I')
6199:
6200: Cursor c_ckeck_rehire(p_assignment_id in number,
6201: p_session_date in date) is
6202: select 'Y'
6203: from per_assignments_f paf_o,
6204: per_assignment_status_types past
6205: where paf_o.assignment_id = p_assignment_id
6206: and paf_o.assignment_status_type_id = past.assignment_status_type_id
6207: and past.per_system_status = 'ACTIVE_ASSIGN'
6211: and past.legislation_code = 'US')
6212: OR (past.legislation_code is null
6213: and exists
6214: (select 'x'
6215: from per_assignments_f paf_a
6216: where paf_a.assignment_id = p_assignment_id
6217: and paf_a.business_group_id = past.business_group_id)
6218: )
6219: )
6220:
6221: and exists
6222: (
6223: select distinct paf_i.assignment_id
6224: from per_assignments_f paf_i,
6225: per_assignments_f paf_term
6226: where paf_i.person_id = paf_o.person_id
6227: and paf_i.person_id = paf_term.person_id
6228: and paf_i.assignment_id > paf_term.assignment_id
6221: and exists
6222: (
6223: select distinct paf_i.assignment_id
6224: from per_assignments_f paf_i,
6225: per_assignments_f paf_term
6226: where paf_i.person_id = paf_o.person_id
6227: and paf_i.person_id = paf_term.person_id
6228: and paf_i.assignment_id > paf_term.assignment_id
6229: and paf_i.effective_start_date >= paf_term.effective_end_date
6233: ) ;
6234:
6235: Cursor c_get_person_id(p_assignment_id in number) is
6236: select distinct paf.person_id
6237: from per_assignments_f paf
6238: where paf.assignment_id = p_assignment_id ;
6239:
6240: Cursor c_get_all_assignments(p_person_id in number,
6241: p_session_date in date) is
6239:
6240: Cursor c_get_all_assignments(p_person_id in number,
6241: p_session_date in date) is
6242: select distinct paf.assignment_id
6243: from per_assignments_f paf
6244: where paf.person_id = p_person_id
6245: and ( trunc(paf.effective_end_date,'Y') = trunc(p_session_date,'Y')
6246: or (trunc(paf.effective_start_date,'Y') = trunc(p_session_date,'Y')
6247: and trunc(paf.effective_end_date,'Y') > trunc(p_session_date,'Y'))) ;
6598: */
6599:
6600: cursor csr_get_max_assign_end_dt is
6601: select max(effective_end_date)
6602: from per_assignments_f
6603: where assignment_id = p_assignment_id;
6604:
6605:
6606: l_max_assign_end_dt date := NULL;
7659: /* cursor to get the next location */
7660:
7661: cursor csr_get_next_location (p_next_eff_date date) is
7662: select paf.location_id, paf.effective_end_date
7663: from PER_ASSIGNMENTS_F paf
7664: where paf.assignment_id = p_assignment_id
7665: and p_next_eff_date between paf.effective_start_date
7666: and paf.effective_end_date;
7667:
7685:
7686: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
7687: select nvl(hsck.segment18, paf.location_id)
7688: from HR_SOFT_CODING_KEYFLEX hsck,
7689: PER_ASSIGNMENTS_F paf
7690: where paf.assignment_id = p_assignment
7691: and p_session_dt between paf.effective_start_date
7692: and paf.effective_end_date
7693: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
7937: p_end_date date) is
7938: select paf1.location_id,
7939: paf1.effective_start_date,
7940: paf1.effective_start_date - 1
7941: from per_assignments_f paf1
7942: where paf1.assignment_id = passignment
7943: and paf1.effective_start_date >= p_start_date
7944: and paf1.effective_end_date <= p_end_date
7945: order by 2;
7984: /* cursor to get the next location */
7985:
7986: cursor csr_get_next_location (p_next_eff_date date) is
7987: select paf.location_id
7988: from PER_ASSIGNMENTS_F paf
7989: where paf.assignment_id = p_assignment_id and
7990: p_next_eff_date between paf.effective_start_date
7991: and paf.effective_end_date;
7992:
8004:
8005: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
8006: select nvl(hsck.segment18, paf.location_id)
8007: from HR_SOFT_CODING_KEYFLEX hsck,
8008: PER_ASSIGNMENTS_F paf
8009: where paf.assignment_id = p_assignment
8010: and p_session_dt between paf.effective_start_date
8011: and paf.effective_end_date
8012: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
8025: /* cursor to get the previous location */
8026:
8027: cursor csr_get_prev_location (p_prev_eff_date date) is
8028: select paf.location_id
8029: from PER_ASSIGNMENTS_F paf
8030: where paf.assignment_id = p_assignment and
8031: p_prev_eff_date between paf.effective_start_date
8032: and paf.effective_end_date;
8033:
9014: /* cursor to get the next location */
9015:
9016: cursor csr_get_next_location (p_next_eff_date date) is
9017: select paf.location_id
9018: from PER_ASSIGNMENTS_F paf
9019: where paf.assignment_id = p_assignment_id and
9020: p_next_eff_date between paf.effective_start_date
9021: and paf.effective_end_date;
9022:
9032:
9033: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
9034: select nvl(hsck.segment18, paf.location_id)
9035: from HR_SOFT_CODING_KEYFLEX hsck,
9036: PER_ASSIGNMENTS_F paf
9037: where paf.assignment_id = p_assignment
9038: and p_session_dt between paf.effective_start_date
9039: and paf.effective_end_date
9040: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
10681: from pay_us_city_names pcn,
10682: pay_us_counties puc,
10683: pay_us_states pus,
10684: per_addresses pa,
10685: per_assignments_f paf
10686: where paf.assignment_id = p_assignment_id
10687: and p_effective_start_date between paf.effective_start_date and
10688: paf.effective_end_date
10689: and pa.person_id = paf.person_id
11041: /* Cursor to get the max effective end date of the assignment */
11042:
11043: cursor csr_asg_end_date is
11044: select max(effective_end_date)
11045: from PER_ASSIGNMENTS_F paf
11046: where paf.assignment_id = p_assignment_id;
11047:
11048: l_effective_end_date date;
11049:
11168: cursor csr_addr_get_assignment(p_person number) is
11169: select paf.assignment_id, min(paf.effective_start_date)
11170: from per_addresses pa,
11171: hr_soft_coding_keyflex hsck,
11172: per_assignments_f paf
11173: where paf.person_id = p_person
11174: and paf.assignment_type = 'E'
11175: and paf.soft_coding_keyflex_id is not null
11176: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11191: cursor csr_addr_get_assignment(p_person number) is
11192: select paf.assignment_id, min(paf.effective_start_date)
11193: from per_addresses pa,
11194: hr_soft_coding_keyflex hsck,
11195: per_assignments_f paf
11196: where paf.person_id = p_person
11197: and paf.assignment_type = 'E'
11198: and paf.soft_coding_keyflex_id is not null
11199: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11216: cursor csr_get_assignment(p_person number) is
11217: select paf.assignment_id, min(paf.effective_start_date)
11218: from per_addresses pa,
11219: hr_soft_coding_keyflex hsck,
11220: per_assignments_f paf
11221: where paf.person_id = p_person
11222: and paf.assignment_type = 'E'
11223: and paf.soft_coding_keyflex_id is not null
11224: and paf.effective_end_date = to_date('12/31/4712','MM/DD/YYYY')
11238: select 1
11239: from per_addresses pa,
11240: per_people_f ppf,
11241: hr_soft_coding_keyflex hsck,
11242: per_assignments_f paf
11243: where paf.assignment_id = p_assignment
11244: and p_session_date between paf.effective_start_date
11245: and paf.effective_end_date
11246: and paf.soft_coding_keyflex_id is not null
11259: select min(paf.effective_start_date)
11260: from per_addresses pa,
11261: per_people_f ppf,
11262: hr_soft_coding_keyflex hsck,
11263: per_assignments_f paf
11264: where paf.assignment_id = p_assignment
11265: and paf.soft_coding_keyflex_id is not null
11266: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11267: and paf.location_id is not null
11289: from pay_us_city_names pcn,
11290: pay_us_counties puc,
11291: pay_us_states pus,
11292: per_addresses pa,
11293: per_assignments_f paf
11294: where paf.assignment_id = p_assignment
11295: and p_effective_start_date between paf.effective_start_date and
11296: paf.effective_end_date
11297: and pa.person_id = paf.person_id
11320: from pay_us_city_names pcn,
11321: pay_us_counties puc,
11322: pay_us_states pus,
11323: per_addresses pa,
11324: per_assignments_f paf
11325: where paf.assignment_id = p_assignment
11326: and p_effective_start_date between paf.effective_start_date and
11327: paf.effective_end_date
11328: and pa.person_id = paf.person_id
11346: /* instead, if cursor is NOTFOUND, we use location id */
11347: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
11348: select hsck.segment18
11349: from HR_SOFT_CODING_KEYFLEX hsck,
11350: PER_ASSIGNMENTS_F paf
11351: where paf.assignment_id = p_assignment
11352: and p_session_dt between paf.effective_start_date
11353: and paf.effective_end_date
11354: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
11361:
11362: cursor csr_max_loc_date(p_assignment_id NUMBER, p_loc_id NUMBER
11363: ,p_ef_date DATE) is
11364: select min(paf.effective_start_date) - 1
11365: from per_assignments_f paf
11366: where paf.assignment_id = p_assignment_id
11367: and paf.effective_start_date > p_ef_date
11368: and paf.location_id <> p_loc_id;
11369:
11369:
11370: cursor csr_min_loc_date(p_assignment_id NUMBER, p_loc_id NUMBER
11371: ,p_ef_date DATE) is
11372: select max(paf.effective_end_date) + 1
11373: from per_assignments_f paf
11374: where paf.assignment_id = p_assignment_id
11375: and paf.effective_end_date < p_ef_date
11376: and paf.location_id <> p_loc_id;
11377: