[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', 'MT') ;
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
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
';
p_sqlstr := '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 '||
'OR '||
'exists (select ''x'' '||
'from ff_contexts fc1, '||
'ff_archive_items fai1, '||
'ff_archive_item_contexts faic1, '||
'ff_database_items fdi1 '||
'where fc1.context_name = ''JURISDICTION_CODE'' '||
'and fc1.context_id = faic1.context_id '||
'and fdi1.user_name = ''A_COUNTY_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 substr(faic1.context,1,2) = substr(faic.context,1,2) '||
'and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, '||
'''A_COUNTY_WITHHELD_PER_JD_GRE_YTD'', '||
'paa.tax_unit_id, '||
'faic1.context , 6),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)';
p_sqlstr := '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 '||
'OR '||
'exists (select ''x'' '||
'from ff_contexts fc1, '||
'ff_archive_items fai1, '||
'ff_archive_item_contexts faic1, '||
'ff_database_items fdi1 '||
'where fc1.context_name = ''JURISDICTION_CODE'' '||
'and fc1.context_id = faic1.context_id '||
'and fdi1.user_name = ''A_SCHOOL_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 substr(faic1.context,1,2) = substr(faic.context,1,2) '||
'and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, '||
'''A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'', '||
'paa.tax_unit_id, '||
'faic1.context , 8),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 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 paa.serial_number BETWEEN to_char(p_stperson) AND to_char(p_endperson)
and to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
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 )
or
( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_SIT_WITHHELD_PER_JD_GRE_YTD', /* 6809739 */
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 /*+ index(ppa pay_payroll_actions_N52) */
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,
pay_population_ranges ppr
WHERE
ppa1.payroll_action_id = p_pactid
and ppa1.payroll_action_id=ppr.payroll_action_id
AND ppr.chunk_number = p_chunk
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 paa.serial_number BETWEEN to_char(p_stperson) AND to_char(p_endperson)
-- and to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
AND to_number(paa.serial_number) =ppr.person_id
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 )
or
( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_SIT_WITHHELD_PER_JD_GRE_YTD', /* 6809739 */
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 )
or
(nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0))
/* Commenting it due to Performance Issue Bug# 5630156 */
-- 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 /*+ index(ppa pay_payroll_actions_N52) */
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,
pay_population_ranges ppr
WHERE
ppa1.payroll_action_id = p_pactid
and ppa1.payroll_action_id=ppr.payroll_action_id
AND ppr.chunk_number = p_chunk
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 =ppr.person_id
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 )
or
(nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0))
/* Commenting it due to Performance Issue Bug# 5630156 */
-- 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
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 )
or
(
nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0))
OR
exists (select 'x'
from ff_contexts fc1,
ff_archive_items fai1,
ff_archive_item_contexts faic1,
ff_database_items fdi1
where fc1.context_name = 'JURISDICTION_CODE'
and fc1.context_id = faic1.context_id
and fdi1.user_name = 'A_COUNTY_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 substr(faic1.context,1,2) = substr(faic.context,1,2)
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_COUNTY_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic1.context , 6),0) > 0)
)
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 ;
SELECT /*+ index(ppa pay_payroll_actions_N52) */
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,
pay_population_ranges ppr
WHERE
ppa1.payroll_action_id = p_pactid
and ppa1.payroll_action_id=ppr.payroll_action_id
AND ppr.chunk_number = p_chunk
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 =ppr.person_id
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 )
or
(
nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0))
OR
exists (select 'x'
from ff_contexts fc1,
ff_archive_items fai1,
ff_archive_item_contexts faic1,
ff_database_items fdi1
where fc1.context_name = 'JURISDICTION_CODE'
and fc1.context_id = faic1.context_id
and fdi1.user_name = 'A_COUNTY_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 substr(faic1.context,1,2) = substr(faic.context,1,2)
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_COUNTY_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic1.context , 6),0) > 0)
)
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 ;
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 )
or
(
nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0) )
OR
exists (select 'x'
from ff_contexts fc1,
ff_archive_items fai1,
ff_archive_item_contexts faic1,
ff_database_items fdi1
where fc1.context_name = 'JURISDICTION_CODE'
and fc1.context_id = faic1.context_id
and fdi1.user_name = 'A_SCHOOL_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 substr(faic1.context,1,2) = substr(faic.context,1,2)
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic1.context , 8),0) > 0)
)
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 ;
SELECT /*+ index(ppa pay_payroll_actions_N52) */
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,
pay_population_ranges ppr
WHERE
ppa1.payroll_action_id = p_pactid
and ppa1.payroll_action_id=ppr.payroll_action_id
AND ppr.chunk_number = p_chunk
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 =ppr.person_id
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 )
or
(
nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic.context , 2),0) > 0) )
OR
exists (select 'x'
from ff_contexts fc1,
ff_archive_items fai1,
ff_archive_item_contexts faic1,
ff_database_items fdi1
where fc1.context_name = 'JURISDICTION_CODE'
and fc1.context_id = faic1.context_id
and fdi1.user_name = 'A_SCHOOL_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 substr(faic1.context,1,2) = substr(faic.context,1,2)
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
paa.tax_unit_id,
faic1.context , 8),0) > 0)
)
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 ;
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 paa.serial_number between to_char(p_stperson) AND to_char(p_endperson)
AND to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
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 /*+ index(ppa pay_payroll_actions_N52) */ 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,
pay_population_ranges ppr
WHERE ppa1.payroll_action_id = p_pactid
and ppa1.payroll_action_id=ppr.payroll_action_id
AND ppr.chunk_number = p_chunk
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 paa.serial_number between to_char(p_stperson) AND to_char(p_endperson)
AND to_number(paa.serial_number) =ppr.person_id
AND paf.person_id =ppr.person_id
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 parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
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'
);
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'
);
update pay_assignment_actions
set SERIAL_NUMBER = 'E999999999'
where assignment_action_id = p_assignment_action_id;
SELECT federal_ein
,tax_unit_name
FROM pay_us_w2_tax_unit_v put
WHERE tax_unit_id = c_tax_unit_id
AND year = c_tax_year;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE tax_unit_id = c_tax_unit_id
AND assignment_action_id = c_assignment_action_id;
select count(*)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and effective_date = to_date('31/12/'|| cp_tax_year, 'dd/mm/yyyy')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and NVL(paa.serial_number, 'S') <> 'E999999999'
and paa.tax_unit_id = cp_tax_unit_id;
select count(*)
from pay_payroll_actions ppa
,pay_assignment_actions paa
where ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and ppa.effective_date = to_date('31/12/'|| cp_tax_year,'dd/mm/yyyy')
and ppa.payroll_action_id = paa.payroll_action_id
and NVL(paa.serial_number,'S') = 'E999999999'
and paa.action_status = 'C'
and paa.tax_unit_id = cp_tax_unit_id;
SELECT user_entity_name,
DECODE(fue.user_entity_name,
'A_REGULAR_EARNINGS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
'A_FIT_SUBJ_WHABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_FIT_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_SS_EE_TAXABLE_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SS_EE_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_BOX_7_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_EIC_ADVANCE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_DEPENDENT_CARE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_401K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_403B_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_408K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_457_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_501C_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_MILITARY_HOUSING_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_NONQUAL_PLAN_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_W2_NONQUAL_457_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_BOX_11_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_FIT_3RD_PARTY_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_NONQUAL_STOCK_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_HSA_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_NONTAX_COMBAT_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_BOX_8_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_W2_MSA_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_408P_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_ADOPTION_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_W2_ROTH_401K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_W2_ROTH_403B_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
'A_SS_ER_W11_TAXABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0),
'A_W2_HEALTH_COVERAGE_PER_GRE_YTD', nvl(sum(to_number(value)),0), --Bug 13497022
'A_W2_ROTH_457B_PER_GRE_YTD', nvl(sum(to_number(value)),0) --Bug 13497022
) val
FROM ff_archive_items fai,
pay_action_interlocks pai,
pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_user_entities fue
where ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and paa.action_status = 'C'
and NVL(paa.serial_number, 'S') <> 'E999999999'
and paa.assignment_action_id = pai.locking_action_id
and fai.context1 = pai.locked_action_id
and fai.user_entity_id = fue.user_entity_id
and fue.user_entity_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' ,
'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' ,
'A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD',
'A_FIT_WITHHELD_PER_GRE_YTD',
'A_SS_EE_TAXABLE_PER_GRE_YTD',
'A_SS_EE_WITHHELD_PER_GRE_YTD',
'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD',
'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD',
'A_W2_BOX_7_PER_GRE_YTD',
'A_EIC_ADVANCE_PER_GRE_YTD',
'A_W2_DEPENDENT_CARE_PER_GRE_YTD',
'A_W2_401K_PER_GRE_YTD',
'A_W2_403B_PER_GRE_YTD',
'A_W2_408K_PER_GRE_YTD',
'A_W2_457_PER_GRE_YTD',
'A_W2_501C_PER_GRE_YTD',
'A_W2_MILITARY_HOUSING_PER_GRE_YTD',
'A_W2_NONQUAL_PLAN_PER_GRE_YTD',
'A_W2_NONQUAL_457_PER_GRE_YTD',
'A_W2_BOX_11_PER_GRE_YTD',
'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD',
'A_FIT_3RD_PARTY_PER_GRE_YTD',
'A_W2_NONQUAL_STOCK_PER_GRE_YTD',
'A_W2_HSA_PER_GRE_YTD',
'A_W2_NONTAX_COMBAT_PER_GRE_YTD',
'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD',
'A_W2_BOX_8_PER_GRE_YTD',
/* Sum of */
'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD',
'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD',
'A_W2_MSA_PER_GRE_YTD',
'A_W2_408P_PER_GRE_YTD',
'A_W2_ADOPTION_PER_GRE_YTD',
'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD',
'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD',
'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD',
'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD',
'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD',
'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD',
'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD'
, 'A_W2_ROTH_401K_PER_GRE_YTD'
, 'A_W2_ROTH_403B_PER_GRE_YTD'
, 'A_SS_ER_W11_TAXABLE_PER_GRE_YTD'
, 'A_W2_HEALTH_COVERAGE_PER_GRE_YTD' --Bug 13497022
, 'A_W2_ROTH_457B_PER_GRE_YTD' --Bug 13497022
)
group by fue.user_entity_name;
SELECT
fue.user_entity_name,decode(fue.user_entity_name,
'A_SIT_WITHHELD_PER_JD_GRE_YTD' ,nvl(sum(to_number(value)),0) ,
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0)
) val
FROM ff_archive_item_contexts faic
,ff_archive_items fai
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_interlocks pai
,ff_user_entities fue
WHERE
ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and ppa.effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = pai.locking_action_id
and fai.context1 = pai.locked_action_id
and context = '72-000-0000'
and fai.archive_item_id = faic.archive_item_id
and fai.user_entity_id = fue.user_entity_id
and paa.tax_unit_id = cp_tax_unit_id
and fue.user_entity_name in ('A_SIT_WITHHELD_PER_JD_GRE_YTD',
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD')
and paa.action_status = 'C'
group by fue.user_entity_name;
SELECT
fue.user_entity_name,decode(fue.user_entity_name,
'A_SIT_WITHHELD_PER_JD_GRE_YTD' ,nvl(sum(to_number(value)),0) ,
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0),
'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0)) val
FROM ff_archive_item_contexts faic
,ff_archive_items fai
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_interlocks pai
,ff_user_entities fue
WHERE
ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and ppa.effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = pai.locking_action_id
and fai.context1 = pai.locked_action_id
and fai.archive_item_id = faic.archive_item_id
and fai.user_entity_id = fue.user_entity_id
and paa.tax_unit_id = cp_tax_unit_id
and case when fue.user_entity_name = 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' then to_char(cp_tax_unit_id)
else '72-000-0000' end = context
and fue.user_entity_name in ('A_SIT_WITHHELD_PER_JD_GRE_YTD',
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD' ,
'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' )
and paa.action_status = 'C'
and exists (select 'Y'
from ff_user_entities fai1, ff_archive_items fue1
where fai1.user_entity_name = 'A_STATE_ABBREV'
and fai1.user_entity_id = fue1.user_entity_id
and fue1.value = 'PR'
and fai1.user_entity_id = fue1.user_entity_id
and fue1.context1 = fai.context1 )
group by fue.user_entity_name; */
select count(*)
from pay_payroll_actions ppa
,pay_assignment_actions paa
,ff_archive_items fai
where ppa.report_type = 'W2'
and ppa.report_qualifier = 'FED'
and ppa.report_category = 'MT'
and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = fai.context1
and name is not null
and name like 'TRANSFER_RO_TOTAL'
and paa.tax_unit_id = cp_tax_unit_id
group by tax_unit_id;