[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'; /* Bug:2296797 */
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_qualifier,
ppa.report_type
INTO p_year_start,
p_year_end,
p_business_group_id,
p_state_abbrev,
p_report_type
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_pactid;
SELECT state_code
INTO p_state_code
FROM pay_us_states
WHERE state_abbrev = p_state_abbrev;
SELECT jurisdiction_code
INTO l_jurisdiction_code
FROM pay_state_rules
WHERE state_code = p_state_abbrev;
SELECT hou.organization_id gre
FROM hr_organization_information hoi,
hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id AND
hoi.organization_id = hou.organization_id AND
hoi.org_information_context = 'CLASS' AND
hoi.org_information1 = 'HR_LEGAL' AND
NOT EXISTS (
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = hou.organization_id
AND org_information_context = '1099R Magnetic Report Rules');
SELECT user_entity_id from ff_user_entities
WHERE user_entity_name = 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER';
SELECT payroll_action_id
FROM pay_payroll_actions
WHERE report_type = 'YREND'
AND effective_date = p_year_end
AND start_date = p_year_start
AND business_group_id+0 = p_business_group_id
AND SUBSTR(legislative_parameters,
INSTR(legislative_parameters, 'TRANSFER_GRE=') +
LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
-- ADDED FOLLOWING CHECK CONDITION
AND action_status = 'C';
SELECT '1'
FROM dual
WHERE EXISTS (SELECT '1'
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = cp_payroll_action_id
AND paa.action_status = 'E'
)
AND NOT EXISTS ( SELECT '1'
FROM pay_action_parameters
WHERE parameter_name = 'FORCE_MAG_REPORT'
AND INSTR(parameter_value, 'E') > 0
);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT '1'
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = cp_payroll_action_id
AND paa.action_status = 'M')
AND NOT EXISTS (SELECT '1'
FROM pay_action_parameters
WHERE parameter_name = 'FORCE_MAG_REPORT'
AND INSTR(parameter_value, 'R') > 0
);
SELECT user_entity_id
FROM ff_database_items fdi
WHERE user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' ;
SELECT context_id
FROM ff_contexts
WHERE context_name = 'TAX_UNIT_ID';
SELECT context_id
FROM ff_contexts
WHERE context_name = 'JURISDICTION_CODE';
SELECT 'Y'
FROM ff_archive_item_contexts con3,
ff_archive_item_contexts con2,
ff_contexts fc3,
ff_contexts fc2,
ff_archive_items target,
ff_database_items fdi
WHERE target.context1 = to_char(cp_payroll_action_id)
/* context of payroll_action_id */
AND fdi.user_name = 'A_FIPS_CODE_JD'
AND target.user_entity_id = fdi.user_entity_id
AND fc2.context_name = 'TAX_UNIT_ID'
AND con2.archive_item_id = target.archive_item_id
AND con2.context_id = fc2.context_id
AND ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
AND fc3.context_name = 'JURISDICTION_CODE'
AND con3.archive_item_id = target.archive_item_id
AND con3.context_id = fc3.context_id
AND substr(ltrim(rtrim(con3.context)),1,2) = p_state_code;
SELECT SUBSTR(legislative_parameters,INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
+ LENGTH('TRANSFER_TRANS_LEGAL_CO_ID='),
(INSTR(legislative_parameters, 'TRANSFER_DATE=')
- INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
- LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')-1 ))
FROM pay_payroll_actions
WHERE report_type = 'W2'
AND effective_date = p_year_end
AND report_qualifier = p_state_abbrev
AND business_group_id = p_business_group_id
AND report_category IN ('RG', 'RM') ;
SELECT DISTINCT paf.person_id
FROM per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1
WHERE ppa1.payroll_action_id = :payroll_action_id
AND ppa.report_type = ''YREND''
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND ppa.start_date = ppa1.start_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = ''C''
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 hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context =
''1099R Magnetic Report Rules'')
ORDER BY paf.person_id
';
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
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 fai.value = ppa1.report_qualifier
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,
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)
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
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 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 fai.value = ppa1.report_qualifier
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paa.action_status = 'C'
and paf.person_id BETWEEN p_stperson AND p_endperson
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 exists (select 'x'
from hr_organization_information hou
where hou.organization_id = paa.tax_unit_id
and hou.org_information16 = 'P'
and hou.org_information_context = 'W2 Reporting Rules')
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 1, 3, 4 DESC, 2
FOR UPDATE OF paf.assignment_id;
SELECT
to_number(paa.serial_number),
paf.assignment_id,
paa.tax_unit_id,
paf.effective_end_date,
paa.assignment_action_id,
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)
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
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 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 fai.value = ppa1.report_qualifier
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paa.action_status = 'C'
and paf.person_id BETWEEN p_stperson AND p_endperson
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 1, 3, 4 DESC, 2
FOR UPDATE OF paf.assignment_id;
SELECT paf.person_id,
paf.assignment_id,
Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
paf.effective_end_date,
paa.assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa1
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type = 'YREND'
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND ppa.start_date = ppa1.start_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paf.assignment_id = paa.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND paf.assignment_type = 'E'
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
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 1, 3, 4 DESC, 2
FOR UPDATE OF paf.assignment_id;
select to_number(fai.value) value
from ff_archive_item_contexts faic,
ff_archive_items fai,
ff_contexts fc,
ff_database_items fdi
where fdi.user_name = p_user_name
and fc.context_name = 'TAX_UNIT_ID'
and fai.context1 = to_char(p_assignment_action_id)
and fai.user_entity_id = fdi.user_entity_id
and faic.archive_item_id = fai.archive_item_id
and faic.context_id = fc.context_id
and faic.context = to_char(p_tax_unit_id)
and faic.sequence_no = 1;
select to_number(fai.value) value
from ff_archive_item_contexts faic,
ff_archive_items fai,
ff_contexts fc,
ff_database_items fdi
where fdi.user_name IN ('A_REGULAR_EARNINGS_PER_GRE_YTD',
'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',
'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD',
'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',
'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD')
and fc.context_name = 'TAX_UNIT_ID'
and fai.context1 = to_char(p_assignment_action_id)
and fai.user_entity_id = fdi.user_entity_id
and faic.archive_item_id = fai.archive_item_id
and faic.context_id = fc.context_id
and faic.context = to_char(p_tax_unit_id)
and faic.sequence_no = 1;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
--update serial number for highly compensated people for the
--state W2.
/*IF l_report_type = 'STW2' THEN
hr_utility.set_location(
'pay_us_mmref_reporting.create_assignement_act', 80);
UPDATE pay_assignment_actions
SET serial_number = 999999
WHERE assignment_action_id = lockingactid;
SELECT user_entity_id
FROM ff_database_items
WHERE user_name in ( 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER',
'A_TAX_UNIT_NAME');
SELECT user_entity_id
FROM ff_database_items
WHERE user_name = 'TAX_UNIT_ADDRESS_LINE_1';
SELECT value
INTO l_ein_val
FROM ff_archive_items fai,
ff_contexts fc,
ff_archive_item_contexts faic
WHERE fai.context1 = to_char(p_pactid)
AND user_entity_id = c_id.user_entity_id
AND faic.archive_item_id = fai.archive_item_id
AND faic.context = to_char(l_gre)
AND fc.context_name = 'TAX_UNIT_ID'
AND fc.context_id = faic.context_id ;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
SELECT value
FROM ff_archive_items fai,
ff_archive_item_contexts faic,
ff_archive_item_contexts faic1
WHERE context1 = to_char(p_pactid)
AND user_entity_id = l_sit_state_id
AND faic.archive_item_id = fai.archive_item_id
AND faic1.archive_item_id = fai.archive_item_id
AND faic.context = to_char(p_tax_unit)
and faic1.context = p_jurisdictions || '-000-0000';
IS select state_code
from pay_us_states
WHERE state_abbrev = cp_state;
SELECT DISTINCT paa.tax_unit_id unit_id,ppa1.payroll_action_Id payroll_action,name
FROM pay_assignment_actions paa
,pay_payroll_actions ppa1 /* year End Pre-process for GRE */
,pay_payroll_actions ppa /* Year End Pre-process for W-2 */
,hr_organization_units hou
WHERE
ppa.payroll_action_id = cp_payroll_action_id /* W2 payroll_action_id */
and ppa.payroll_action_id = paa.payroll_action_id
and ppa1.legislative_parameters like ltrim(rtrim(to_char(paa.tax_unit_id))) || ' TRANS%'
and ppa1.effective_date = ppa.effective_date
and ppa1.report_type = 'YREND'
and ppa1.report_qualifier = 'FED'
and hou.organization_id = paa.tax_unit_id;
SELECT org_information17 from hr_organization_information
WHERE org_information_context = 'W2 Reporting Rules'
AND organization_id = cp_tax_unit_id;
SELECT target.value
FROM
ff_archive_item_contexts con3,
ff_archive_item_contexts con2,
ff_contexts fc3,
ff_contexts fc2,
ff_archive_items target,
ff_database_items fdi
WHERE target.context1 = to_char(cp_payroll_action_id)
/* context of payroll_action_id */
and fdi.user_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID'
and target.user_entity_id = fdi.user_entity_id
and fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = target.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = ltrim(rtrim(to_char(cp_tax_unit_id)))
and fc3.context_name = 'JURISDICTION_CODE'
and con3.archive_item_id = target.archive_item_id
and con3.context_id = fc3.context_id
and substr(ltrim(rtrim(con3.context)),1,2) = ltrim(rtrim(cp_state_code));
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER';
SELECT 'RG'
FROM DUAL
WHERE EXISTS
(SELECT NULL
FROM pay_payroll_actions ppa,
ff_archive_items fai
WHERE ppa.report_type = 'YREND'
AND ppa.report_qualifier = 'FED'
AND ppa.report_category = 'RT'
AND ppa.effective_date = p_effective_date
AND ppa.business_group_id = p_business_group_id
AND fai.context1 = ppa.payroll_action_id
AND fai.user_entity_id = p_user_entity_id
AND fai.value = 'Y'
);