VIEW: APPS.PAY_US_WAGES_W2C_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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