[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(ptp.end_date)
into l_py_prd_per_yr
from per_time_periods PTP
where ptp.prd_information1 =
(select tperiod.prd_information1
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id)
and ptp.payroll_id = p_payroll_id;
select count(ptp.end_date)
into l_za_pay_mnth_prd_num
from per_time_periods ptp
where ptp.pay_advice_date =
(select tperiod.pay_advice_date
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id
)
and ptp.end_date <=
(select tperiod.end_date
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id
)
and ptp.payroll_id = p_payroll_id;
select global_value
into l_glb_value
from ff_globals_f
where global_name = p_global_name
and p_effective_date between effective_start_date
and effective_end_date
and legislation_code = 'ZA';
select pdb.defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name = p_bal_name
and pbd.dimension_name = p_dim_name
and pbd.legislation_code = 'ZA'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pay_balance_pkg.get_value(p_def_bal_id, --p_def_bal_id
p_asg_act_id, --assignment_action_id
null,
null,
null,
null,
null,
null,
null,
'TRUE')
from dual;
select pet.element_type_id
, piv.input_value_id
from pay_element_types_f pet
, pay_input_values_f piv
where pet.element_name = p_element_name
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
and piv.element_type_id = pet.element_type_id
and piv.name = 'Pay Value'
and p_effective_date between piv.effective_start_date
and piv.effective_end_date;
select max(paa.action_sequence)
into l_Oct_act_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date between to_date('1-10-2007','DD-MM-YYYY')
and to_date('31-10-2007','DD-MM-YYYY')
and exists (select 1
from pay_run_results prr
where element_type_id =l_ee_contr_ele_type_id
and prr.assignment_action_id = paa.assignment_action_id
) ;
hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Oct-2007');
select max(paa.action_sequence)
into l_Nov_act_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date between to_date('1-11-2007','DD-MM-YYYY')
and to_date('30-11-2007','DD-MM-YYYY')
and exists (select 1
from pay_run_results prr
where element_type_id =l_ee_contr_ele_type_id
and prr.assignment_action_id = paa.assignment_action_id
) ;
hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Nov-2007');
select max(paa.action_sequence)
into l_Dec_act_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date between to_date('1-12-2007','DD-MM-YYYY')
and to_date('31-12-2007','DD-MM-YYYY')
and exists (select 1
from pay_run_results prr
where element_type_id =l_ee_contr_ele_type_id
and prr.assignment_action_id = paa.assignment_action_id
) ;
hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Dec-2007');
select max(paa.action_sequence)
into l_Jan_act_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date between to_date('1-01-2008','DD-MM-YYYY')
and to_date('31-01-2008','DD-MM-YYYY')
and exists (select 1
from pay_run_results prr
where element_type_id =l_ee_contr_ele_type_id
and prr.assignment_action_id = paa.assignment_action_id
) ;
hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Jan-2007');
select prrv.result_value
, prr.element_entry_id
from pay_run_results prr
, pay_run_result_values prrv
where prr.assignment_action_id = p_asg_act_id
and prr.element_type_id = p_ele_type_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = p_ip_value_id;
select ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and p_reflection_date between start_date and end_date ;
select pel.element_link_id
from per_assignments_f ASG,
pay_element_links_f PEL
where P_REFLECTION_DATE between pel.effective_start_date
and pel.effective_end_date
and P_REFLECTION_DATE between asg.effective_start_date
and asg.effective_end_date
-- and pel.element_link_id = P_ELEMENT_LINK_ID
and pel.element_type_id = P_ELE_TYPE_ID
and asg.assignment_id = P_ASG_ID
and ((pel.payroll_id is not null
and asg.payroll_id = pel.payroll_id)
or (pel.link_to_all_payrolls_flag = 'Y'
and asg.payroll_id is not null)
or (pel.payroll_id is null
and pel.link_to_all_payrolls_flag = 'N'))
and (pel.organization_id = asg.organization_id
or pel.organization_id is null)
and (pel.position_id = asg.position_id
or pel.position_id is null)
and (pel.job_id = asg.job_id
or pel.job_id is null)
and (pel.grade_id = asg.grade_id
or pel.grade_id is null)
and (pel.location_id = asg.location_id
or pel.location_id is null)
and (
pel.pay_basis_id = asg.pay_basis_id
or
--
-- if EL is associated with a pay basis then this clause fails
--
pel.pay_basis_id is null and
NOT EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and P_REFLECTION_DATE between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
or
--
-- if EL is associated with a pay basis then the associated
-- PB_ID must match the PB_ID on ASG
--
pel.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and P_REFLECTION_DATE between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
or
pel.pay_basis_id is null and
asg.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and P_REFLECTION_DATE between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
)
and (pel.employment_category = asg.employment_category
or pel.employment_category is null)
and (pel.people_group_id is null
or exists
(select 1
from pay_assignment_link_usages_f palu
where palu.assignment_id = P_ASG_ID
and palu.element_link_id = pel.element_link_id
and P_REFLECTION_DATE between palu.effective_start_date
and palu.effective_end_date))
;
SELECT original_entry_id,
entry_type,
cost_allocation_keyflex_id,
updating_action_id,
updating_action_type,
comment_id,
reason,
target_entry_id,
subpriority,
date_earned,
personal_payment_method_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
label_identifier
FROM pay_element_entries_f pee
WHERE pee.element_entry_id = p_element_entry_id
AND p_eff_dt BETWEEN pee.effective_start_date
AND pee.effective_end_date;
hr_entry_api.insert_element_entry(
--
-- Common Parameters
--
p_effective_start_date => l_reflection_date,
p_effective_end_date => l_ee_end_date,
--
-- Element Entry Table
--
p_element_entry_id => l_element_entry_id,
p_original_entry_id => l_original_entry_id,
p_assignment_id => p_asg_id,
p_element_link_id => l_rtr_ee_cntr_ele_link_id,
p_creator_type => 'RR',
p_entry_type => 'E', -- for Bug 7229385
p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id,
p_updating_action_id => l_updating_action_id,
p_updating_action_type => l_updating_action_type,
p_comment_id => l_comment_id,
p_creator_id => null ,-- assignemnt_action_id of retropay run goes here
p_reason => l_reason,
p_target_entry_id => null, -- for Bug 7229385
p_subpriority => l_subpriority,
p_date_earned => l_date_earned,
p_personal_payment_method_id => l_personal_payment_method_id,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
p_attribute16 => l_attribute16,
p_attribute17 => l_attribute17,
p_attribute18 => l_attribute18,
p_attribute19 => l_attribute19,
p_attribute20 => l_attribute20,
--
-- Element Entry Values Table
--
p_input_value_id1 => p_retro_ee_contr_ip_value_id,
p_entry_value1 => p_diff_ee_contr,
-- p_override_user_ent_chk varchar2 default 'N',
p_label_identifier => l_label_identifier
) ;
select start_date,end_date
into l_prev_entry_start_date, l_prev_entry_end_date
from per_time_periods
where time_period_id = p_time_prd_id ;
update pay_element_entries_f
set source_asg_action_id = p_assact_id
,source_start_date = l_prev_entry_start_date
,source_end_date = l_prev_entry_end_date
where element_entry_id = l_element_entry_id;
select assignment_id,
assignment_number
from per_all_assignments_f paaf
where payroll_id = p_payroll_id
and assignment_status_type_id in (1,3) -- pick active and terminated ( whose final process date is left) assignments
and p_effective_date between effective_start_date and effective_end_date ;
select prr.element_entry_id
from pay_run_results prr
where prr.assignment_action_id = p_asact_id
and prr.element_type_id = p_ele_type_id ;
select distinct include_or_exclude
into v_incl_sw
from hr_assignment_set_amendments
where assignment_set_id = p_asg_set_id;
select include_or_exclude
into inc_flag
from hr_assignment_set_amendments
where assignment_set_id = p_asg_set_id
and assignment_id = rec_all_asg_ids.assignment_id;
select ppa.payroll_action_id
,paa.assignment_action_id
,ppa.effective_date
,ppa.time_period_id
,paa.assignment_id
,ppa.action_type
into l_pact_id
,l_assact_id
,l_eff_date
,l_time_prd_id
,l_asg_id
,l_action_type
from pay_payroll_actions ppa
,pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.action_sequence = rec_assact(rec_count).action_seq
and paa.assignment_id = rec_assact(rec_count).assignment_id ;
select count(1)
into l_process_run_count
from pay_element_entries_f
where source_asg_action_id = l_assact_id
and element_type_id in (l_retro_ee_contr_ele_type_id,
l_retro_er_contr_ele_type_id,
l_retro_excs_er_cntr_ele_tp_id);