View: PAY_US_W2_WAGES_V

Product:	PAY - Payroll
Description:	Used in W2 Report.
Implementation/DBA Data:	APPS.PAY_US_W2_WAGES_V

View Text

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
, PAF.PERSON_ID PERSON_ID
, 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
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_PREFIX' ) PRE_NAME_ADJUNCT
, NVL(HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_PER_NATIONAL_IDENTIFIER')
, 'APPLIED FOR') SSN
, DECODE(LEAVING_REASON
, NULL
, 'ZZ'
, LEAVING_REASON) TERMINATION_REASON
, PAF.LOCATION_ID SORT_LOCATION_ID
, PAF.ORGANIZATION_ID SORT_ORGANIZATION_ID
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_WAGES'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX1
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_FIT_WITHHELD_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX2
, (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_BOX3
, 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_BOX4
, 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_BOX5
, 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_BOX6
, 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_BOX7
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_BOX_8_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX8
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_EIC_ADVANCE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX9
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_DEPENDENT_CARE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX10
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_BOX_11_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX11
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_FRINGE_BENEFITS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX12
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_ASG_STATUTORY_EMPLOYEE'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX15A
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_DECEASED'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX15B
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_PENSION_PLAN_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX15C
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_LEGAL_REP'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX15D
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_DEF_COMP_401K_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BOX15F
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_GROSS_1099R'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) GROSS_1099R
FROM PER_PERIODS_OF_SERVICE PPS
, PER_ASSIGNMENTS_F PAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF.EFFECTIVE_START_DATE = (SELECT MAX(PAF2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F PAF2
WHERE PAF2.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF2.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE)
AND PAF.EFFECTIVE_END_DATE >= PPA.START_DATE
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PAF.PERSON_ID = PPS.PERSON_ID

Columns