[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.business_group_id,
pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_STATE'),
ppa.report_type,
pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'LC')
INTO p_year_start,
p_year_end,
p_business_group_id,
p_state_abbrev,
p_report_type,
p_locality_code
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_pactid;
select state_code into l_state_code
from pay_us_states pus
where pus.state_abbrev = p_state_abbrev;
SELECT jurisdiction_code
INTO l_jurisdiction_code
FROM pay_state_rules
WHERE state_code = p_state_abbrev;
SELECT DISTINCT
to_number(paa.serial_number)
FROM ff_archive_item_contexts faic,
ff_archive_items fai,
ff_database_items fdi,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf,
pay_payroll_actions ppa1,
pay_us_states pus
WHERE ppa1.payroll_action_id = :payroll_action_id
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa1.effective_date = ppa.effective_date
AND ppa.report_type = ''YREND''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paf.assignment_id = paa.assignment_id
AND paf.assignment_type = ''E''
AND fdi.user_name = ''A_STATE_ABBREV''
AND fdi.user_entity_id = fai.user_entity_id
AND fai.archive_item_id = faic.archive_item_id
AND fai.context1 = paa.assignment_action_id
AND pus.STATE_ABBREV = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
''TRANSFER_STATE'')
AND fai.value = pus.STATE_ABBREV
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paa.action_status = ''C''
AND nvl(hr_us_w2_rep.get_w2_arch_bal( paa.assignment_action_id,
''A_W2_STATE_WAGES'',
paa.tax_unit_id,
faic.context , 2), 0) > 0
AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
SELECT ''x''
FROM pay_us_state_tax_info_f pustif
WHERE substr(faic.context,1,2) = pustif.state_code
AND ppa.effective_date between pustif.effective_start_date
AND pustif.effective_end_date
AND pustif.sit_exists = ''Y'')
AND NOT EXISTS (
SELECT ''x''
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
AND hoi.org_information_context = ''1099R Magnetic Report Rules''
)
ORDER BY to_number(paa.serial_number)';
SELECT
to_number(paa.serial_number),
paf.assignment_id,
paa.tax_unit_id,
paf.effective_end_date,
paa.assignment_action_id,
sum(fai1.value)
FROM
pay_assignment_actions paa, -- YREND PAA
pay_payroll_actions ppa, -- YREND PPA
per_all_assignments_f paf,
pay_payroll_actions ppa1,
ff_contexts fc1 , --for city context
ff_archive_items fai1, -- city
ff_archive_item_contexts faic1, -- city_context
ff_database_items fdi1 --database_items for City_withheld
--,pay_us_city_tax_info_f puctif
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa1.effective_date = ppa.effective_date
AND ppa.report_type = 'YREND'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paf.assignment_id = paa.assignment_id
AND paf.assignment_type = 'E'
AND fc1.context_name = 'JURISDICTION_CODE'
AND faic1.context_id = fc1.context_id
AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
AND fdi1.user_entity_id = fai1.user_entity_id
AND fai1.context1 = paa.assignment_action_id
AND fai1.archive_item_id = faic1.archive_item_id
AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
AND (c_locality_code IS NULL OR
( c_locality_code IS NOT NULL
AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
WHERE substr(puctif.jurisdiction_code,1,2)||'-000-'||
substr(puctif.jurisdiction_code,8,4)
= substr(ltrim(rtrim(faic1.context)),1,2)||'-000-'||
substr(ltrim(rtrim(faic1.context)),8,4)
AND puctif.jurisdiction_code like substr(c_locality_code,1,2)||'%'||
substr(c_locality_code,8,4)||'%'
AND puctif.effective_start_date < ppa.effective_date
AND puctif.effective_end_date >= ppa.effective_date
)
)
)
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paa.action_status = 'C'
AND paa.serial_number BETWEEN p_stperson AND p_endperson
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND NOT EXISTS
(
SELECT 'x'
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
AND hoi.org_information_context = '1099R Magnetic Report Rules'
)
AND rtrim(ltrim(fai1.value)) <> '0'
GROUP BY paa.serial_number,
paf.assignment_id,
paa.tax_unit_id,
paf.effective_end_date,
paa.assignment_action_id
ORDER BY 1, 3, 4 DESC, 2;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT paa1.assignment_action_id, -- archiver asg action Id
paa1.tax_unit_id, -- archiver Tax Unit Id
paa1.payroll_action_id, -- archiver payroll action id
ppa.payroll_action_id, -- Main Payroll Action Id
paa.assignment_action_id, -- Main Asg Action Id
paa.assignment_id,
ppa.effective_date, -- Date Earned
pay_us_mmref_local_xml.get_parameter('TRANSFER_REPORTING_YEAR',
ppa.legislative_parameters),
pay_us_mmref_local_xml.get_parameter('LC',ppa.legislative_parameters),
pay_us_mmref_local_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and pai.locking_action_id = paa.assignment_action_id
and pai.locked_action_id = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.report_type = 'YREND'
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.effective_date = ppa.effective_date;