[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
'SELECT distinct PAF.person_id
FROM per_assignments_f PAF,
pay_payrolls_f PPY,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND PAF.business_group_id = PPA.business_group_id
AND PPY.payroll_id = PAF.payroll_id
AND PPY.payroll_id = '||g_payroll_id||'
ORDER BY PAF.person_id';
'SELECT distinct PAF.person_id
FROM per_assignments_f PAF,
pay_payrolls_f PPY,
pay_payroll_actions PPA,
per_assignment_extra_info AEI
WHERE PPA.payroll_action_id = :payroll_action_id
AND PAF.business_group_id = PPA.business_group_id
AND PPY.payroll_id = PAF.payroll_id
AND AEI.assignment_id = PAF.assignment_id
AND AEI.aei_information_category = ''ZA_SPECIFIC_INFO''
AND AEI.aei_information7 = to_char('||g_legal_entity_id||')';
SELECT PAF.assignment_id
FROM per_assignments_f PAF
, per_assignment_extra_info aei
, per_time_periods ptp
WHERE PAF.payroll_id = nvl(p_payroll_id, PAF.payroll_id)
AND PAF.assignment_id = nvl(p_asg_id, PAF.assignment_id)
AND aei.assignment_id = PAF.assignment_id
AND aei.aei_information_category = 'ZA_SPECIFIC_INFO'
AND aei.aei_information7 = to_char(p_legal_entity_id)
AND PAF.effective_end_date = (select max(PAF2.effective_end_date)
from per_assignments_f PAF2
where PAF2.assignment_id = PAF.assignment_id
and PAF2.payroll_id = PAF.payroll_id)
AND PAF.person_id BETWEEN stperson and endperson
AND ptp.payroll_id = PAF.payroll_id
and p_period_end_date between ptp.start_date and ptp.end_date
AND exists
( select 1 from
pay_assignment_actions paa3,
pay_payroll_actions ppa2
where paa3.assignment_id = PAF.assignment_id
and paa3.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
and ppa2.time_period_id >= (select MIN(ptp1.time_period_id) min_time_period
from per_time_periods ptp1
where ptp1.payroll_id = PAF.payroll_id
and ptp1.prd_information1 = ptp.prd_information1
)
and ppa2.time_period_id <= ptp.time_period_id
and ppa2.payroll_id = PAF.payroll_id
and paa3.ACTION_STATUS in ( 'C', 'S')
);
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into l_ass_act_id
from dual;
-- insert into pay_assignment_actions.
hr_nonrun_asact.insact(l_ass_act_id,
v_assignments.assignment_id,
pactid,
chunk,
null);
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select ppa.legislative_parameters, ppa.payroll_action_id
into leg_param, l_payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = p_assactid;
select assignment_id
into l_asg_id
from pay_assignment_actions
where assignment_action_id = p_assactid;
select ptp.prd_information1,PAF.payroll_id, ptp.time_period_id, ptp.prd_information2, ptp.pay_advice_date
into g_tax_year, g_payroll_id, g_end_period_id, l_qtr_num, l_pay_advice_date
from per_time_periods ptp
,pay_assignment_actions paa
,per_assignments_f PAF
where paa.assignment_action_id = p_assactid
and PAF.assignment_id = paa.assignment_id
and ptp.payroll_id = PAF.payroll_id
-- and g_end_period_date between PAF.effective_start_date and PAF.effective_end_date
and PAF.effective_start_date = (select max(effective_start_date) from per_all_assignments_f paf2
where paf2.assignment_id = paa.assignment_id
and effective_start_date <= g_end_period_date)
and g_end_period_date between ptp.start_date and ptp.end_date;
select MIN(ptp.time_period_id)
into g_st_period_id
from per_time_periods ptp
where ptp.payroll_id = g_payroll_id
and ptp.prd_information1 = g_tax_year;
select haou.name
from hr_all_organization_units haou,
hr_organization_information hoi,
hr_organization_information hoi2
where haou.organization_id = p_organization_id
and hoi.organization_id = haou.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hoi2.organization_id = haou.organization_id
and hoi2.org_information_context = 'CLASS'
and hoi2.org_information1 = 'HR_LEGAL'
and hoi2.org_information2 = 'Y' ;
select distinct pai.action_information21 payroll_id,
ppa.effective_date effective_date,
ppf.payroll_name payroll_name,
ptp.start_date start_date,
ptp.end_date end_date
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
, pay_all_payrolls_f ppf
, per_time_periods ptp
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_TAX_REG_REP'
and ppf.payroll_id = pai.action_information21
and ptp.payroll_id = ppf.payroll_id
and ptp.time_period_id = pai.action_information22
order by payroll_id;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
SELECT ptp.period_num
, fnd_date.date_to_displaydate(ptp.start_date)
, fnd_date.date_to_displaydate(ptp.end_date)
INTO c_period_num
,c_period_start
,c_period_end
FROM per_time_periods ptp
WHERE ptp.time_period_id = p_end_period_id;
SELECT count( DISTINCT pai.action_information3)
INTO CP_Total_Employees
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id;
SELECT count( DISTINCT pai.action_information11)
INTO CP_Total_Assignments
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id;
DELETE
FROM pay_action_information
WHERE action_context_id in
(
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
)
AND action_information_category = 'ZA_TAX_REG_REP';
DELETE
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'ZA_TAX_REG_REP_PAY_DET';
DELETE
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id;
DELETE
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;