[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pay_core_utils.get_parameter(p_token,legislative_parameters)
from pay_payroll_actions
where payroll_action_id = p_pact_id;
** Purpose : This procedure returns an SQL statement to select all the
** people that may be eligible for payslip reports.
** The archiver uses this cursor to split the people into chunks
** for parallel processing.
**----------------------------------------------------------**/
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_payroll_id number := null;
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id +0= ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr := 'SELECT DISTINCT ppf.person_id
FROM per_all_people_f ppf,
pay_payroll_actions ppa,
per_all_assignments_f paaf
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppf.business_group_id +0 = ppa.business_group_id
AND paaf.person_id = ppf.person_id
AND paaf.payroll_id = '|| to_char(l_payroll_id)||
' ORDER BY ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
** will be inserted into pay_temp_object_actions.
**----------------------------------------------------------**/
procedure action_creation(pactid in number,
stperson in number,
endperson in number,
chunk in number) is
cursor get_arch_action_det (cp_payroll_id number,
cp_bg_id number,
cp_start_date varchar2,
cp_end_date varchar2 ) is
select distinct paa.assignment_action_id,
paaf.assignment_id,
ppa.effective_date
from per_all_assignments_f paaf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks lck,
pay_payroll_actions ppa1,
pay_assignment_actions paa1
where paa.SOURCE_ACTION_ID is null
and paaf.person_id between stperson and endperson
and ppa.report_type = 'SA_ARCHIVE'
and ppa.action_type ='X'
and paa.assignment_id = paaf.assignment_id
and paaf.business_group_id = cp_bg_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.start_date between fnd_date.canonical_to_date(cp_start_date)
and fnd_date.canonical_to_date(cp_end_date)
and ppa.report_qualifier = 'SA'
and ppa.report_category = 'ARCHIVE'
and ppa.business_group_id = paaf.business_group_id
and paa.action_status = 'C'
and lck.locking_action_id = paa.assignment_action_id
and lck.locked_action_id = paa1.assignment_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_status IN ('C','S')
and ppa1.payroll_id = nvl(cp_payroll_id,ppa1.payroll_id)
and ppa1.business_group_id = ppa.business_group_id
and ppa1.action_type IN ('U','P')
and not exists (
select 1
from pay_action_information pai
where pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.assignment_id = paa.assignment_id
and pai.action_information_category = 'EMEA ELEMENT INFO'
and pai.action_information3 in ('E','D'));
select business_group_id
into l_bg_id
from pay_payroll_actions
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select ptoa.object_id assignment_action_id
from pay_temp_object_actions ptoa
where object_action_id = p_assactid;
SELECT run_assact.assignment_id run_assgt_id
,run_assact.assignment_action_id run_assact_id
,prepay_payact.effective_date prepay_effective_date
FROM pay_action_interlocks archive_intlck
,pay_assignment_actions prepay_assact
,pay_payroll_actions prepay_payact
,pay_action_interlocks prepay_intlck
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
WHERE archive_intlck.locking_action_id = p_locking_action_id
AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U','P')
AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_Action_id = p_assignment_action_id;