View: PAY_US_LOCALITY_W2_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_US_LOCALITY_W2_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
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 FAIC.CONTEXT NOT IN ('26-047-0690'
, '26-037-0690'
, '26-095-0690'
, '26-165-0690')
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' 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
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 ) 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
, 'MO'
, 'KANSAS CITY' LOCALITY_NAME
, '26-000-0690' JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, '26-000-0690'
, 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
, '26-000-0690'
, 11) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 ('26-047-0690'
, '26-037-0690'
, '26-095-0690'
, '26-165-0690')
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID ) 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
, DECODE(STATE_ABBREV
, 'IN'
, PUCTIF.CNTY_INFORMATION8||'C '|| FAI.VALUE
, 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
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_STATES PUS
, PAY_US_COUNTY_TAX_INFO_F PUCTIF
, 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 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
, DECODE(FDI.USER_NAME
, 'A_CITY_SD_NAME'
, 'CITY SCHOOL'
, 'A_COUNTY_SD_NAME'
, '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
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 IN ('A_CITY_SD_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
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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) NOT IN ('05'
, '31'
, '33') 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
, 'AEIC' TAX_TYPE
, 'IN' STATE_ABBREV
, 'IN-AEIC' LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, FND_NUMBER.CANONICAL_TO_NUMBER('0') W2_LOCAL_WAGES
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(FAI.VALUE)
, 0) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_STATES PUS
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND FDI.USER_NAME = 'A_STEIC_ADVANCE_PER_JD_GRE_YTD'
AND FDI.USER_ENTITY_ID = FAI.USER_ENTITY_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE
AND PUS.STATE_ABBREV = 'IN'

Columns