[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_selected_asg_set in w2c_action_creation
procedure for Perf Improvement
31-MAR-2008 asgugupt 115.19 6909112 Set ln_primary_assignment_id properly
for secondary assignment in
procedure w2c_action_creation
*****************************************************************************/
gv_package VARCHAR2(100) := 'pay_us_w2c_arch';
select ppa.payroll_action_id
from pay_payroll_actions ppa
where ppa.report_type = 'YREND'
and ppa.effective_date = cp_w2c_eff_date
and pay_us_w2c_arch.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
= cp_w2c_tax_unit_id;
select to_number(pay_us_w2c_arch.get_parameter(
'TRANSFER_GRE',ppa.legislative_parameters)),
to_number(pay_us_w2c_arch.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_us_w2c_arch.get_parameter(
'SSN',ppa.legislative_parameters)),
to_number(pay_us_w2c_arch.get_parameter(
'ASG_SET',ppa.legislative_parameters)),
to_char(effective_date,'YYYY') ,
effective_date,
start_date,
business_group_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to created the range rows for the
W-2C Archiver.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE w2c_range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct asg.person_id person_id
from per_all_assignments_f asg
where person_id = ' || to_char(ln_person_id) ||
' and :p_payroll_action_id is not null';
'select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = ''' || ln_asg_set || '''
and asgset.assignment_id = paf.assignment_id
and asgset.include_or_exclude = ''I''
and :payroll_action_id is not null ';
CURSOR c_selected_asg_set(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number
,cp_eoy_pactid in number
,cp_year_start_date in date) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = cp_asg_set
and asgset.include_or_exclude = 'I'
and paf.assignment_id = asgset.assignment_id
--and exists (select 1 from pay_assignment_actions paa
-- where paa.assignment_id = asgset.assignment_id
-- and paa.payroll_action_id = cp_eoy_pactid)
and paf.person_id between cp_start_person
and cp_end_person
and paf.effective_end_date >= cp_year_start_date; /* Bug 4349941 */
select ppa.report_type, paa.assignment_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= cp_start_date
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('YREND', 'W2C_PRE_PROCESS', 'W-2C PAPER')
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date
)
order by paa.assignment_action_id desc;
select substr(full_name,1,31), substr(national_identifier,1,11)
from per_all_people_f
where person_id = cp_person_id
ORDER BY effective_end_date desc;
SELECT max(paa.assignment_id)
FROM
pay_assignment_actions paa,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
WHERE paf.person_id = cp_person_id
AND paa.assignment_id = paf.assignment_id
and paf.primary_flag = 'Y'
AND paa.tax_unit_id = cp_tax_unit_id
and ppa.action_status ='C'
and paa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN cp_start_date and
cp_end_date;
/*need to determine if the selected person has
any unprinted W2Cs. In this case we would not
create an action for him. Messages should be pushed in the
logs saying why his action was not created.
Actions will be created if
#1 there is a YEPP action not followed by a
W2C_PRE_PROCESS action
#2 there is a W2C_PRE_PROCESS action for this person
which is followed by a W2C report process */
open get_prev_w2c_dtls(p_person_id
,ln_tax_unit_id
,ld_end_date
,ld_start_date);
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('creating asg action');
/* Update the serial number column with the person id
so that the W2C report will not have
to do an additional checking against the assignment
table */
hr_utility.trace('updating asg action');
update pay_assignment_actions aa
set aa.serial_number = p_person_id
where aa.assignment_action_id = ln_w2c_asg_action;
open c_selected_asg_set (p_start_person_id
,p_end_person_id
,ln_asg_set
,ln_eoy_pactid
,ld_start_date) ;
hr_utility.trace('Opened cusor c_selected_asg_set');
fetch c_selected_asg_set into ln_person_id_sel ;
if c_selected_asg_set%notfound then
hr_utility.trace('No Person found for reporting in this chunk');
hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id));
close c_selected_asg_set;