[Home] [Help]
67: -- Get a list of employees for this establishment
68: -- and accrual plan, if any
69: -- Build the query dynamically
70: l_select := 'select distinct per.person_id emp_id, estab.organization_id estab_id, per.full_name name, per.employee_number empnum from per_all_people_f per, per_all_assignments_f ass, pay_element_entries_f ent, '||
71: 'pay_accrual_plans acc, hr_all_organization_units estab';
72: l_where := ' where per.person_id = ass.person_id and per.business_group_id = '||p_business_group_id||' and '|| '''' ||l_date_to|| ''''||' between per.effective_start_date '
73: ||'and per.effective_end_date and estab.business_group_id = per.business_group_id and ass.establishment_id = estab.organization_id '
74: ||'and ass.effective_end_date >= '|| '''' ||l_date_from|| ''''||' and ass.effective_start_date <= '|| '''' ||l_date_to|| ''''||' and ass.assignment_id = ent.assignment_id '
75: ||'and ent.element_type_id = acc.accrual_plan_element_type_id and acc.accrual_plan_id = '||p_accrual_plan_id||' and ent.effective_end_date >= '|| '''' ||l_date_from|| ''''||' and ent.effective_start_date <= '|| '''' ||l_date_to|| ''''||'';
341: 'acc_hdr_enrol_start',
342: to_char(ent.effective_start_date, 'dd-Mon-yy') ,
343: 'acc_hdr_enrol_end',
344: decode(ent.effective_end_date, hr_general.end_of_time,null, to_char(ent.effective_end_date, 'dd-Mon-yy'))
345: from pay_accrual_plans acc,
346: pay_element_entries_f ent,
347: per_all_assignments_f asg
348: where acc.accrual_plan_id = p_accrual_plan_id
349: and asg.person_id = p_emp_id
364: select greatest(ent.effective_start_date, per.original_date_of_hire) co_start_date
365: from pay_element_entries_f ent,
366: per_all_assignments_f asg,
367: per_all_people_f per,
368: pay_accrual_plans acc
369: where acc.accrual_plan_id = p_accrual_plan_id
370: and per.person_id = p_emp_id
371: and asg.person_id = per.person_id
372: and per.effective_end_date = (select max(effective_end_date)
417: abs.abs_information20 abs_out_wkg_hrs,
418: decode(abs.date_projected_start, null, 'N', 'Y') abs_proj_yn
419: from per_absence_attendances abs,
420: per_absence_attendance_types abt,
421: pay_accrual_plans pap,
422: po_vendors po
423: where abs.absence_attendance_type_id = abt.absence_attendance_type_id
424: and abt.input_value_id = pap.pto_input_value_id
425: and abs.person_id = p_emp_id
436: to_char(pee.effective_start_date, 'dd-Mon-yy') adj_start,
437: to_char(pee.effective_end_date, 'dd-Mon-yy') adj_end,
438: round(fnd_number.canonical_to_number(pev.screen_entry_value)*fnd_number.canonical_to_number(ncr.add_or_subtract), 2) adj_hours,
439: hr_general.decode_lookup('ADD_SUBTRACT',ncr.add_or_subtract) add_or_subtract
440: from pay_accrual_plans pap,
441: pay_net_calculation_rules ncr,
442: pay_element_entries_f pee,
443: pay_element_entry_values_f pev,
444: pay_input_values_f iv,