View: PAY_US_WAGES_W2C_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_US_WAGES_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
, 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)
, 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)
, 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)
, 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)
, 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)
, 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)
, 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)
, 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)
, 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)
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_DEPENDENT_CARE_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_NONQUAL_PLAN_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)
, 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
, PPA.EFFECTIVE_DATE)
, 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
, PPA.EFFECTIVE_DATE)
, HR_US_W2_REP.GET_W2_BOX_15(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_TP_SICK_PAY_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0
, PPA.EFFECTIVE_DATE)
, 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
, PPA.REPORT_TYPE /*
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_CAPITAL_GAIN_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) CAPITAL_GAIN_1099R
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_EE_CONTRIBUTIONS_OR_PREMIUMS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) EE_CONTRIBUTION_PREMIUMS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_OTHER_EE_ANNUITY_CONTRACT_AMOUNT_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) EE_ANNUITY_CONTRACT_AMT
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_TOTAL_EE_CONTRIBUTIONS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) TOTAL_EE_CONTRIBUTIONS
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_UNREALIZED_NET_ER_SECURITY_APPRECIATION_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) UNREALIZED_NET_ER_SECURITY */
FROM PER_PERIODS_OF_SERVICE PPS
, PER_ALL_ASSIGNMENTS_F PAF
, 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 PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF.EFFECTIVE_START_DATE = (SELECT MAX(PAF2.EFFECTIVE_START_DATE)
FROM PER_ALL_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