[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'W2'
and report_qualifier = 'FED'
and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
and ppa.payroll_action_id = paa.payroll_action_id
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(value),0) * 100,
'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' ,nvl(sum(value),0) * 100,
'A_FIT_SUBJ_WHABLE_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_FIT_WITHHELD_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_SS_EE_TAXABLE_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
'A_SS_EE_WITHHELD_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
'A_W2_BOX_7_PER_GRE_YTD', nvl(sum(value),0) * 100 ,
'A_EIC_ADVANCE_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_W2_DEPENDENT_CARE_PER_GRE_YTD',nvl(sum(value),0) * 100 ,
'A_W2_401K_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_W2_403B_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_W2_408K_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_457_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_501C_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_MILITARY_HOUSING_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_NONQUAL_PLAN_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_NONQUAL_457_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_W2_BOX_11_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_FIT_3RD_PARTY_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_NONQUAL_STOCK_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_HSA_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_NONTAX_COMBAT_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD',nvl(sum(value),0) * 100,
'A_W2_BOX_8_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_MSA_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_408P_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_ADOPTION_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD', nvl(sum(value),0) * 100,
'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD', nvl(sum(value),0) * 100
, 'A_W2_ROTH_401K_PER_GRE_YTD', nvl(sum(value),0) * 100
, 'A_W2_ROTH_403B_PER_GRE_YTD', nvl(sum(value),0) * 100
) 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 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 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'
)
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(value),0) * 100,
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',nvl(sum(value),0) * 100,
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',nvl(sum(value),0) * 100,
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD',nvl(sum(value),0) * 100
) 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.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 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 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;