[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_number(UE.creator_id)
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_db_item_name
and UE.user_entity_id = DI.user_entity_id
and Ue.creator_type = 'B';
SELECT '1'
INTO l_resides_true
FROM dual
WHERE EXISTS (
SELECT '1'
FROM per_assignments_f paf,
per_addresses pad
WHERE paf.assignment_id = p_assignment_id AND
paf.person_id = pad.person_id AND
pad.date_from <= p_period_end AND
NVL(pad.date_to ,p_period_end) >= p_period_start AND
pad.region_2 = p_state AND
pad.primary_flag = 'Y');
select legislative_parameters,
pay_negbal_pkg.get_parameter('TRANSFER_STATE',
ppa.legislative_parameters)
into leg_param,
l_state
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'SELECT distinct ASG.person_id
FROM per_assignments_f ASG,
hr_organization_units HOU,
pay_payrolls_f PPY,
pay_state_rules SR,
hr_organization_information HOI,
pay_us_asg_reporting puar,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND SR.state_code = '''||l_state||'''
AND substr(SR.jurisdiction_code,1,2) = substr(puar.jurisdiction_code,1,2)
AND ASG.assignment_id = puar.assignment_id
AND puar.tax_unit_id = HOU.organization_id
AND ASG.business_group_id + 0 = PPA.business_group_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= PPA.effective_date
AND ASG.effective_end_date >= PPA.start_date
AND ((not exists (
select ''x'' from hr_organization_information hoi2
where HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
and HOI2.org_information2 is not null
and HOI2.organization_id = hou.organization_id))
or ( '''||l_state||''' = ''CA'')
)
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
AND HOI.ORG_INFORMATION1 = '''||l_state||'''
AND PPY.payroll_id = ASG.payroll_id
ORDER BY ASG.person_id';
SELECT paa.assignment_action_id locked_action_id,
asg.assignment_id assignment_id,
asg.person_id person_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date,
sr.jurisdiction_code jurisdiction_code
FROM hr_organization_information hoi,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_state_rules sr,
per_assignments_f asg,
pay_payroll_actions ppa_arch
WHERE ppa_arch.payroll_action_id = pactid
AND asg.person_id between stperson and endperson
AND asg.business_group_id + 0 = ppa_arch.business_group_id
AND asg.assignment_type = 'E'
AND asg.effective_start_date <= l_period_end
AND asg.effective_end_date >= l_period_start
AND paa.assignment_id = asg.assignment_id
AND (paa.action_sequence,asg.person_id,paa.tax_unit_id)
in (select max(paa1.action_sequence),paf1.person_id, paa1.tax_unit_id
from pay_action_classifications pac,
pay_payroll_actions ppa1,
pay_assignment_actions paa1,
per_assignments_f paf1
where paf1.person_id = asg.person_id
AND paf1.business_group_id + 0 = ppa_arch.business_group_id
AND paf1.assignment_type = 'E'
AND paf1.effective_start_date <= l_period_end
AND paf1.effective_end_date >= l_period_start
and paa1.assignment_id = paf1.assignment_id
and paa1.tax_unit_id = paa.tax_unit_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa1.effective_date between
l_period_start
and l_period_end
group by paf1.person_id, paa1.tax_unit_id)
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between l_period_start
and l_period_end
AND ppa.action_type in ('R','Q','V','B','I')
AND ppa.effective_date between asg.effective_start_date
and asg.effective_end_date
AND SR.state_code = l_state
AND hoi.organization_id = paa.tax_unit_id
AND hoi.org_information_context = 'State Tax Rules'
AND hoi.org_information1 = l_state
AND EXISTS (select '' from pay_us_asg_reporting puar
where asg.assignment_id = puar.assignment_id
and paa.tax_unit_id = puar.tax_unit_id
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2));
SELECT decode(l_state,'CA',null,hoi2.org_information2)
FROM hr_organization_information hoi2
WHERE hoi2.organization_id = greid
AND hoi2.org_information_context = '1099R Magnetic Report Rules';
select pay_negbal_pkg.get_parameter('TRANSFER_STATE',
ppa.legislative_parameters) state_abbrev,
ppa.start_date,
ppa.effective_date,
trunc(ppa.effective_date, 'Y'),
add_months(trunc(ppa.effective_date, 'Y'),12) - 1
into l_state,
l_qtr_start,
l_qtr_end,
l_year_start,
l_year_end
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
selecting people in the 4th quarter is different to that used for the
first 3 quarters of the tax year. */
if l_state = 'NY' and to_char(l_qtr_end,'MM')= '12' then
/* Period is the last quarter of the year.*/
l_period_start := l_year_start;
select count(*)
into l_sui_exempt
from pay_us_emp_state_tax_rules_f ptax,
pay_us_states pst
where ptax.assignment_id = assignid
and ptax.effective_start_date <= l_qtr_end
and ptax.effective_end_date >= l_qtr_start
and pst.state_code = ptax.state_code
and pst.state_abbrev = l_state
and ptax.sui_exempt = 'Y'
and not exists ( select 'x'
from pay_us_emp_state_tax_rules_f ptax,
pay_us_states pst
where ptax.assignment_id = assignid
and ptax.effective_start_date <= l_qtr_end
and ptax.effective_end_date >= l_qtr_start
and pst.state_code = ptax.state_code
and pst.state_abbrev = l_state
and ptax.sui_exempt = 'N') ;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_utility.set_location('pay_negbal_pkg.procngb',8);
-- insert an interlock to this action
-- Bug fix 1850043
-- hr_nonrun_asact.insint(lockingactid,lockedactid);
sqlstr := 'select paa.rowid
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf, -- #1894165
hr_organization_units hou,
hr_organization_units hou1
where ppa.payroll_action_id = :pactid
and paa.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.business_group_id + 0 = ppa.business_group_id
and paf.assignment_type = ''E''
and paf.effective_start_date = (select max(paf1.effective_start_date)
from per_all_assignments_f paf1 --# 1894165
where paf1.assignment_id = paf.assignment_id
and paf1.business_group_id + 0 = ppa.business_group_id
and paf1.assignment_type = ''E''
and paf1.effective_start_date <= ppa.effective_date
and paf1.effective_end_date >=
decode(pay_negbal_pkg.get_parameter
(''TRANSFER_STATE'',ppa.legislative_parameters),
''NY'',
decode(to_char(ppa.effective_date,''Q''),
4, trunc(ppa.start_date, ''Y''), ppa.start_date
)
, ppa.start_date
)
)
and paa.tax_unit_id = hou.organization_id
and hou1.organization_id = nvl(paf.organization_id,paf.business_group_id) -- # 1894165
order by hou.name,hou1.name,paf.assignment_number
for update of paf.assignment_id';