[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_date.canonical_to_date(pai.action_information4) start_date
, fnd_date.canonical_to_date(pai.action_information5) end_date
, pai.action_information8 limit1
, pai.action_information9 limit2
, fnd_date.canonical_to_date(pai.action_information11) split_start_date1
, fnd_date.canonical_to_date(pai.action_information12) split_end_date1
, fnd_date.canonical_to_date(pai.action_information13) split_start_date2
, fnd_date.canonical_to_date(pai.action_information14) split_end_date2
, pai.action_information7 num_of_days
from pay_action_information pai
where action_context_id = p_payroll_action_id
and action_information_category = 'ZA_COID_PAYROLL_INFO';
Select
global_value,
effective_start_date,
effective_end_date
From
ff_globals_f
Where
global_name = 'ZA_COIDA_LIMIT'
And legislation_code = 'ZA'
And (p_start_period_date between effective_start_date and effective_end_date
Or p_end_period_date between effective_start_date and effective_end_date)
Order By effective_start_date;
select effective_date
from pay_payroll_actions
where payroll_action_id = pactid;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
SELECT ptp.start_date
INTO g_start_period_date
FROM per_time_periods ptp
WHERE ptp.time_period_id = l_strt_prd_id;
SELECT ptp.end_date
INTO g_end_period_date
FROM per_time_periods ptp
WHERE ptp.time_period_id = l_end_prd_id;
Select name
into l_org_name
From
hr_organization_units
Where
business_group_id +0 = l_business_grp_id
And business_group_id +0 = organization_id;
Select payroll_name
into l_payroll_name
From pay_payrolls_f
Where payroll_id = l_payroll_id
And g_end_period_date between effective_start_date and effective_end_date;
Select
'Y' into g_split_year
From
dual
Where
1 < (Select
count(global_value)
From
ff_globals_f
Where
global_name = 'ZA_COIDA_LIMIT'
And legislation_code = 'ZA'
And (g_start_period_date between effective_start_date and effective_end_date
Or g_end_period_date between effective_start_date and effective_end_date));
select max(end_date) - min(start_date) + 1
into g_num_of_days
from per_time_periods
where payroll_id = l_payroll_id
and prd_information1 = (select prd_information1
from per_time_periods
where payroll_id = l_payroll_id
and g_start_period_date between start_date and end_date);
update pay_payroll_actions
set payroll_id = l_payroll_id
where payroll_action_id = pactid;
'select distinct ass.person_id
from per_assignments_f ass,
pay_payrolls_f ppf,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and ass.business_group_id = ppa.business_group_id
and ass.assignment_type = ''E''
and ppf.payroll_id = ass.payroll_id
and ppf.payroll_id = ppa.payroll_id
order by ass.person_id';
SELECT
asg.person_id
, asg.assignment_id
FROM
per_assignment_extra_info paei
, per_all_assignments_f asg
, per_person_type_usages_f pptu
, per_person_types ppt
WHERE
asg.payroll_id = l_payroll_id
AND asg.effective_end_date =
( SELECT
MAX(asgm.effective_end_date)
FROM
per_assignments_f asgm
WHERE
asgm.assignment_id = asg.assignment_id
AND g_start_period_date <= asgm.effective_end_date
AND g_end_period_date >= asgm.effective_start_date
and asgm.payroll_id = l_payroll_id -- Bug 12572065
)
AND g_end_period_date BETWEEN pptu.effective_start_date and pptu.effective_end_date
AND asg.person_id between stperson and endperson
AND pptu.person_id = asg.person_id
AND pptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','EX_EMP')
AND asg.assignment_id = paei.assignment_id
AND paei.information_type = 'ZA_SPECIFIC_INFO'
AND exists (select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa
where
ppa.payroll_id=asg.payroll_id
and paa.assignment_id=asg.assignment_id
and ppa.time_period_id <=l_end_period_id
and ppa.time_period_id >=l_start_period_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'
);
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
SELECT
prrv.result_value
FROM
pay_element_types_f pet
, pay_input_values_f piv
, pay_run_results prr
, pay_run_result_values prrv
WHERE
pet.element_name = 'ZA_Tax'
AND pet.legislation_code = 'ZA'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Tax Status'
AND piv.input_value_id = prrv.input_value_id
AND prr.element_type_id = pet.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND prr.assignment_action_id =
(
SELECT
MAX(paa2.assignment_action_id)
FROM
pay_run_results prr2
, pay_assignment_actions paa2
, pay_payroll_actions ppa2
WHERE
prr2.element_type_id = pet.element_type_id
AND prr2.run_result_id = prr2.run_result_id
AND prr2.assignment_action_id = paa2.assignment_action_id
AND paa2.assignment_id = p_assignment_id
AND paa2.payroll_action_id = ppa2.payroll_action_id
AND ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
AND ppa2.effective_date <= g_end_period_date
AND paa2.source_action_id IS not null
);
select pdb.defined_balance_id
from pay_balance_dimensions pbd
, pay_defined_balances pdb
, pay_balance_types pbt
where pbd.dimension_name = p_dim_name
and pbd.legislation_code = 'ZA'
and pdb.balance_type_id = pbt.balance_type_id
and pbt.balance_name = p_bal_name
and pbt.legislation_code = 'ZA'
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select max(paa.action_sequence)
into l_run_action_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_asg_id
and paa.action_status IN ('C','S') --10376999
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and ppa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
-- and ptp.prd_information1 = l_tax_year
and ptp.pay_advice_date <= p_date
and ptp.pay_advice_date >= l_start_py_adv_dt
and ptp.payroll_id = p_payroll_id; -- Bug # 12329847 and 12334564
select assignment_action_id
into l_run_assact_id
from pay_assignment_actions
where assignment_id = p_asg_id
and action_sequence = l_run_action_seq;
select min(ptp.start_date),
max(ptp.end_date)
into l_start_date,
l_end_date
from per_time_periods ptp
where ptp.payroll_id = P_PAYROLL_ID
and ptp.pay_advice_date <= P_DATE
and ptp.pay_advice_date >= L_START_PY_ADV_DT;
select count(1)
into l_payroll_exists
from per_all_assignments_f asg
where asg.assignment_id = p_asg_id
and asg.effective_start_date <= l_end_date
and asg.effective_end_date >= l_start_date
and asg.payroll_id = p_payroll_id; -- Bug#11938675
select max(past.per_system_status)
into l_per_system_status
from per_assignment_status_types past,
per_all_assignments_f asg
where past.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_asg_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.effective_start_date <= p_date
and asg.effective_end_date >= L_START_PY_ADV_DT;
select past.per_system_status
into l_per_system_status
from per_assignment_status_types past,
per_all_assignments_f asg
where past.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_asg_id
and p_date between asg.effective_start_date and asg.effective_end_date;
Select ppt.system_person_type
into l_person_type
from
per_person_type_usages_f pptu
, per_person_types ppt
Where p_date BETWEEN pptu.effective_start_date and pptu.effective_end_date
AND pptu.person_id = p_person_id
AND pptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','EX_EMP');
select max(PAY_ADVICE_DATE)+1
into l_pay_adv_start_date
from per_time_periods
where payroll_id=p_payroll_id
and PAY_ADVICE_DATE < l_start_py_adv_dt;
select distinct pay_advice_date
from per_time_periods
where payroll_id=p_payroll_id
and start_date >=g_start_period_date
and end_date <= g_end_period_date
order by pay_advice_date;
select per.last_name||' '||substr(per.first_name,1,1)||'.'||substr(nvl(per.middle_names,' '),1,1) last_name
, per.person_id
, per.employee_number emp_no
, decode(ppt.system_person_type
,'EMP','Emp'
,'EX_EMP','Ex-Emp') type
, asg.assignment_number asg_No
, asg.assignment_id ass_id
, asg.effective_start_date asg_start_date
, per.effective_start_date hire_date
, asg.effective_end_date asg_end_date
, per.effective_end_date term_date
, nvl(paei.aei_information4,'01') nature
, nvl(paei.aei_information15,'N') foreign_national
, nvl(paei.aei_information16,'N') exclude_coida -- 12534150
from per_all_people_f per
, per_all_assignments_f asg
, pay_assignment_actions paa
, per_assignment_extra_info paei
, per_person_type_usages_f pptu
, per_person_types ppt
where paa.assignment_action_id = p_assactid
and asg.assignment_id = paa.assignment_id
and per.person_id = asg.person_id
and paei.assignment_id = asg.assignment_id
AND pptu.person_id = per.person_id
AND pptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','EX_EMP')
AND paei.information_type = 'ZA_SPECIFIC_INFO'
and p_effective_date between per.effective_start_date and per.effective_end_date
and p_effective_date between pptu.effective_start_date and pptu.effective_end_date
AND asg.effective_end_date =
( SELECT
MAX(asgm.effective_end_date)
FROM
per_assignments_f asgm
WHERE
asgm.assignment_id = asg.assignment_id
AND g_start_period_date <= asgm.effective_end_date
AND g_end_period_date >= asgm.effective_start_date
) ;
select ppa.legislative_parameters, ppa.payroll_action_id
into leg_param , pactid
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and ppa.payroll_action_id = paa.payroll_action_id;
select max(PAY_ADVICE_DATE)+1
into l_pay_adv_start_date
from per_time_periods
where payroll_id=l_payroll_id
and PAY_ADVICE_DATE < rec_pay_dates.pay_advice_date;
select distinct
pai.action_information30 person_id
, pai.action_information9 asg_type
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
, pay_assignment_actions paa2
, pay_action_information pai2
where ppa.payroll_action_id = pactid
and ppa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZA_COID_EMP_INFO'
and paa2.payroll_action_id = ppa.payroll_action_id
and paa2.assignment_action_id = pai2.action_context_id
and pai2.action_information30 = pai.action_information30 --Same person id
and pai2.action_information9 = pai.action_information9 --Same assignment type
and pai.action_information_id <> pai2.action_information_id
and pai2.action_context_type = 'AAP'
and pai2.action_information_category = 'ZA_COID_EMP_INFO';
select pai.*
from pay_action_information pai,
pay_assignment_actions paa
where pai.action_information30 = l_person_id
and pai.action_information9 = l_asg_type
and paa.payroll_action_id = pactid
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category='ZA_COID_EMP_INFO';
select pai.*
from pay_action_information pai
where pai.action_context_id = l_act_context
and pai.action_information1 = nvl(l_month,pai.action_information1)
and pai.action_information_category='ZA_COID_EMP_INCOME_INFO';
select nvl(sum(pai.action_information2),0) norm_income
, nvl(sum(pai.action_information3),0) dir_income
, nvl(sum(pai.action_information4),0) cash_comp
, nvl(sum(pai.action_information5),0) tot_income
, pai.action_information1 mon
, fnd_date.canonical_to_date(pai.action_information10) pay_adv_date --revisit --need to populate this in archive code
, pai.action_information8 num_days
from pay_action_information pai
, pay_assignment_actions paa
, pay_action_information pai2
where pai.action_information30 = l_person_id
and pai.action_information9 = l_asg_type
and paa.assignment_action_id = pai.action_context_id
and paa.payroll_action_id = pactid
and pai.action_context_id = pai2.action_context_id
and pai.action_information_category ='ZA_COID_EMP_INCOME_INFO'
and pai2.action_information_category = 'ZA_COID_EMP_INFO'
and pai.action_information9 = pai2.action_information9
and nvl(pai.action_information29,'I') <> 'E'
and nvl(pai2.action_information29,'I') <> 'E'
group by pai.action_information1
, pai.action_information10
, pai.action_information8;
select count(pai.action_information_id) num_emp
, nvl(sum(pai2.action_information2),0) norm_income
, nvl(sum(pai2.action_information3),0) dir_income
, nvl(sum(pai2.action_information4),0) cash_comp
, nvl(sum(pai2.action_information5),0) tot_income
, nvl(sum(pai2.action_information6),0) act_earn1
, nvl(sum(pai2.action_information7),0) act_earn2
, pai2.action_information1 mon
, pai.action_information9 asg_type
from pay_action_information pai --ZA_COID_EMP_INFO
, pay_action_information pai2 --ZA_COID_EMP_INCOME_INFO
, pay_assignment_actions paa
where paa.payroll_action_id = pactid
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category = 'ZA_COID_EMP_INFO'
and pai2.action_information_category = 'ZA_COID_EMP_INCOME_INFO'
and pai.action_context_id = pai2.action_context_id
and nvl(pai.action_information29,'I') <> 'E'
and nvl(pai2.action_information29,'I') <> 'E'
and pai.action_information9 = pai2.action_information9
group by pai2.action_information1
, pai.action_information9;
select to_char(PAY_ADVICE_DATE,'MON') mon -- Modified
from per_time_periods
where payroll_id = p_payroll_id
and time_period_id >= p_strt_prd_id
and time_period_id <= p_end_prd_id
minus
select action_information1 mon
from pay_action_information
where action_context_id = pactid
and action_information_category = 'ZA_COID_TOT_MONTH_INFO'
and action_information9 = p_asg_type;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
cum_tab.delete;
pay_action_information_api.update_action_information(
p_action_information_id => rec_emp_inc_info.action_information_id,
p_object_version_number => rec_emp_inc_info.object_version_number,
p_action_information1 => l_month,
p_action_information2 => cum_tab(l_month).norm_inc,
p_action_information3 => cum_tab(l_month).dir_inc,
p_action_information4 => cum_tab(l_month).cash_comp,
p_action_information5 => cum_tab(l_month).tot_inc,
p_action_information6 => cum_tab(l_month).act_earn1,
p_action_information7 => cum_tab(l_month).act_earn2
);
pay_action_information_api.update_action_information(
p_action_information_id => rec_action_info.action_information_id,
p_object_version_number => rec_action_info.object_version_number,
p_action_information29 => 'E'
);
pay_action_information_api.update_action_information(
p_action_information_id => rec_dup_inc_info.action_information_id,
p_object_version_number => rec_dup_inc_info.object_version_number,
p_action_information29 => 'E'
);