eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_US_W2_LOCALITY_V

Source

View Text - Preformatted


SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'CITY' tax_type, pus.state_abbrev, decode(faic.context,'33-119-3230','YONK' ,fai.value) locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_CITY_LOCAL_WAGES', paa.tax_unit_id,faic.context, 11) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_CITY_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 11) w2_box_21 FROM pay_us_states pus, ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_CITY_NAME' 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 faic.context not in ('33-005-2010', '33-047-2010', '33-061-2010' ,'33-081-2010', '33-085-2010') AND substr(faic.context,1,2) = pus.state_code AND EXISTS (select 'x' from pay_us_city_tax_info_f puctif where puctif.jurisdiction_code = faic.context and ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date and puctif.city_tax = 'Y') UNION ALL SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'CITY' tax_type, 'NY', 'NYC' locality_name, '33-000-2010' jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_LOCAL_WAGES', paa.tax_unit_id, '33-000-2010' , 11) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id, '33-000-2010' , 11) w2_box_21 FROM pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND exists ( select 'Y' from ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi where fdi.user_name = 'A_CITY_NAME' and fdi.user_entity_id = fai.user_entity_id and fai.archive_item_id = faic.archive_item_id and faic.context in ('33-005-2010','33-047-2010','33-061-2010', '33-081-2010', '33-085-2010') and fai.context1 = paa.assignment_action_id and exists (select 'x' from pay_us_city_tax_info_f puctif where puctif.jurisdiction_code = faic.context and ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date and puctif.city_tax = 'Y')) UNION ALL SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'COUNTY' tax_type, state_abbrev, fai.value locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_COUNTY_LOCAL_WAGES' , paa.tax_unit_id, faic.context, 6) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 6) w2_box_21 FROM pay_us_states pus, ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_COUNTY_NAME' 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 EXISTS (select 'x' from pay_us_county_tax_info_f puctif where puctif.jurisdiction_code = faic.context and ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date and puctif.county_tax = 'Y') AND substr(faic.context,1,2) = pus.state_code UNION ALL SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'CITY SCHOOL' tax_type, pus.state_abbrev, fai.value locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SCHOOL_LOCAL_WAGES', paa.tax_unit_id,faic.context, 8) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id,faic.context, 8) w2_box_21 FROM pay_us_states pus, ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_CITY_SD_NAME' 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 substr(faic.context,1,2) = pus.state_code /* AND EXISTS (select 'x' from pay_us_city_tax_info_f puctif where puctif.jurisdiction_code = faic.context and ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date and puctif.school_tax = 'Y') */ UNION ALL SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'COUNTY SCHOOL' tax_type, pus.state_abbrev, fai.value locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal( paa.assignment_action_id, 'A_SCHOOL_LOCAL_WAGES',paa.tax_unit_id,faic.context, 8) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' ,paa.tax_unit_id,faic.context, 8) w2_box_21 FROM pay_us_states pus, ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_COUNTY_SD_NAME' 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 substr(faic.context,1,2) = pus.state_code /* AND EXISTS (select 'x' from pay_us_county_tax_info_f puctif where puctif.jurisdiction_code = faic.context and ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date and puctif.school_tax = 'Y') */ UNION ALL SELECT to_number(to_char(ppa.effective_date,'YYYY')) year, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, 'SDI' tax_type, fai.value state_abbrev, fai.value||' SDI' locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SDI_EE_TAXABLE_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 2) w2_box_20, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 2) w2_box_21 FROM ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id 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 substr(faic.context,1,2) <> '31'

View Text - HTML Formatted

SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'CITY' TAX_TYPE
, PUS.STATE_ABBREV
, DECODE(FAIC.CONTEXT
, '33-119-3230'
, 'YONK'
, FAI.VALUE) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 11) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 11) W2_BOX_21
FROM PAY_US_STATES PUS
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_CITY_NAME'
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 FAIC.CONTEXT NOT IN ('33-005-2010'
, '33-047-2010'
, '33-061-2010'
, '33-081-2010'
, '33-085-2010')
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE
AND EXISTS (SELECT 'X'
FROM PAY_US_CITY_TAX_INFO_F PUCTIF
WHERE PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE
AND PUCTIF.CITY_TAX = 'Y') UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'CITY' TAX_TYPE
, 'NY'
, 'NYC' LOCALITY_NAME
, '33-000-2010' JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, '33-000-2010'
, 11) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '33-000-2010'
, 11) W2_BOX_21
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND EXISTS ( SELECT 'Y'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE FDI.USER_NAME = 'A_CITY_NAME'
AND FDI.USER_ENTITY_ID = FAI.USER_ENTITY_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAIC.CONTEXT IN ('33-005-2010'
, '33-047-2010'
, '33-061-2010'
, '33-081-2010'
, '33-085-2010')
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND EXISTS (SELECT 'X'
FROM PAY_US_CITY_TAX_INFO_F PUCTIF
WHERE PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE
AND PUCTIF.CITY_TAX = 'Y')) UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'COUNTY' TAX_TYPE
, STATE_ABBREV
, FAI.VALUE LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_COUNTY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 6) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 6) W2_BOX_21
FROM PAY_US_STATES PUS
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_COUNTY_NAME'
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 EXISTS (SELECT 'X'
FROM PAY_US_COUNTY_TAX_INFO_F PUCTIF
WHERE PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE
AND PUCTIF.COUNTY_TAX = 'Y')
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'CITY SCHOOL' TAX_TYPE
, PUS.STATE_ABBREV
, FAI.VALUE LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_BOX_21
FROM PAY_US_STATES PUS
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_CITY_SD_NAME'
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 SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE /*
AND EXISTS (SELECT 'X'
FROM PAY_US_CITY_TAX_INFO_F PUCTIF
WHERE PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE
AND PUCTIF.SCHOOL_TAX = 'Y') */ UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'COUNTY SCHOOL' TAX_TYPE
, PUS.STATE_ABBREV
, FAI.VALUE LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL( PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_BOX_21
FROM PAY_US_STATES PUS
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_COUNTY_SD_NAME'
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 SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE /*
AND EXISTS (SELECT 'X'
FROM PAY_US_COUNTY_TAX_INFO_F PUCTIF
WHERE PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE
AND PUCTIF.SCHOOL_TAX = 'Y') */ UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, 'SDI' TAX_TYPE
, FAI.VALUE STATE_ABBREV
, FAI.VALUE||' SDI' LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_TAXABLE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) W2_BOX_20
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) W2_BOX_21
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
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 SUBSTR(FAIC.CONTEXT
, 1
, 2) <> '31'