[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hoi.org_information3 wage_plan
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = p_transfer_state;
SELECT DISTINCT aei_information3 wage_plan
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND paei.aei_information1 = p_transfer_state
AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
SELECT count(*) ct
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = p_org_information_context
AND org_information1 = p_org_information1
AND org_information4 = 'Y';
SELECT count(*) ct
FROM (select distinct
a.organization_id,
a.org_information1,
a.org_information3
FROM hr_organization_information a
WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
WHERE b.organization_id = p_organization_id
AND 1 < ( SELECT count(*)
FROM hr_organization_information orgi
WHERE organization_id = p_organization_id
AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND org_information1 = b.org_information1
AND org_information3 = b.org_information3);
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT target.ORG_INFORMATION8 yes_no
FROM hr_organization_information target
WHERE target.organization_id = l_tax_unit_id
AND target.org_information_context = 'Federal Tax Rules';
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE';
SELECT arch.value
FROM ff_archive_items arch
WHERE arch.user_entity_id = p_user_entity_id
AND arch.context1 = p_assignment_action_id;
select distinct person_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select max(ppa.effective_date)
from per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.effective_date between p_quarter_start_date
and p_quarter_end_date
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = asg.assignment_id
and paa.action_status <> 'S'
and asg.effective_end_date >= p_quarter_start_date
and asg.effective_start_date <= p_quarter_end_date
and asg.business_group_id = ppa.business_group_id
and asg.assignment_type = 'E'
and paa.tax_unit_id = p_tax_unit_id
and asg.assignment_id = p_assignment_id;
select distinct pest.state_code,pus.state_abbrev
from per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
pay_us_emp_state_tax_rules_f pest,
pay_us_states pus
where paaf.person_id = p_person_id
and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and paaf.effective_end_date >= p_year_start_date
and paaf.effective_start_date <= p_quarter_end_date
and hsck.segment1=to_char(p_tax_unit_id)
and pest.assignment_id = paaf.assignment_id
and pest.business_group_id = paaf.business_group_id
and pest.effective_end_date >= p_year_start_date
and pest.effective_start_date <= p_quarter_end_date
and pus.state_code=pest.state_code;
select distinct substr(peev.screen_entry_value,1,2),pus.state_abbrev
from per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_balance_types pbt,
pay_balance_feeds_f pbf,
pay_element_links_f pel,
pay_us_states pus
where paaf.person_id=p_person_id
and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and paaf.effective_end_date >= p_year_start_date
and paaf.effective_start_date <= p_quarter_end_date
and hsck.segment1=to_char(p_tax_unit_id)
and pee.assignment_id = paaf.assignment_id
and pee.effective_end_date >= p_year_start_date
and pee.effective_start_date <= p_quarter_end_date
and pee.element_link_id = pel.element_link_id
and pee.element_entry_id = peev.element_entry_id
and paaf.business_group_id = pel.business_group_id
and pel.effective_end_date >= p_year_start_date
and pel.effective_start_date <= p_quarter_end_date
and pel.element_type_id = piv1.element_type_id
and piv1.name='Jurisdiction'
and piv1.effective_end_date >= p_year_start_date
and piv1.effective_start_date <= p_quarter_end_date
and piv1.input_value_id = peev.input_value_id
and pbt.balance_name ='SUI ER Taxable'
and pbt.balance_type_id = pbf.balance_type_id
and pbf.input_value_id = piv2.input_value_id
and piv2.effective_end_date >= p_year_start_date
and piv2.effective_start_date <= p_quarter_end_date
and piv2.element_type_id = pee.element_type_id
and pus.state_code=substr(peev.screen_entry_value,1,2)
minus
select distinct pest.state_code,pus.state_abbrev
from per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
pay_us_emp_state_tax_rules_f pest,
pay_us_states pus
where paaf.person_id = p_person_id
and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
and paaf.effective_end_date >= p_year_start_date
and paaf.effective_start_date <= p_quarter_end_date
and hsck.segment1=to_char(p_tax_unit_id)
and pest.assignment_id = paaf.assignment_id
and pest.business_group_id = paaf.business_group_id
and pest.effective_end_date >= p_year_start_date
and pest.effective_start_date <= p_quarter_end_date
and pus.state_code=pest.state_code;
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.legislation_code = 'US'
and pbt.balance_name = 'SUI ER Taxable'
and pbd.legislation_code = 'US'
and pbd.dimension_name = p_dimension_name
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT least(max(effective_end_date),p_reporting_date)
INTO l_effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND assignment_type = 'E'
AND effective_end_date >= l_quarter_start_date ;
select fnd_number.canonical_to_number(target.value)
from
ff_archive_items target,ff_user_entities fue
where target.user_entity_id = fue.user_entity_id
and fue.user_entity_name='A_SQWL_LOC_QTR_END'
and target.context1 = to_char(p_assignment_action_id);
Select lpad(nvl(lei.lei_information1,'0001'),4,'0')
from per_gen_hierarchy pgh
,per_gen_hierarchy_versions pghv
,per_gen_hierarchy_nodes pghn -- parent organization
,per_gen_hierarchy_nodes pghn2 -- establishment organizations
,hr_organization_information hoi
,hr_organization_units hou
,hr_locations loc
,hr_location_extra_info lei
where pgh.hierarchy_id = Pay_Magtape_Generic.Get_Parameter_Value(
'TRANSFER_HIERARCHY_ID') --parameter p_hierarchy_id
and pghv.HIERARCHY_VERSION_id =Pay_Magtape_Generic.Get_Parameter_Value(
'TRANSFER_HIERARCHY_VERSION') --parameter p_hierarchy_verision_number
and pgh.hierarchy_id = pghv.hierarchy_id
and pghv.hierarchy_version_id = pghn.hierarchy_version_id
and pghn.node_type = 'PAR'
and pghn.entity_id = hou.organization_id
and hou.business_group_id = pgh.business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'MWR_Info'
and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
and pghn.business_group_id = pghn2.business_group_id
and pghn2.node_type = 'EST'
and pghn2.entity_id = loc.location_id
and loc.region_2 = Pay_Magtape_Generic.Get_Parameter_Value(
'TRANSFER_STATE')
and loc.location_id = lei.location_id
and lei.information_type = 'Multi Work Site Information'
and loc.location_id = cp_location_id;
SELECT user_entity_id
FROM ff_database_items
WHERE user_name = p_dbi_name;
SELECT NVL(target.value, ' ')
FROM ff_archive_items target
WHERE target.user_entity_id = p_user_entity_id
AND target.context1 = p_assignment_action_id;
select count(distinct tax_unit_id)
into lv_employer_count
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id ;
select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')))
into lv_employer_total_wages
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_database_items fdi,
ff_database_items fdi1
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' ;
select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')) - to_number(nvl(fai2.value, '0')))
into lv_excess_wages
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_archive_items fai2,
ff_database_items fdi,
ff_database_items fdi1,
ff_database_items fdi2
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
and fai2.context1 = fai1.context1
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD';
select nvl(sum(to_number(nvl(fai.value, '0'))),0),
nvl(sum(to_number(nvl(fai1.value, '0'))),0),
nvl(sum(to_number(nvl(fai2.value, '0'))),0),
nvl(sum(to_number(nvl(fai3.value, '0'))),0)
into p_sui_subj,
p_sui_pre_tax,
p_sui_taxable,
p_sui_gross
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_archive_items fai2,
ff_archive_items fai3,
ff_database_items fdi,
ff_database_items fdi1,
ff_database_items fdi2,
ff_database_items fdi3
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_MONTH'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_MONTH'
and fai2.context1 = fai1.context1
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_MONTH'
and fai3.context1 = fai1.context1
and fai3.user_entity_id = fdi3.user_entity_id
and fdi3.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_MONTH'
and length(translate(trim(fai.value),' .0123456789',' ')) is null
and length(translate(trim(fai2.value),' .0123456789',' ')) is null ;
select paa.assignment_action_id paa_id, fdi.user_name dbi_name, nvl(fai.value, '0') value
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_database_items fdi
where ppa.payroll_action_id = c_payroll_action_id --1436067
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = c_tax_unit_id --7896
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name in
('A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',
'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD',
'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD',
'A_SUI_ER_GROSS_PER_JD_GRE_QTD',
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD',
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD',
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD',
'A_SIT_WITHHELD_PER_JD_GRE_QTD',
'A_SDI_EE_PRE_TAX_REDNS_PER_JD_GRE_QTD',
'A_SDI_EE_SUBJ_WHABLE_PER_JD_GRE_QTD',
'A_SDI_EE_TAXABLE_PER_JD_GRE_QTD',
'A_SCL_ASG_US_CA_WAGE_PLAN_CODE'
)
order by paa.assignment_action_id;