View: PAY_US_TERRITORY_W2_V

Product:	PAY - Payroll
Description:	This view is used to fetch the values for PuertoRico Employee W-2
Implementation/DBA Data:	APPS.PAY_US_TERRITORY_W2_V

View Text

SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PAA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, PAA.ACTION_STATUS ACTION_STATUS
, PAA.SERIAL_NUMBER PERSON_ID
, FAI.VALUE TERRITORY_ABBREV
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_LAST_NAME' ) LAST_NAME
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_FIRST_NAME' ) FIRST_NAME
, DECODE( HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_MIDDLE_NAMES' )
, NULL
, NULL
, SUBSTR(HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_MIDDLE_NAMES')
, 1
, 1)) MIDDLE_NAME
, NVL(HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_NATIONAL_IDENTIFIER')
, 'APPLIED FOR') SSN
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_MARITAL_STATUS' ) MARITAL_STATUS
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_CON_NATIONAL_IDENTIFIER' ) SPOUSE_SSN
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_STATE_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) - (HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) + HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) + HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) ) TERRITORY_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SIT_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) TERRITORY_TAX_WH
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) COMMISSIONS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_REIMB_EXPENSES_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) REIMBURSED_EXPENSES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) RETIREMENT_CONTRIBUTION
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) ALLOWANCES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) TIPS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TERRITORY_PENSION_ANNUITY_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) COST_OF_PENSION
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_401K_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)+ HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_403B_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) + HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_457_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) CODA_PLAN
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SS_EE_TAXABLE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_SOCIAL_SECURITY_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SS_EE_WITHHELD_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_SST_WITHHELD
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_MED_WAGES_TIPS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_MED_TAX_WITHHELD
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_BOX_7_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_SOCIAL_SECURITY_TIPS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) UNCOLL_SS_TAX_ON_TIPS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) UNCOLL_MED_TAX_ON_TIPS
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 FAI.VALUE = 'PR'

Columns