View: PAY_US_LOCALITY_W2C_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_US_LOCALITY_W2C_V

View Text

SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE IN ('YREND'
, 'W2C_PRE_PROCESS')
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
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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 UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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 UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PPA.EFFECTIVE_DATE
, 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_LOCAL_WAGES
, 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_LOCAL_INCOME_TAX
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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) NOT IN ( '31'
, '33')

Columns