[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : This returns the select statement that is used to create the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
procedure range_cursor
(
pactid in number,
sqlstr out nocopy varchar2
) is
begin
sql_range :=
'SELECT distinct ASG.person_id
FROM per_assignments_f ASG,
pay_payrolls_f PPY,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND ASG.business_group_id = PPA.business_group_id
AND ASG.assignment_type = ''E''
AND PPY.payroll_id = ASG.payroll_id
ORDER BY ASG.person_id';
SELECT /*+ INDEX(asg PER_ASSIGNMENTS_F_N12) */
/* we used the above hint to always ensure that the use the person_id
index on per_assignments_f, otherwise, it is feasible the CBO may decide to
choose the N7 (payroll_id) index due to it being a bind */
asg.person_id person_id
, asg.assignment_id assignment_id
FROM
per_all_assignments_f asg
, pay_all_payrolls_f ppf
, pay_payroll_actions ppa_arch
WHERE
asg.business_group_id + 0 = ppa_arch.business_group_id
AND asg.person_id BETWEEN stperson
AND endperson
AND ppf.payroll_id = p_payroll_id
AND ppf.payroll_id = asg.payroll_id
AND
( ppa_arch.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
OR
( asg.effective_end_date <= ppa_arch.effective_date
AND asg.effective_end_date =
( SELECT MAX(asg2.effective_end_date)
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
)
)
)
AND ppa_arch.payroll_action_id = pactid
AND EXISTS (SELECT /*+ ORDERED */
/* the ordered hint will force the paa table to be joined to first */
NULL
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_id = asg.assignment_id
AND ppa.effective_date BETWEEN ppa_arch.start_date
AND ppa_arch.effective_date
AND ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
AND ppf.payroll_id = ppa.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id)
order by 1, 2
for update of asg.assignment_id;
select distinct pac.context_value
from pay_action_contexts pac,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
where paa.assignment_id = asg_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I') -- added for 5165859
AND pac.assignment_Action_id = paa.assignment_action_id
And pac.context_value <> 'To Be Advised'
and ffc.context_name = 'SOURCE_TEXT'
and ffc.context_id = pac.context_id
and ppa.effective_date >= (select ppa_arch.start_date
from pay_payroll_actions ppa_arch
where ppa_arch.payroll_action_id = pay_action_id)
and ppa.effective_date <= (select ppa_arch.effective_date
from pay_payroll_actions ppa_arch
where ppa_arch.payroll_action_id = pay_action_id);
select legislative_parameters,payroll_id
into leg_param,l_ppa_payroll_id
from pay_payroll_actions
where payroll_action_id = pactid;
update pay_payroll_actions
set payroll_id = l_payroll_id
where payroll_action_id = pactid;
select distinct include_or_exclude
into v_incl_sw
from hr_assignment_set_amendments
where assignment_set_id = asg_set_id;
select include_or_exclude
into inc_flag
from hr_assignment_set_amendments
where assignment_set_id = asg_set_id
and assignment_id = asgrec.assignment_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select distinct code
from pay_za_irp5_bal_codes
where code in (4001, 4002, 4003, 4004, 4005, 4006, 4007, 4018);
Select distinct context_value clearance_number
FROM PAY_ACTION_CONTEXTS PAC,
ff_contexts fcon
Where pac.context_id = fcon.context_id
AND fcon.context_name ='SOURCE_NUMBER'
And PAC.ASSIGNMENT_ACTION_ID in
(
Select paa_all.assignment_Action_id from
pay_assignment_actions paa,
pay_assignment_actions paa_all,
pay_payroll_actions ppa,
per_time_periods ptp
Where paa.assignment_action_id = p_assignment_Action_id
and paa_all.assignment_id = paa.assignment_id
and paa_all.payroll_action_id = ppa.payroll_action_id
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date > add_months(p_effective_date,-12)
and ptp.end_date <= p_effective_date
and ppa.action_type in ('R', 'Q','V', 'B', 'I') -- added for 5165859
)
UNION
Select '99999999999'
FROM dual;
Select max(context_value) directive_number
From
PAY_ACTION_CONTEXTS PAC,
ff_contexts fcon
Where PAC.assignment_action_id = p_assignment_action_id
AND pac.context_id = fcon.context_id
AND fcon.context_name ='SOURCE_TEXT'; */
SELECT DISTINCT pac.context_value directive_number
from pay_action_contexts pac,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
where paa.assignment_id = p_ass_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I') -- added for 5165859
AND pac.assignment_Action_id = paa.assignment_action_id
And pac.context_value <> 'To Be Advised'
and ffc.context_name = 'SOURCE_TEXT'
and ffc.context_id = pac.context_id
and ppa.effective_date >= (select ppa_arch.start_date
from pay_payroll_actions ppa_arch
where ppa_arch.payroll_action_id = p_pact_id)
and ppa.effective_date <= (select ppa_arch.effective_date
from pay_payroll_actions ppa_arch
where ppa_arch.payroll_action_id = p_pact_id);
select aa.assignment_id,
paf.payroll_id,
ppa.effective_date,
ppa.payroll_action_id
into asgid, l_payroll_id, l_eff_date, l_pact_id
from pay_assignment_actions aa,
pay_payroll_actions ppa,
per_assignments_f paf
where aa.assignment_action_id = p_assactid
and aa.assignment_id = paf.assignment_id
and ppa.payroll_action_id = aa.payroll_action_id
and paf.effective_start_date =
(
select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.effective_start_date <= ppa.effective_date
and paf2.assignment_id = aa.assignment_id
);
"This will not select the processing of any ZA_Tax_On_Lump_Sums that took place for this
"Assignment while it was still on an earlier Payroll.
"Thus, modified to not limit the search to Lump Sum processing that took place on the
"Payroll that the assignment was on at Tax Year End. Instead it will also look for
"Lump Sum processing that took place on earlier payrolls for this assignment
*/
/*
As part of Lump Sum Enhancement the Assignment_action_id is stored
in the table pay_assignment_actions during action_creation which will be used here
*/
Select count(*)
into l_main_crt_flag
From pay_assignment_actions paa_arch
Where paa_arch.assignment_action_id > p_assactid
AND paa_arch.payroll_action_id = l_pact_id
AND paa_arch.assignment_id = asgid;
select max(paa.action_sequence)
into aaseq
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = asgid
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and ppa.effective_date <= p_effective_date;
select assignment_action_id, payroll_action_id
into aaid, paid
from pay_assignment_actions
where assignment_id = asgid
and action_sequence = aaseq;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
/* Select decode(count(source_action_id),0,'N','Y')
into g_ls_indicator
From pay_assignment_actions paa_arch
Where paa_arch.assignment_action_id = pay_archive.archive_aa;*/
Select decode(count(*), 0 ,'Y', 'N')
into g_ls_indicator
From pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = pay_archive.archive_aa
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and paa_arch.assignment_action_id =
(
select max(paa.assignment_action_id)
from pay_assignment_actions paa
where paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.assignment_id = paa_arch.assignment_id
) ;
g_ls_table.delete;
/* Select decode(count(source_action_id),0,'N','Y')
into g_ls_indicator
From pay_assignment_actions paa_arch
Where paa_arch.assignment_action_id = pay_archive.archive_aa;*/
Select decode(count(*), 0 ,'Y', 'N')
into g_ls_indicator
From pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = pay_archive.archive_aa
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and paa_arch.assignment_action_id =
(
select max(paa.assignment_action_id)
from pay_assignment_actions paa
where paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.assignment_id = paa_arch.assignment_id
) ;
select count(*)
into l_count
from pay_payroll_actions
where action_type = 'X'
and report_type = 'ZA_IRP5'
and business_group_id = to_number(p_bg_id)
and pay_za_irp5_archive_pkg.get_parameter('TAX_YEAR', legislative_parameters) = p_tax_year;
update hr_organization_information
set org_information11 = '0'
where organization_id = to_number(p_bg_id)
and org_information_context = 'ZA_TAX_FILE_ENTITY';
update hr_organization_information
set org_information11 = to_char(l_count)
where organization_id = to_number(p_bg_id)
and org_information_context = 'ZA_TAX_FILE_ENTITY';
select nvl(to_number(org_information11), 0)
into l_count
from hr_organization_information
where organization_id = to_number(p_bg_id)
and org_information_context = 'ZA_TAX_FILE_ENTITY';
update hr_organization_information
set org_information11 = to_char(l_count)
where organization_id = to_number(p_bg_id)
and org_information_context = 'ZA_TAX_FILE_ENTITY';
select max(substr(paa.serial_number, 5, 6))
into max_num
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.business_group_id = p_bg
and ppa.report_type = 'ZA_IRP5'
and ppa.action_type = 'X'
and substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4) = p_tax_year
and ppa.payroll_action_id <> substr(p_pay, 28, 9)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_ass
and substr(paa.serial_number, 1, 2) = '&&';
select max(substr(paa.serial_number, 3, 6))
into l_max_num
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.business_group_id = p_bg
and ppa.report_type = 'ZA_IRP5'
and ppa.action_type = 'X'
and substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4) = p_tax_year
and ppa.payroll_action_id <> substr(p_pay, 28, 9)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = p_ass
and substr(paa.serial_number, 1, 2) <> '&&';