[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Insert an assignment action for a particular assignment
* to be processed by the balance adjustment. Note that there
* is one assignment action per assignment/balance adjustment.
* Therefore, there can be more than one assignment action per
* assignnment attached to the payroll action.
*/
function insert_assact
(
p_info in out nocopy info_r,
p_element_entry_id in number,
run_type_id in number default null
) return number is
l_assactid number;
select act.assignment_action_id
into l_assactid
from pay_assignment_actions act
where act.payroll_action_id = p_info.batchid
and nvl(act.tax_unit_id, g_number) = nvl(l_tax_unit_id, g_number)
and act.assignment_id = p_info.asgid;
hr_utility.set_location('insert_assact', 20);
select act.assignment_action_id
into l_assactid
from pay_assignment_actions act
where act.payroll_action_id = p_info.batchid
and act.assignment_id = p_info.asgid
and nvl(act.tax_unit_id, g_number) = nvl(l_tax_unit_id, g_number);
update pay_assignment_actions
set chunk_number = g_curr_chunk_no
where assignment_action_id= l_assactid;
end insert_assact;
select pac.business_group_id,
pac.effective_date,
pac.payroll_id,
pbg.legislation_code,
pac.batch_process_mode,
pac.action_type
into l_business_group_id,
l_effective_date,
l_payroll_id,
l_legislation_code,
l_mode,
l_action_type
from pay_payroll_actions pac,
per_business_groups pbg
where pac.payroll_action_id = p_batch_id
and pac.action_status <> 'C'
and pbg.business_group_id = pac.business_group_id;
g_info.assactid := insert_assact(g_info, p_element_entry_id,run_type_id);
* Currently, this inserts a payroll action against which
* the Balance Adjustments will be processed.
*/
function init_batch
(
p_batch_name in varchar2 default null,
p_effective_date in date,
p_consolidation_set_id in number,
p_payroll_id in number,
p_action_type in varchar2 default 'B', -- for balance adjustment.
p_batch_mode in varchar2 default 'STANDARD',
p_prepay_flag in varchar2 default 'Y'
) return number is
l_proc varchar2(72) := 'pay_bal_adjust.init_batch';
select
ptp.time_period_id
from
per_time_periods ptp
where
ptp.payroll_id = p_payroll_id
and p_effective_date between ptp.start_date
and ptp.end_date;
select con.business_group_id,
pay_payroll_actions_s.nextval
into l_business_group_id,
l_payroll_action_id
from pay_consolidation_sets con,
pay_all_payrolls_f prl
where con.consolidation_set_id = p_consolidation_set_id
and prl.payroll_id = p_payroll_id
and p_effective_date between
prl.effective_start_date and prl.effective_end_date
;
insert into pay_payroll_actions (
payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
action_population_status,
action_status,
effective_date,
date_earned,
action_sequence,
legislative_parameters,
future_process_mode,
batch_process_mode,
object_version_number,
time_period_id,
creation_date)
values (l_payroll_action_id,
p_action_type,
l_business_group_id,
p_consolidation_set_id,
p_payroll_id,
'P',
'U',
p_effective_date,
p_effective_date,
pay_payroll_actions_s.nextval,
p_batch_name,
p_prepay_flag,
p_batch_mode,
1,
l_time_period_id,
sysdate);
* This update is no longer necessary, hence commented out.
*
-- Update the inserted run result appropriately.
update pay_run_results prr
set prr.assignment_action_id = p_assactid
,prr.source_id = nvl(p_original_entry_id, prr.source_id)
,prr.status = 'P'
where prr.source_id = p_element_entry_id
and prr.source_type = 'E';
select tax_unit_id
into tax_unit
from pay_assignment_actions
where assignment_action_id = p_assactid;
SELECT ee.object_version_number
FROM pay_element_entries_f ee
WHERE l_element_entry_id = ee.element_entry_id
-- bug 675794, added date condition to select correct row
AND l_effective_date BETWEEN ee.effective_start_date
AND ee.effective_end_date;
SELECT ptp.status
FROM per_time_periods ptp,
per_all_assignments_f pas
WHERE pas.assignment_id = p_assignment_id
AND pas.payroll_id = ptp.payroll_id
AND l_effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND l_effective_date BETWEEN pas.effective_start_date
AND pas.effective_end_date;
SELECT 'X'
FROM pay_element_entries_f ee,
pay_element_types_f et,
pay_element_links_f el
WHERE el.element_link_id = ee.element_link_id
AND el.element_link_id = p_element_link_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = p_assignment_id
AND l_effective_date BETWEEN ee.effective_start_date
AND ee.effective_end_date
AND l_effective_date BETWEEN el.effective_start_date
AND el.effective_end_date
AND l_effective_date BETWEEN et.effective_start_date
AND et.effective_end_date
AND et.multiple_entries_allowed_flag = 'N'
AND ee.entry_type = 'E';
SELECT et.closed_for_entry_flag,
et.process_in_run_flag,
et.element_name,
et.legislation_code,
--
-- Bugfix 2665492
-- Retrieve the element_link costable_type
--
el.costable_type
FROM pay_element_types_f et,
pay_element_links_f el
WHERE el.element_link_id = p_element_link_id
AND el.element_type_id = et.element_type_id
AND l_effective_date BETWEEN el.effective_start_date
AND el.effective_end_date
AND l_effective_date BETWEEN et.effective_start_date
AND et.effective_end_date;
hr_entry_api.insert_element_entry
(
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_element_entry_id => l_element_entry_id,
p_original_entry_id => p_original_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => p_element_link_id,
-- create all elements as type 'F' with NULL creator
p_creator_type => 'B',
p_entry_type => 'B',
p_creator_id => l_info.assactid,
p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_num_entry_values => p_num_entry_values,
p_input_value_id_tbl => p_input_value_id_tbl,
p_entry_value_tbl => p_entry_value_tbl);
update pay_element_entries_f pee
set pee.creator_id = l_info.assactid,
pee.creator_type = 'B',
pee.balance_adj_cost_flag = p_balance_adj_cost_flag
where pee.element_entry_id = l_element_entry_id
and l_info.effdate between
pee.effective_start_date and pee.effective_end_date;
update pay_element_entries_f pee
set pee.creator_id = l_info.assactid,
pee.creator_type = 'B',
pee.balance_adj_cost_flag = p_balance_adj_cost_flag
where pee.element_entry_id = l_element_entry_id
and l_info.effdate between
pee.effective_start_date and pee.effective_end_date;
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_pact_id;
select ptp.time_period_id
into l_timperid
from per_time_periods ptp
where ptp.payroll_id = l_info.payid
and l_info.effdate between
ptp.start_date and ptp.end_date;
update pay_payroll_actions pac
set pac.action_status = 'C',
pac.action_population_status = 'C',
pac.time_period_id = l_timperid
where pac.payroll_action_id = p_batch_id;
select count(*)
into l_processed
from pay_assignment_actions act
where act.payroll_action_id = l_info.batchid
and act.action_status = 'C';
select assignment_id
into l_assignment_id
from pay_assignment_actions
where assignment_action_id=p_assignment_action_id;
select ee.element_entry_id,
ee.element_link_id,
ee.original_entry_id
into l_element_entry_id,
l_element_link_id,
l_original_entry_id
from pay_element_entries_f ee
where ee.creator_id = p_assignment_action_id
and ee.assignment_id = l_assignment_id
and ee.creator_type='B'
and ee.entry_type = 'B'
and p_effdate between ee.effective_start_date and ee.effective_end_date;
select element_entry_id
into p_entry_id
from pay_element_entries_f
where assignment_id=p_assignment_id
and entry_type='B'
and creator_id=p_assignment_action_id
and p_effdate between effective_start_date and effective_end_date;
select tp.start_date,tp.end_date
into l_start_date,l_end_date
from per_time_periods tp,per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.payroll_id=tp.payroll_id
and p_effdate between tp.start_date and tp.end_date
and p_effdate between asg.effective_start_date and asg.effective_end_date;
hr_entry_api.insert_element_entry
(
p_effective_start_date => l_start_date,
p_effective_end_date => l_end_date,
p_element_entry_id => p_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => l_link_id,
p_creator_type => 'B',
p_entry_type => 'B',
p_cost_allocation_keyflex_id =>p_costkflx_id,
p_creator_id => p_assignment_action_id);
update pay_element_entries_f
set balance_adj_cost_flag = p_balcostflg
where element_entry_id = p_entry_id;
select payroll_id
into p_context_value
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effdate between effective_start_date and effective_end_date;
select tax_unit_id
into p_context_value
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select payroll_action_id
into p_context_value
from pay_assignment_actions
where assignment_action_id =p_assignment_action_id;
select input_value_id,frr.result_rule_type,frr.severity_level
from pay_formula_result_rules_f frr,
pay_status_processing_rules_f spr
where spr.element_type_id= p_ele_type
and spr.formula_id = p_formula_id
and spr.status_processing_rule_id = frr.status_processing_rule_id
and upper(frr.result_name) = upper(p_out_name)
and p_effdate between spr.effective_start_date and spr.effective_end_date
and p_effdate between frr.effective_start_date and frr.effective_end_date;
select formula_id
into l_formula_id
from pay_status_processing_rules_f
where element_type_id =p_ele_type
and processing_rule='B'
and p_effdate between effective_start_date and effective_end_date;
select ee.screen_entry_value
into inp_value
from pay_element_entry_values_f ee,
pay_input_values_f iv
where ee.element_entry_id=p_entry_id
and ee.input_value_id=iv.input_value_id
and upper(iv.name)=upper(inp_name)
and p_effdate between iv.effective_start_date and iv.effective_end_date
and p_effdate between ee.effective_start_date and ee.effective_end_date;
seLect formula_name
into l_formula_name
from ff_formulas_f
where formula_id = l_formula_id
and p_effdate between effective_start_date and effective_end_date;
select element_type_id
into l_ind_ele_type_id
from pay_input_values_f
where input_value_id = outputs.input_value_id
and p_effdate between effective_start_date
and effective_end_date;
select prr.run_result_id
into l_rr_id
from pay_run_results prr
where element_type_id = l_ind_ele_type_id
and source_id = p_entry_id
and source_type = 'I'
and assignment_action_id = p_assignment_action_id;
select rr.run_result_id
from pay_run_results rr
where
rr.assignment_action_id = p_assact_id
and rr.source_id = p_eentry_id
and rr.source_type in ('E', 'I')
;