[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select VARCHAR2(500);
select lookup_code,meaning
from hr_lookups
where lookup_type='FR_DIF_STMT_LOOKUP_CODE'
and lookup_code <> 'TOTAL'; -- added for 5111065
select lookup_code,meaning
from hr_lookups
where lookup_type='NAME_TRANSLATIONS'
and lookup_code = 'TOTAL';
xml_table.delete;
hr_utility.set_location('Deleted rows in xml table', 20);
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, '||
'pay_accrual_plans acc, hr_all_organization_units estab';
OPEN ref_csr_emp_list FOR l_select||l_where||l_where_emp||l_where_estab||l_order_by;
select lookup_code,meaning
from hr_lookups
where lookup_type='FR_DIF_STMT_LOOKUP_CODE'
and lookup_code = c_lookup_code;
Select 'er_hdr_estab_name',
estab_tl.name ,
'er_hdr_estab_addr_compl',
estab_loc.address_line_2 ,
'er_hdr_estab_addr_nstreet',
estab_loc.address_line_1,
'er_hdr_estab_addr_town',
estab_loc.region_3,
'er_hdr_estab_addr_zip',
estab_loc.postal_code,
'er_hdr_estab_addr_towncity',
estab_loc.town_or_city,
'er_hdr_estab_country',
estab_ft.nls_territory,
'er_hdr_comp_name',
comp_tl.name,
'er_hdr_comp_addr_compl',
comp_loc.address_line_2,
'er_hdr_comp_addr_nstreet',
comp_loc.address_line_1,
'er_hdr_comp_addr_town',
comp_loc.region_3,
'er_hdr_comp_addr_zip',
comp_loc.postal_code,
'er_hdr_comp_addr_towncity',
comp_loc.town_or_city,
'er_hdr_comp_country',
comp_ft.nls_territory,
'er_hdr_estab_siret',
estab_info.org_information2
from hr_all_organization_units estab,
hr_all_organization_units_tl estab_tl,
hr_all_organization_units comp,
hr_all_organization_units_tl comp_tl,
hr_organization_information estab_info,
hr_locations_all estab_loc,
hr_locations_all comp_loc,
fnd_territories estab_ft,
fnd_territories comp_ft
where estab.organization_id = p_estab_id
and estab.business_group_id = p_business_group_id
and estab_tl.organization_id = estab.organization_id
and estab_tl.language = userenv('LANG')
and estab_info.organization_id(+) = estab.organization_id
and estab_info.org_information_context(+) = 'FR_ESTAB_INFO'
and comp.organization_id(+) = estab_info.org_information1
and comp.business_group_id(+) = p_business_group_id
and comp_tl.organization_id(+) = comp.organization_id
and comp_tl.language(+) = userenv('LANG')
and estab_loc.location_id(+) = estab.location_id
and estab_loc.style(+) ='FR'
and comp_loc.location_id(+) = comp.location_id
and comp_loc.style(+) ='FR'
and estab_ft.territory_code(+) = estab_loc.country
and comp_ft.territory_code(+) = comp_loc.country;
Select 'emp_hdr_full_name' ,
per.full_name ,
'emp_hdr_empnum',
per.employee_number,
'emp_hdr_addr_compl',
per_addr.address_line2,
'emp_hdr_addr_nstreet',
per_addr.address_line1,
'emp_hdr_addr_town',
per_addr.region_3,
'emp_hdr_addr_zip',
per_addr.postal_code,
'emp_hdr_addr_towncity',
per_addr.town_or_city,
'emp_hdr_country',
per_ft.nls_territory ,
'emp_hdr_hiredate',
to_char(per.original_date_of_hire, 'dd-Mon-yy'),
'emp_hdr_adj_svc_date',
null, -- adjusted service date
'emp_hdr_term_date',
decode(serv.actual_termination_date, hr_general.end_of_time, null, to_char(serv.actual_termination_date, 'dd-Mon-yy')),
'emp_hdr_coll_aggr',
col_agr.name,
'emp_hdr_asg_catg',
hr_general.decode_lookup('EMP_CAT', asg.employment_category),
asg.assignment_id,
asg.payroll_id
from per_all_people_f per,
per_addresses per_addr,
fnd_territories per_ft,
per_periods_of_service serv,
per_all_assignments_f asg,
per_collective_agreements col_agr
where per.person_id = p_emp_id
and asg.person_id = per.person_id
and per.effective_end_date = (select max(effective_end_date)
from per_all_people_f
where person_id = per.person_id
and effective_end_date >= p_date_from)
and asg.effective_end_date =(select max(effective_end_date)
from per_all_assignments_f
where person_id = per.person_id
and effective_end_date >= p_date_from)
and serv.person_id = per.person_id
and (serv.actual_termination_date is null
or serv.actual_termination_date= (select greatest(actual_termination_date)
from per_periods_of_service
where person_id = per.person_id
and actual_termination_date > per.effective_start_date))
and per_addr.person_id(+) = per.person_id
and per_addr.primary_flag(+) = 'Y'
and per_ft.territory_code(+) = per_addr.country
and col_agr.collective_agreement_id(+) = asg.collective_agreement_id;
select to_char(ctr.effective_start_date, 'dd-Mon-yy') ctr_hdr_start,
decode(ctr.effective_end_date, hr_general.end_of_time, null,to_char(ctr.effective_end_date, 'dd-Mon-yy')) ctr_hdr_end,
hr_general.decode_lookup('CONTRACT_TYPE',ctr.type) ctr_hdr_type,
hr_general.decode_lookup('FR_CONTRACT_CATEGORY',ctr_information2) ctr_hdr_category,
decode(ctr.ctr_information12, 'HOUR', ctr.ctr_information11, to_char(asg.normal_hours)) ctr_hdr_hours
from per_contracts_f ctr,
per_all_assignments_f asg
where asg.person_id = p_emp_id
and asg.effective_start_date <= p_date_to
and asg.effective_end_date >= p_date_from
and asg.contract_id(+) = ctr.contract_id
and ctr.effective_end_date =(select greatest(effective_end_date)
from per_contracts_f
where contract_id= ctr.contract_id
and effective_end_date >= p_date_from)
and ctr_information_category(+) = 'FR';
select 'acc_hdr_plan_name',
acc.accrual_plan_name ,
'acc_hdr_enrol_start',
to_char(ent.effective_start_date, 'dd-Mon-yy') ,
'acc_hdr_enrol_end',
decode(ent.effective_end_date, hr_general.end_of_time,null, to_char(ent.effective_end_date, 'dd-Mon-yy'))
from pay_accrual_plans acc,
pay_element_entries_f ent,
per_all_assignments_f asg
where acc.accrual_plan_id = p_accrual_plan_id
and asg.person_id = p_emp_id
and ent.assignment_id = asg.assignment_id
and ent.element_type_id = acc.accrual_plan_element_type_id
and ent.effective_start_date = (select max(effective_start_date)
from pay_element_entries_f
where assignment_id = ent.assignment_id
and element_type_id = acc.accrual_plan_element_type_id
and effective_start_date <= p_date_to
and effective_end_date >= p_date_from);
select greatest(ent.effective_start_date, per.original_date_of_hire) co_start_date
from pay_element_entries_f ent,
per_all_assignments_f asg,
per_all_people_f per,
pay_accrual_plans acc
where acc.accrual_plan_id = p_accrual_plan_id
and per.person_id = p_emp_id
and asg.person_id = per.person_id
and per.effective_end_date = (select max(effective_end_date)
from per_all_people_f
where person_id = per.person_id
and effective_end_date >= p_date_from)
and asg.effective_end_date =(select max(effective_end_date)
from per_all_assignments_f
where person_id = per.person_id
and effective_end_date >= p_date_from)
and ent.assignment_id = asg.assignment_id
and ent.element_type_id = acc.accrual_plan_element_type_id
and ent.effective_start_date = (select max(effective_start_date)
from pay_element_entries_f
where assignment_id = ent.assignment_id
and element_type_id = acc.accrual_plan_element_type_id
and effective_start_date <= p_date_to
and effective_end_date >= p_date_from);
select asg.effective_start_date start_date,
substr(hruserdt.get_table_value(p_business_group_id, 'FR_CIPDZ', 'CIPDZ',nvl(asg.employment_category,'FR'),p_date_from),1,1) asg_catg,
asg.payroll_id payroll_id,
decode(ctr.ctr_information12, 'HOUR', fnd_number.canonical_to_number(ctr.ctr_information11), asg.normal_hours) wkg_hours
from per_all_assignments_f asg,
per_contracts_f ctr
where asg.person_id= p_emp_id
and asg.effective_end_date >= p_date_from
and asg.effective_start_date <= p_date_to
and asg.contract_id = ctr.contract_id
and asg.effective_start_date between ctr.effective_start_date and ctr.effective_end_date
order by asg.effective_start_date asc;
select to_char(abs.date_start, 'dd-Mon-yy') abs_start,
to_char(abs.date_end, 'dd-Mon-yy') abs_end,
abs.absence_hours abs_duration,
hr_general.decode_lookup('FR_TRAINING_LEAVE_CATEGORY',abs.abs_information1) abs_trg_catg,
abs.abs_information2 abs_course,
po.vendor_name abs_trg_prov,
hr_general.decode_lookup('FR_TRAINING_TYPE',abs.abs_information4) abs_trg_type,
abs.abs_information17 abs_reference,
hr_general.decode_lookup('FR_LEGAL_TRG_CATG',abs.abs_information19) abs_leg_catg,
abs.abs_information20 abs_out_wkg_hrs,
decode(abs.date_projected_start, null, 'N', 'Y') abs_proj_yn
from per_absence_attendances abs,
per_absence_attendance_types abt,
pay_accrual_plans pap,
po_vendors po
where abs.absence_attendance_type_id = abt.absence_attendance_type_id
and abt.input_value_id = pap.pto_input_value_id
and abs.person_id = p_emp_id
and abs.abs_information_category = 'FR_TRAINING_ABSENCE'
and abs.date_start between p_date_from and p_date_to
and pap.accrual_plan_id = p_accrual_plan_id
and po.vendor_id = fnd_number.canonical_to_number(abs.abs_information3);
select ele.element_name adj_element,
to_char(pee.effective_start_date, 'dd-Mon-yy') adj_start,
to_char(pee.effective_end_date, 'dd-Mon-yy') adj_end,
round(fnd_number.canonical_to_number(pev.screen_entry_value)*fnd_number.canonical_to_number(ncr.add_or_subtract), 2) adj_hours,
hr_general.decode_lookup('ADD_SUBTRACT',ncr.add_or_subtract) add_or_subtract
from pay_accrual_plans pap,
pay_net_calculation_rules ncr,
pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f iv,
pay_element_types_f ele
where pap.accrual_plan_id = p_accrual_plan_id
and pee.assignment_id = c_assignment_id
and pee.element_entry_id = pev.element_entry_id
and pev.input_value_id = ncr.input_value_id
and pap.accrual_plan_id = ncr.accrual_plan_id
and ncr.input_value_id not in (pap.co_input_value_id,pap.pto_input_value_id)
and pev.screen_entry_value is not null
and pev.effective_start_date = pee.effective_start_date
and pev.effective_end_date = pee.effective_end_date
and iv.input_value_id = ncr.input_value_id
and c_end_date between iv.effective_start_date and iv.effective_end_date
and ele.element_type_id = iv.element_type_id
and c_end_date between ele.effective_start_date and ele.effective_end_date
and pee.element_type_id = iv.element_type_id
and exists
(select null
from pay_element_entry_values_f pev1,
pay_input_values_f piv2
where pev1.element_entry_id = pev.element_entry_id
and pev1.input_value_id = ncr.date_input_value_id
and pev1.effective_start_date = pev.effective_start_date
and pev1.effective_end_date = pev.effective_end_date
and ncr.date_input_value_id = piv2.input_value_id
and pee.element_type_id = piv2.element_type_id
and c_end_date between piv2.effective_start_date
and piv2.effective_end_date
and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
pev1.screen_entry_value, Null))
between c_start_date and c_end_date);