[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_NUMBER(UE.creator_id)
FROM ff_database_items DI,
ff_user_entities UE
WHERE DI.user_name = p_db_item_name
AND UE.user_entity_id = DI.user_entity_id
AND UE.creator_type = 'B'
AND UE.legislation_code = 'US';
select ppa.start_date
,ppa.effective_date
,ppa.report_type
,ppa.report_qualifier
,ppa.business_group_id
from pay_payroll_actions ppa
where payroll_action_id = cp_payroll_action_id;
SELECT jurisdiction_code
INTO l_jurisdiction_code
FROM pay_state_rules
WHERE state_code = p_state_abbrev;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cpn_business_group_id
and ppa.effective_date between cpd_start_date and cpd_end_date
and ppa.action_type = 'X'
and ppa.report_type IN ('W-2C PAPER','W2C_XML')
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = 'E'
and not exists
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cpd_start_date and cpd_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.action_status = 'C')
and not exists
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cpd_start_date and cpd_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.action_status = 'C');
select ppa.payroll_action_id,
paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type = 'YREND'
and ppa.effective_date = cp_w2c_eff_date
and paa.assignment_id = cp_assignment_id
and pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(
ppa.payroll_action_id,
'TRANSFER_GRE') = cp_w2c_tax_unit_id;
'select distinct paf.person_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = '|| ln_business_group_id || '
and ppa.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa.action_type = ''X''
and ppa.report_type IN (''W-2C PAPER'',''W2C_XML'')
and ppa.action_status = ''C''
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = ''E''
and :payroll_action_id is not null
and not exists
(select ''x'' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa1.action_type = ''X''
and ppa1.report_type = ''MARK_W2C_PAPER''
and ppa1.action_status = ''C'')
and not exists
(select ''x'' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa1.action_type = ''X''
and ppa1.report_type = ''W2C''
and ppa1.report_qualifier = ''FED''
and ppa1.action_status = ''C'')
order by paf.person_id';
select distinct paa.assignment_id,
paf.person_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type IN ('W-2C PAPER','W2C_XML')
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = 'E'
and paf.person_id between cp_start_person_id
and cp_end_person_id
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.report_category = 'RT'
and ppa1.action_status = 'C')
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C'
)
and exists
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_assignment_actions paa2,
pay_payroll_actions ppa1,
pay_payroll_actions ppa2
where paa2.assignment_Action_id = pai.locked_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C'
and paa2.assignment_id = paa.assignment_id
and ppa2.action_type = 'X'
and ppa2.report_type IN ('W-2C PAPER','W2C_XML')
and ppa2.action_status = 'C'
and ppa2.payroll_action_id = paa2.payroll_action_id
and paa2.assignment_Action_id > paa.assignment_Action_id
and ppa2.effective_date between cp_start_date and cp_end_date
);
select paa.assignment_id,
paa.tax_unit_id,
paf.person_id,
paa.assignment_Action_id, -- Maximum Assignment Action_ID
to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid,
to_number(substr(paa.serial_number,16,30)) w2c_pp_locked_actid
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type IN ('W-2C PAPER','W2C_XML')
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = 'E'
and paf.person_id between cp_start_person_id
and cp_end_person_id
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date
and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.report_category = 'RT'
and ppa1.action_status = 'C')
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C'
)
and paa.assignment_Action_id =
( SELECT max(paa1.assignment_action_id)
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1
WHERE ppa1.payroll_action_id = paa1.payroll_Action_id
and ppa1.report_type IN ('W-2C PAPER','W2C_XML')
and ppa1.action_status = 'C'
and ppa1.effective_date between cp_start_date and cp_end_date
and paa1.assignment_id = paa.assignment_id
and ppa1.business_group_id = cp_business_group_id
)
ORDER BY paf.person_id;
select distinct paa.assignment_action_id
--bug 7504239
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cpn_business_group_id
and ppa.effective_date between cpd_start_date and cpd_end_date
and ppa.action_type = 'X'
and ppa.report_type IN ('W-2C PAPER','W2C_XML')
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paa.assignment_id = cpn_assignment_id
and paa.assignment_action_id <> cpn_max_asgn_action_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = 'E'
and not exists
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cpd_start_date and cpd_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.action_status = 'C')
and not exists
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cpd_start_date and cpd_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.action_status = 'C');
select --paa.assignment_id,
--paa.tax_unit_id,
--paf.person_id,
--ppa.report_type,
paa.assignment_Action_id,
ppa.payroll_action_id,
to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type IN ('W-2C PAPER','W2C_XML')
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.assignment_type = 'E'
and paf.person_id = cp_person_id
and paa.assignment_Action_id < cp_w2c_paper_action_id
and exists ((select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date
and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.report_category = 'RT'
and ppa1.action_status = 'C')
UNION ALL
(select 'x'
from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date
and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C'))
order by paa.assignment_action_id DESC;
select ppa.report_type locked_report_type,
ppa.payroll_action_id locked_paction_id,
paa.assignment_action_id locked_action_id,
paa.serial_number serial_number
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_locking_action
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select substr(full_name,1,48), employee_number
from per_all_people_f
where person_id = cp_person_id
order by effective_end_date desc;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('Creating Assignment 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.set_location(gv_package || '.action_creation', 130);
** Update the serial number column with the assignment action
** of the last two archive processes
*************************************************************/
ln_serial_number := lpad(ln_corrected_asg_action,15,0)||
lpad(ln_orig_reported_asg_action,15,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_w2c_asg_action;