[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppa.business_group_id,
paa.action_status ASSACT_ACTION_STATUS,
ppa.date_earned,
ppa.effective_date,
paa.assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id=p_locked_assignment_action_id
and paa.action_status='C'
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.action_type in ('R','Q','B','I')
for update of paa.assignment_action_id;
select asg.person_id,
asg.period_of_service_id,
pds.date_start,
pds.leaving_reason,
pds.actual_termination_date,
asg.employment_category
into p_person_id,
p_period_of_service_id,
p_date_start,
p_leaving_reason,
p_actual_termination_date,
p_employment_category
from per_all_assignments_f asg,
per_periods_of_service pds
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and pds.period_of_service_id = asg.period_of_service_id;
PROCEDURE insert_row(
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY VARCHAR2,
p_locked_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
p_locking_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
p_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
p_effective_date IN pay_payroll_actions.effective_date%TYPE,
p_value IN pay_jp_custom_pkg.value_rec)
IS
l_action_status VARCHAR2(1);
hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',10);
hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
END insert_row;
insert_row(
p_errbuf => p_errbuf
,p_retcode => p_retcode
,p_locked_assignment_action_id => p_locked_assignment_action_id
,p_locking_assignment_action_id => p_locking_assignment_action_id
,p_assignment_id => l_assignment_id
,p_effective_date => l_effective_date
,p_value => l_value
);
delete pay_action_information pai
where (action_information_category='JP_PRE_TAX_1'
or action_information_category='JP_PRE_TAX_2')
and action_context_type='AAP'
and not exists(
select NULL
from pay_assignment_actions paa
where paa.assignment_action_id=pai.action_information1);
SELECT 0
INTO l_dummy
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
AND INCLUDE_OR_EXCLUDE = 'I'
AND ROWNUM = 1;
SELECT INCLUDE_OR_EXCLUDE
INTO l_inc_or_exc
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_ID = p_assignment_id
AND ASSIGNMENT_SET_ID = p_assignment_set_id;
SELECT 1
INTO l_dummy
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
AND ROWNUM = 1
AND EXISTS ( select null from hr_assignment_sets
where ASSIGNMENT_SET_ID = p_assignment_set_id
AND FORMULA_ID IS NULL
);
SELECT DISTINCT paa1.assignment_action_id, ppa1.payroll_action_id
FROM pay_payroll_actions ppa, pay_assignment_actions paa,
pay_payroll_actions ppa1, pay_assignment_actions paa1,
pay_action_information pai
WHERE ppa.business_group_id = p_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.date_earned BETWEEN p_from_date AND p_to_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND pai.action_information_category = 'JP_PRE_TAX_1'
AND pai.action_context_type = 'AAP'
AND pai.action_information1 = paa.assignment_action_id
AND pai.action_context_id = paa1.assignment_action_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.business_group_id = p_business_group_id
AND ppa1.action_type = 'X';
l_assact_tab.DELETE;
SELECT COUNT(1)
INTO l_count1 -- Bug 13772840
FROM pay_assignment_actions
WHERE payroll_action_id = l_payroll_action_id;
DELETE FROM pay_action_information pai
WHERE (pai.action_information_category='JP_PRE_TAX_1'
OR pai.action_information_category='JP_PRE_TAX_2')
AND pai.action_context_type='AAP'
AND exists ( select null
from pay_assignment_actions paa,pay_payroll_actions ppa
where paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
and ppa.business_group_id = p_business_group_id
and ppa.date_earned BETWEEN p_from_date AND p_to_date --Bug 14094779
and paa.ASSIGNMENT_ACTION_ID = pai.ACTION_INFORMATION1
and ppa.payroll_id = p_payroll_id );
SELECT DISTINCT paa1.assignment_action_id, ppa1.payroll_action_id
FROM pay_payroll_actions ppa, pay_assignment_actions paa,
pay_payroll_actions ppa1, pay_assignment_actions paa1,
pay_action_information pai
WHERE ppa.business_group_id = p_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.date_earned BETWEEN l_from_date AND l_to_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND pai.action_information_category = 'JP_PRE_TAX_1'
AND pai.action_context_type = 'AAP'
AND pai.action_information1 = paa.assignment_action_id
AND pai.action_context_id = paa1.assignment_action_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.business_group_id = p_business_group_id
AND ppa1.action_type = 'X'
AND pai.assignment_id = p_assignment_id;
SELECT DISTINCT ASSIGNMENT_ID
FROM per_all_assignments_f
WHERE l_from_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
OR l_to_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PAYROLL_ID = p_payroll_id
AND BUSINESS_GROUP_ID = p_business_group_id;
SELECT formula_id INTO l_formula_id
FROM hr_assignment_sets
WHERE assignment_set_id = l_ass_set_id;
l_assact_tab.DELETE;
SELECT COUNT(1)
INTO l_count1
FROM pay_assignment_actions
WHERE payroll_action_id = l_payroll_action_id;
DELETE FROM pay_action_information pai
WHERE (pai.action_information_category='JP_PRE_TAX_1'
OR pai.action_information_category='JP_PRE_TAX_2')
AND pai.action_context_type='AAP'
AND exists ( select null
from pay_assignment_actions paa,pay_payroll_actions ppa
where paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
and ppa.business_group_id = p_business_group_id
and ppa.date_earned BETWEEN l_from_date AND l_to_date --Bug 14094779
and paa.ASSIGNMENT_ACTION_ID = pai.ACTION_INFORMATION1
and ppa.payroll_id = p_payroll_id )
AND pai.assignment_id = l_temp_table(i).assignment_id;
SELECT COUNT(1)
INTO l_count
FROM pay_action_information
WHERE action_information_category = 'JP_PRE_TAX_1'
AND action_context_type = 'AAP'
AND action_information1 = p_assignment_action_id;