[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'BASE_UNIT',CINST.value))) base_unit
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_A',CINST.value))) x_cot_a
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_B',CINST.value))) x_cot_b
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_C',CINST.value))) x_cot_c
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_COT_YOUNG_AGE',CINST.value))) x_cot_young_age
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_OLD_AGE',CINST.value))) x_cot_old_age
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_COT_AGE_UNITS',CINST.value))) x_cot_age_units
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_COT_TRAINING_HOURS',CINST.value))) x_cot_training_hours
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_COT_TRAINING_UNITS',CINST.value))) x_cot_training_units
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_AP',CINST.value))) x_cot_ap
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_IMPRO',CINST.value))) x_cot_impro
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_CAT',CINST.value))) x_cot_cat
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_CDTD',CINST.value))) x_cot_cdtd
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_COT_CFP',CINST.value))) x_cot_cfp
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_IPP_LOW_RATE',CINST.value))) x_ipp_low_rate
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_IPP_MEDIUM_RATE',CINST.value))) x_ipp_medium_rate
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_IPP_HIGH_RATE',CINST.value))) x_ipp_high_rate
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_IPP_LOW_UNITS',CINST.value))) x_ipp_low_units
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_IPP_MEDIUM_UNITS',CINST.value))) x_ipp_medium_units
,max(fnd_number.canonical_to_number(decode(R.row_low_range_or_name,
'X_IPP_HIGH_UNITS',CINST.value))) x_ipp_high_units
,max(fnd_number.canonical_to_number(decode(
R.row_low_range_or_name,'X_HIRE_UNITS',CINST.value))) x_hire_units
from pay_user_tables TAB
, pay_user_rows_f R
, pay_user_columns C
, pay_user_column_instances_f CINST
where TAB.user_table_name = 'FR_D2_RATES'
and TAB.legislation_code = 'FR'
and TAB.business_group_id is null
and C.user_table_id = TAB.user_table_id
and C.legislation_code = 'FR'
and C.business_group_id is null
and C.user_column_name = 'VALUE'
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and p_effective_date between R.effective_start_date
and R.effective_end_date
and R.business_group_id is null
and R.legislation_code = 'FR'
and CINST.user_row_id = R.user_row_id
and p_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and CINST.business_group_id is null
and CINST.legislation_code = 'FR';
select business_group_id
into l_business_group_id
from hr_all_organization_units
where organization_id = p_establishment_id;
if l_employee_count > 0 then -- update headcounts
--
p_headcount_obligation := p_headcount_obligation + l_employee_count;
update_particular (p_establishment_id,
rec_emp_year.person_id,
p_1jan,
p_31dec,
l_business_group_id,
l_employee_count,
p_headcount_particular,
pcs_count,
pcs_codes);
update_count_disabled (rec_emp_year.person_id, l_list_disabled,
l_employee_count, p_count_disabled);
select org_information4
into l_hours_text
from hr_organization_information
where organization_id = p_establishment_id
and org_information_context = 'FR_ESTAB_INFO';
l_proc := 'Update_pcs_code';
select count(lookup_code)
into l_unused_number
from fnd_common_lookups
where lookup_type = 'FR_PCS_CODE'
and description = p_pcs_code;
select lookup_code
into p_pcs_code
from fnd_common_lookups
where lookup_type = 'FR_PCS_CODE'
and description = p_pcs_code;
select description
into l_unused_char
from fnd_common_lookups
where lookup_type = 'FR_PCS_CODE'
and lookup_code = p_pcs_code;
select name
into l_job_name
from per_jobs
where job_id = p_job_id;
select job_id
from per_all_assignments_f
where person_id = p_person_id
and nvl(establishment_id,-1) = p_establishment_id
and effective_start_date <= p_31dec
and effective_end_date >= p_1jan
order by primary_flag desc, effective_start_date desc;
select job_information1, name
into p_pcs_code, p_job_title
from per_jobs_v
where job_id = lid
and nvl(job_information_category,' ') = 'FR';
select nvl(max(fnd_number.canonical_to_number(pei_information2)),0)
into p_hours_training
from per_people_extra_info
where person_id = p_person_id
and nvl(pei_information_category,' ') = 'FR_PROF_TRAIN'
and nvl(pei_information1,' ') = to_char(p_year);
select max(date_start)
into l_date_start
from (select date_start
from per_periods_of_service
where person_id = p_person_id
and date_start <= p_31dec
union all
select date_start
from per_periods_of_placement
where person_id = p_person_id
and date_start <= p_31dec);
select to_number(to_char(min(effective_start_date),'YYYY'))
into p_year_became_permanent
from per_contracts_f pcf
where pcf.effective_start_date >= l_date_start
and pcf.person_id = p_person_id
and pcf.CTR_INFORMATION_CATEGORY = 'FR'
and pcf.CTR_INFORMATION2 = 'PERMANENT'
and pcf.STATUS like 'A-%';
procedure update_particular (p_establishment_id in number,
p_person_id in number,
p_1jan in date,
p_31dec in date,
p_business_group_id in number,
p_employee_count in number,
p_headcount_particular in out nocopy number,
p_pcs_count in out nocopy table_of_number,
p_pcs_codes in out nocopy table_of_Varchar)
is
l_pcs_code_text varchar2(30);
hr_utility.set_location('update_particular pcs_code='||l_pcs_code_text,10);
hr_utility.set_location('update_particular l_pcs_particular = '||l_pcs_particular, 11);
end update_particular;
l_string := l_string || 'select ''' || p_pcs_codes(i) || ''' pc, ';
l_string := 'select 0 pc, 0 ph from dual';
return 'select 0 pc, 0 ph from dual';
procedure update_count_disabled (p_person_id in number,
p_list in varchar2,
p_employee_count in number,
p_count_disabled in out nocopy varchar2)
is
l_pos integer;
l_proc :='update_count_disabled';
end update_count_disabled;
select formula_id, effective_start_date
into p_formula_id, p_formula_start_date
from ff_formulas_f
where formula_name = 'USER_CONTRACT_PRORATED'
and business_group_id = nvl(p_business_group_id,-1)
and p_effective_date between effective_start_date and effective_end_date;
select formula_id, effective_start_date
into p_formula_id, p_formula_start_date
from ff_formulas_f
where formula_name = 'TEMPLATE_CONTRACT_PRORATED'
and legislation_code = 'FR'
and p_effective_date between effective_start_date and effective_end_date;
p_blocks.delete;
select nvl(least(p_end_period,max(a.effective_end_date)),to_date('31124712','DDMMYYYY'))
into l_end_date
from per_assignment_status_types t,
per_all_assignments_f a
where a.assignment_id = p_assignment_id
and a.establishment_id = p_establishment_id
and a.effective_start_date <= p_end_period
and a.effective_end_date >= p_start_period
and a.assignment_type in ('E','C')
and t.assignment_status_type_id = a.assignment_status_type_id
and nvl(t.per_system_status,'') in ('ACTIVE_ASSIGN','SUSP_ASSIGN'
,'ACTIVE_CWK','SUSP_CWK_ASG');
select a.effective_start_date,
p.effective_start_date,
c.effective_start_date,
max(decode(sign(ptu.effective_end_date-p_end_date),
-1,ptu.effective_end_date+1,
ptu.effective_start_date)),
max(decode(sign(b.effective_end_date-p_end_date),
-1,b.effective_end_date+1,
b.effective_start_date))
into l_start_asg,
l_start_per,
l_start_ctr,
l_start_ptu,
l_start_bud
from per_all_assignments_f a,
per_all_people_f p,
per_contracts_f c,
per_person_type_usages_f ptu,
per_assignment_budget_values_f b
where a.assignment_id = p_assignment_id
and p.person_id = a.person_id
and c.contract_id(+) = a.contract_id
and ptu.person_id = p.person_id
and b.assignment_id(+) = a.assignment_id
and b.unit (+) = 'FTE'
and p_end_date between a.effective_start_date
and a.effective_end_date
and p_end_date between p.effective_start_date
and p.effective_end_date
and p_end_date between c.effective_start_date(+)
and c.effective_end_date(+)
and ptu.effective_start_date <= p_end_date
and ptu.effective_end_date >= p.effective_start_date
and b.effective_start_date(+) <= p_end_date
and b.effective_end_date(+) >= a.effective_start_date
group by a.effective_start_date,
p.effective_start_date,
c.effective_start_date;
select ppttl.user_person_type
from per_person_type_usages_f pptu,
per_person_types_tl ppttl
where pptu.person_id = l_person_id
and ppttl.person_type_id = pptu.person_type_id
and p_start_date between pptu.effective_start_date
and pptu.effective_end_date
and ppttl.language = userenv('LANG');
select per.person_type_id,
asg.assignment_status_type_id,
asg.primary_flag,
asg.employment_category,
asg.frequency,
asg.normal_hours,
ctr.type,
ctr.ctr_information5,
ctr.status,
scl.segment2,
nvl(pos.frequency,nvl(org.ORG_INFORMATION4,bus.ORG_INFORMATION4)),
nvl(pos.working_hours,
fnd_number.canonical_to_number(nvl(org.ORG_INFORMATION3,
bus.ORG_INFORMATION3))),
bud.value,
asg.assignment_type,
asg.person_id
into p_block_table(i).per_type_id,
p_block_table(i).asg_status,
p_block_table(i).asg_primary,
p_block_table(i).asg_employment_category,
p_block_table(i).asg_freq,
p_block_table(i).asg_hours,
p_block_table(i).ctr_type,
p_block_table(i).ctr_fr_person_replaced,
p_block_table(i).ctr_status,
p_block_table(i).ass_employee_category,
p_block_table(i).asg_full_time_freq,
p_block_table(i).asg_full_time_hours,
p_block_table(i).asg_fte_value,
p_block_table(i).asg_type,
l_person_id
from per_all_people_f per,
per_contracts_f ctr,
hr_soft_coding_keyflex scl,
per_all_positions pos,
hr_organization_information org,
hr_organization_information bus,
per_assignment_budget_values_f bud,
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_start_date between asg.effective_start_date
and asg.effective_end_date
and per.person_id = asg.person_id
and p_start_date between per.effective_start_date
and per.effective_end_date
and ctr.contract_id (+) = asg.contract_id
and ctr.ctr_information_category (+) = 'FR'
and p_start_date between ctr.effective_start_date (+)
and ctr.effective_end_date (+)
and scl.soft_coding_keyflex_id (+) = asg.soft_coding_keyflex_id
and pos.position_id (+) = asg.position_id
and org.organization_id (+) = asg.organization_id
and org.org_information_context (+) || '' = 'Work Day Information'
and bus.organization_id (+) = asg.business_group_id
and bus.org_information_context (+) || '' = 'Work Day Information'
and bud.assignment_id (+) = asg.assignment_id
and p_start_date between bud.effective_start_date (+)
and bud.effective_end_date (+)
and bud.unit (+) = 'FTE';