eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_US_TERRITORY_W2_V

Source

View Text - Preformatted


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, hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id,'A_SS_ER_W11_TAXABLE_PER_GRE_YTD', paa.tax_unit_id,'00-000-0000',0) hire_act_wages, hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id, 'A_PR_W2_CHARITABLE_CONTRIBUTIONS_PER_GRE_YTD', paa.tax_unit_id, '00-000-0000',0) pr_charitable_contributions, hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id,'A_PR_W2_CONTR_TO_SAVE_AND_DOUBLE_MONEY_PER_GRE_YTD', paa.tax_unit_id, '00-000-0000',0) pr_double_money, hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id,'A_W2_HEALTH_COVERAGE_PER_GRE_YTD', paa.tax_unit_id, '00-000-0000',0) w2_health_coverage 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'

View Text - HTML Formatted

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
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_SS_ER_W11_TAXABLE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) HIRE_ACT_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_PR_W2_CHARITABLE_CONTRIBUTIONS_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) PR_CHARITABLE_CONTRIBUTIONS
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_PR_W2_CONTR_TO_SAVE_AND_DOUBLE_MONEY_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) PR_DOUBLE_MONEY
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_HEALTH_COVERAGE_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_HEALTH_COVERAGE
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'