View: PAY_US_WAGES_1099R_V

Product:	PAY - Payroll
Description:	View for 1099R Paper report
Implementation/DBA Data:	APPS.PAY_US_WAGES_1099R_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
, NVL(HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER')
, 'APPLIED FOR')
, HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_TAX_UNIT_NAME')
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_TAXABLE_AMOUNT_UNKNOWN')
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_TOTAL_DISTRIBUTIONS')
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_EMPLOYEE_DISTRIBUTION_PERCENT')
, HR_US_W2_REP.GET_PER_ITEM(PAA.ASSIGNMENT_ACTION_ID
, 'A_TOTAL_DISTRIBUTION_PERCENT')
, 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_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_W2_GROSS_1099R'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)
, 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)
, 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)
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_UNREALIZED_NET_ER_SEC_APPREC_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_OTHER_EE_ANNUITY_CONTRACT_AMT_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)
, 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)
, HR_US_W2_REP.GET_LAST_DEFFER_YEAR(PAA.ASSIGNMENT_ACTION_ID)
FROM PER_PERIODS_OF_SERVICE PPS
, PER_ALL_ASSIGNMENTS_F PAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.REPORT_QUALIFIER = 'FED'
AND PPA.REPORT_CATEGORY = 'RT'
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
AND EXISTS ( SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI
WHERE HOI.ORGANIZATION_ID = PAA.TAX_UNIT_ID
AND HOI.ORG_INFORMATION_CONTEXT = '1099R MAGNETIC REPORT RULES')

Columns