[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:
3514: p_fed_information30 in varchar2 ) is
3515:
3516: cursor csr_asg_rec is
3517: select assignment_id
3518: from PER_ASSIGNMENTS_F
3519: where assignment_id = p_assignment_id
3520: and p_effective_start_date between effective_start_date
3521: and effective_end_date
3522: for update of assignment_id nowait;
4001: p_sta_information30 in varchar2 ) is
4002:
4003: cursor csr_asg_rec is
4004: select assignment_id
4005: from PER_ASSIGNMENTS_F
4006: where assignment_id = p_assignment_id
4007: and p_effective_start_date between effective_start_date
4008: and effective_end_date
4009: for update of assignment_id nowait;
4482: p_cnt_information30 in varchar2 ) is
4483:
4484: cursor csr_asg_rec is
4485: select assignment_id
4486: from PER_ASSIGNMENTS_F
4487: where assignment_id = p_assignment_id
4488: and p_effective_start_date between effective_start_date
4489: and effective_end_date
4490: for update of assignment_id nowait;
4948:
4949:
4950: cursor csr_asg_rec is
4951: select assignment_id
4952: from PER_ASSIGNMENTS_F
4953: where assignment_id = p_assignment_id
4954: and p_effective_start_date between effective_start_date
4955: and effective_end_date
4956: for update of assignment_id nowait;
5564:
5565: cursor csr_get_asg_gre (p_assignment number)is
5566: select hsck.segment1
5567: from hr_soft_coding_keyflex hsck,
5568: per_assignments_f paf
5569: where paf.assignment_id = p_assignment
5570: and p_effective_start_date between paf.effective_start_date
5571: and paf.effective_end_date
5572: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
6216: cursor c_tax_unit_id(p_assignment_id in number,
6217: p_session_date in date) is
6218: select to_number(segment1) tax_unit_id
6219: from hr_soft_coding_keyflex a,
6220: per_assignments_f b
6221: where b.assignment_id = p_assignment_id
6222: and b.soft_coding_keyflex_id = a.soft_coding_keyflex_id
6223: and p_session_date between b.effective_start_date and b.effective_end_date ;
6224:
6232: and UE.legislation_code = 'US' ;
6233:
6234: cursor c_max_asg_eff_st_date(p_assignment_id in number) is
6235: select max(effective_start_date)
6236: from per_assignments_f paf,
6237: per_assignment_status_types past
6238: where paf.assignment_id = p_assignment_id
6239: and paf.assignment_status_type_id = past.assignment_status_type_id
6240: and past.per_system_status = 'ACTIVE_ASSIGN'
6244: and past.legislation_code = 'US')
6245: OR (past.legislation_code is null
6246: and exists
6247: (select 'x'
6248: from per_assignments_f paf_i
6249: where paf_i.assignment_id = p_assignment_id
6250: and paf_i.business_group_id = past.business_group_id)
6251: )
6252: )
6264: p_session_date in date) is
6265: select max(effective_date)
6266: from pay_payroll_actions ppa,
6267: pay_assignment_actions paa,
6268: per_assignments_f paf
6269: where paf.assignment_id = p_assignment_id
6270: and paf.assignment_id = paa.assignment_id
6271: and paa.payroll_action_id = ppa.payroll_action_id
6272: and ppa.action_type in ('R','Q','B','V','I')
6275:
6276: Cursor c_ckeck_rehire(p_assignment_id in number,
6277: p_session_date in date) is
6278: select 'Y'
6279: from per_assignments_f paf_o,
6280: per_assignment_status_types past
6281: where paf_o.assignment_id = p_assignment_id
6282: and paf_o.assignment_status_type_id = past.assignment_status_type_id
6283: and past.per_system_status = 'ACTIVE_ASSIGN'
6287: and past.legislation_code = 'US')
6288: OR (past.legislation_code is null
6289: and exists
6290: (select 'x'
6291: from per_assignments_f paf_a
6292: where paf_a.assignment_id = p_assignment_id
6293: and paf_a.business_group_id = past.business_group_id)
6294: )
6295: )
6296:
6297: and exists
6298: (
6299: select distinct paf_i.assignment_id
6300: from per_assignments_f paf_i,
6301: per_assignments_f paf_term
6302: where paf_i.person_id = paf_o.person_id
6303: and paf_i.person_id = paf_term.person_id
6304: and paf_i.assignment_id > paf_term.assignment_id
6297: and exists
6298: (
6299: select distinct paf_i.assignment_id
6300: from per_assignments_f paf_i,
6301: per_assignments_f paf_term
6302: where paf_i.person_id = paf_o.person_id
6303: and paf_i.person_id = paf_term.person_id
6304: and paf_i.assignment_id > paf_term.assignment_id
6305: and paf_i.effective_start_date >= paf_term.effective_end_date
6309: ) ;
6310:
6311: Cursor c_get_person_id(p_assignment_id in number) is
6312: select distinct paf.person_id
6313: from per_assignments_f paf
6314: where paf.assignment_id = p_assignment_id ;
6315:
6316: Cursor c_get_all_assignments(p_person_id in number,
6317: p_session_date in date) is
6315:
6316: Cursor c_get_all_assignments(p_person_id in number,
6317: p_session_date in date) is
6318: select distinct paf.assignment_id
6319: from per_assignments_f paf
6320: where paf.person_id = p_person_id
6321: and ( trunc(paf.effective_end_date,'Y') = trunc(p_session_date,'Y')
6322: or (trunc(paf.effective_start_date,'Y') = trunc(p_session_date,'Y')
6323: and trunc(paf.effective_end_date,'Y') > trunc(p_session_date,'Y'))) ;
6674: */
6675:
6676: cursor csr_get_max_assign_end_dt is
6677: select max(effective_end_date)
6678: from per_assignments_f
6679: where assignment_id = p_assignment_id;
6680:
6681:
6682: l_max_assign_end_dt date := NULL;
7735: /* cursor to get the next location */
7736:
7737: cursor csr_get_next_location (p_next_eff_date date) is
7738: select paf.location_id, paf.effective_end_date
7739: from PER_ASSIGNMENTS_F paf
7740: where paf.assignment_id = p_assignment_id
7741: and p_next_eff_date between paf.effective_start_date
7742: and paf.effective_end_date;
7743:
7761:
7762: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
7763: select nvl(hsck.segment18, paf.location_id)
7764: from HR_SOFT_CODING_KEYFLEX hsck,
7765: PER_ASSIGNMENTS_F paf
7766: where paf.assignment_id = p_assignment
7767: and p_session_dt between paf.effective_start_date
7768: and paf.effective_end_date
7769: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
8013: p_end_date date) is
8014: select paf1.location_id,
8015: paf1.effective_start_date,
8016: paf1.effective_start_date - 1
8017: from per_assignments_f paf1
8018: where paf1.assignment_id = passignment
8019: and paf1.effective_start_date >= p_start_date
8020: and paf1.effective_end_date <= p_end_date
8021: order by 2;
8060: /* cursor to get the next location */
8061:
8062: cursor csr_get_next_location (p_next_eff_date date) is
8063: select paf.location_id
8064: from PER_ASSIGNMENTS_F paf
8065: where paf.assignment_id = p_assignment_id and
8066: p_next_eff_date between paf.effective_start_date
8067: and paf.effective_end_date;
8068:
8080:
8081: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
8082: select nvl(hsck.segment18, paf.location_id)
8083: from HR_SOFT_CODING_KEYFLEX hsck,
8084: PER_ASSIGNMENTS_F paf
8085: where paf.assignment_id = p_assignment
8086: and p_session_dt between paf.effective_start_date
8087: and paf.effective_end_date
8088: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
8101: /* cursor to get the previous location */
8102:
8103: cursor csr_get_prev_location (p_prev_eff_date date) is
8104: select paf.location_id
8105: from PER_ASSIGNMENTS_F paf
8106: where paf.assignment_id = p_assignment and
8107: p_prev_eff_date between paf.effective_start_date
8108: and paf.effective_end_date;
8109:
9090: /* cursor to get the next location */
9091:
9092: cursor csr_get_next_location (p_next_eff_date date) is
9093: select paf.location_id
9094: from PER_ASSIGNMENTS_F paf
9095: where paf.assignment_id = p_assignment_id and
9096: p_next_eff_date between paf.effective_start_date
9097: and paf.effective_end_date;
9098:
9108:
9109: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
9110: select nvl(hsck.segment18, paf.location_id)
9111: from HR_SOFT_CODING_KEYFLEX hsck,
9112: PER_ASSIGNMENTS_F paf
9113: where paf.assignment_id = p_assignment
9114: and p_session_dt between paf.effective_start_date
9115: and paf.effective_end_date
9116: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
10757: from pay_us_city_names pcn,
10758: pay_us_counties puc,
10759: pay_us_states pus,
10760: per_addresses pa,
10761: per_assignments_f paf
10762: where paf.assignment_id = p_assignment_id
10763: and p_effective_start_date between paf.effective_start_date and
10764: paf.effective_end_date
10765: and pa.person_id = paf.person_id
11117: /* Cursor to get the max effective end date of the assignment */
11118:
11119: cursor csr_asg_end_date is
11120: select max(effective_end_date)
11121: from PER_ASSIGNMENTS_F paf
11122: where paf.assignment_id = p_assignment_id;
11123:
11124: l_effective_end_date date;
11125:
11244: cursor csr_addr_get_assignment(p_person number) is
11245: select paf.assignment_id, min(paf.effective_start_date)
11246: from per_addresses pa,
11247: hr_soft_coding_keyflex hsck,
11248: per_assignments_f paf
11249: where paf.person_id = p_person
11250: and paf.assignment_type = 'E'
11251: and paf.soft_coding_keyflex_id is not null
11252: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11267: cursor csr_addr_get_assignment(p_person number) is
11268: select paf.assignment_id, min(paf.effective_start_date)
11269: from per_addresses pa,
11270: hr_soft_coding_keyflex hsck,
11271: per_assignments_f paf
11272: where paf.person_id = p_person
11273: and paf.assignment_type = 'E'
11274: and paf.soft_coding_keyflex_id is not null
11275: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11292: cursor csr_get_assignment(p_person number) is
11293: select paf.assignment_id, min(paf.effective_start_date)
11294: from per_addresses pa,
11295: hr_soft_coding_keyflex hsck,
11296: per_assignments_f paf
11297: where paf.person_id = p_person
11298: and paf.assignment_type = 'E'
11299: and paf.soft_coding_keyflex_id is not null
11300: and paf.effective_end_date = to_date('12/31/4712','MM/DD/YYYY')
11314: select 1
11315: from per_addresses pa,
11316: per_people_f ppf,
11317: hr_soft_coding_keyflex hsck,
11318: per_assignments_f paf
11319: where paf.assignment_id = p_assignment
11320: and p_session_date between paf.effective_start_date
11321: and paf.effective_end_date
11322: and paf.soft_coding_keyflex_id is not null
11335: select min(paf.effective_start_date)
11336: from per_addresses pa,
11337: per_people_f ppf,
11338: hr_soft_coding_keyflex hsck,
11339: per_assignments_f paf
11340: where paf.assignment_id = p_assignment
11341: and paf.soft_coding_keyflex_id is not null
11342: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
11343: and paf.location_id is not null
11365: from pay_us_city_names pcn,
11366: pay_us_counties puc,
11367: pay_us_states pus,
11368: per_addresses pa,
11369: per_assignments_f paf
11370: where paf.assignment_id = p_assignment
11371: and p_effective_start_date between paf.effective_start_date and
11372: paf.effective_end_date
11373: and pa.person_id = paf.person_id
11396: from pay_us_city_names pcn,
11397: pay_us_counties puc,
11398: pay_us_states pus,
11399: per_addresses pa,
11400: per_assignments_f paf
11401: where paf.assignment_id = p_assignment
11402: and p_effective_start_date between paf.effective_start_date and
11403: paf.effective_end_date
11404: and pa.person_id = paf.person_id
11422: /* instead, if cursor is NOTFOUND, we use location id */
11423: cursor csr_get_ovrd_loc(p_assignment number, p_session_dt date) is
11424: select hsck.segment18
11425: from HR_SOFT_CODING_KEYFLEX hsck,
11426: PER_ASSIGNMENTS_F paf
11427: where paf.assignment_id = p_assignment
11428: and p_session_dt between paf.effective_start_date
11429: and paf.effective_end_date
11430: and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
11437:
11438: cursor csr_max_loc_date(p_assignment_id NUMBER, p_loc_id NUMBER
11439: ,p_ef_date DATE) is
11440: select min(paf.effective_start_date) - 1
11441: from per_assignments_f paf
11442: where paf.assignment_id = p_assignment_id
11443: and paf.effective_start_date > p_ef_date
11444: and paf.location_id <> p_loc_id;
11445:
11445:
11446: cursor csr_min_loc_date(p_assignment_id NUMBER, p_loc_id NUMBER
11447: ,p_ef_date DATE) is
11448: select max(paf.effective_end_date) + 1
11449: from per_assignments_f paf
11450: where paf.assignment_id = p_assignment_id
11451: and paf.effective_end_date < p_ef_date
11452: and paf.location_id <> p_loc_id;
11453: