View: PAY_US_CODE_W2C_V

Product:	PAY - Payroll
Description:	Used in W2C Report. Retrieves Box 12 information.
Implementation/DBA Data:	APPS.PAY_US_CODE_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
, SUBSTR(HL.LOOKUP_CODE
, INSTR(HL.LOOKUP_CODE
, '_'
, -1
, 1)+1) W2_BALANCE_CODE
, HL.MEANING W2_BALANCE_NAME
, HR_US_W2_REP.GET_W2_ARCH_BAL( PAA.ASSIGNMENT_ACTION_ID
, DECODE(SUBSTR(HL.LOOKUP_CODE
, 1
, 11)
, 'A_W2_USERRA'
, HL.LOOKUP_CODE
, SUBSTR(HL.LOOKUP_CODE
, 1
, LENGTH(LOOKUP_CODE) - 2)) ||'_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BALANCE_CODE_VALUE
, PPA.REPORT_TYPE
FROM PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_DIMENSIONS PBD
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, HR_LOOKUPS HL
WHERE PPA.REPORT_TYPE IN ('YREND'
, 'W2C_PRE_PROCESS')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND HL.LOOKUP_TYPE = 'W2 BOX 12'
AND HL.ENABLED_FLAG = 'Y'
AND PBT.BALANCE_NAME = HL.MEANING
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_GRE_YTD'
AND EXISTS (SELECT F.BALANCE_FEED_ID
FROM PAY_BALANCE_FEEDS_F F
WHERE F.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID)

Columns