[Home] [Help]
253:
254: -- Cursor to get 'Work At Home' flag of current assignment.
255: cursor csr_wrk_at_home is
256: select assign.work_at_home
257: from per_all_assignments_f assign,
258: pay_assignment_actions paa
259: where paa.assignment_id = assign.assignment_id
260: and paa.assignment_action_id = p_asg_act_id
261: and assign.effective_start_date = (select max(paf.effective_start_date)
258: pay_assignment_actions paa
259: where paa.assignment_id = assign.assignment_id
260: and paa.assignment_action_id = p_asg_act_id
261: and assign.effective_start_date = (select max(paf.effective_start_date)
262: from per_all_assignments_f paf
263: where paf.assignment_id = assign.assignment_id);
264:
265: -- Cursor to get address information for the case when
266: -- person is working at home.
269: nvl(addr.add_information19,addr.region_1),
270: nvl(addr.add_information18,addr.town_or_city),
271: nvl(addr.add_information20,addr.postal_code)
272: from per_addresses addr,
273: per_all_assignments_f assign,
274: pay_assignment_actions paa
275: where paa.assignment_id = assign.assignment_id
276: and paa.assignment_action_id = p_asg_act_id
277: and addr.person_id = assign.person_id
279: and assign.effective_start_date
280: between nvl(addr.date_from, assign.effective_start_date)
281: and nvl(addr.date_to,assign.effective_start_date)
282: and assign.effective_start_date = (select max(paf.effective_start_date)
283: from per_all_assignments_f paf
284: where paf.assignment_id = assign.assignment_id);
285:
286:
287: -- Cursor to get address information for the case when
292: nvl(hrloc.loc_information18,hrloc.town_or_city),
293: nvl(hrloc.loc_information20,hrloc.postal_code)
294: from hr_locations hrloc,
295: hr_soft_coding_keyflex hrsckf,
296: per_all_assignments_f assign,
297: pay_assignment_actions paa
298: where paa.assignment_id = assign.assignment_id
299: and paa.assignment_action_id = p_asg_act_id
300: and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
299: and paa.assignment_action_id = p_asg_act_id
300: and assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
301: and nvl(hrsckf.segment18,assign.location_id) = hrloc.location_id
302: and assign.effective_start_date = (select max(paf.effective_start_date)
303: from per_all_assignments_f paf
304: where paf.assignment_id = assign.assignment_id);
305:
306: l_asg_wrk_at_home varchar2(1);
307: l_ele_classification pay_element_classifications.classification_name%type;
1954: p_assignment_id number) IS
1955: SELECT target.SEGMENT4
1956: FROM /* route for SCL keyflex - assignment level */
1957: hr_soft_coding_keyflex target,
1958: per_all_assignments_f ASSIGN
1959: WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
1960: AND ASSIGN.effective_end_date
1961: AND ASSIGN.assignment_id = p_assignment_id
1962: AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
2289: ,cp_assignment_id NUMBER) IS
2290: SELECT nvl(perj.JOB_INFORMATION3, 'EX')
2291: FROM per_jobs perj,
2292: per_jobs_tl perjtl,
2293: per_all_assignments_f paa
2294: WHERE cp_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
2295: AND paa.assignment_id = cp_assignment_id
2296: AND paa.job_id = perj.job_id
2297: AND paa.job_id = perjtl.job_id
2332: group id provided */
2333: CURSOR get_assignment_info(cp_assignment_id NUMBER
2334: ,cp_date DATE) IS
2335: SELECT person_id, payroll_id
2336: FROM per_all_assignments_f
2337: WHERE assignment_id = cp_assignment_id
2338: AND cp_date BETWEEN effective_start_date AND effective_end_date;
2339:
2340: -- Get time_definition_id corresponding to Overtime Week Id
2654: pea.segment3 ER_Account_Number
2655: FROM
2656: pay_assignment_actions paa,
2657: pay_action_information pai,
2658: per_all_assignments_f paf,
2659: per_all_people_f ppf,
2660: pay_external_accounts pea,
2661: pay_org_payment_methods_f popm
2662: WHERE paa.assignment_action_id = cp_chk_assactid