View: PAY_US_W2_BOX14_V

Product:	PAY - Payroll
Description:	Used in W2 Report.
Implementation/DBA Data:	APPS.PAY_US_W2_BOX14_V

View Text

SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1) CODE
, PBT.BALANCE_NAME
, FCL.MEANING
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_W2_BOX_14'||SUBSTR(PBT.BALANCE_NAME
, 10
, 1)||'_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0)
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, FND_COMMON_LOOKUPS FCL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBT.BALANCE_NAME LIKE 'W2 BOX 14%'
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)
AND FCL.LOOKUP_TYPE IN ('W2 BOX 14'
, 'W2 BOX 14 USER-DEFINED')
AND FCL.APPLICATION_ID = 800
AND FCL.LOOKUP_CODE = SUBSTR(PBT.BALANCE_NAME
, 10
, 1)
AND PPA.EFFECTIVE_DATE BETWEEN NVL(FCL.START_DATE_ACTIVE
, PPA.START_DATE)
AND NVL(FCL.END_DATE_ACTIVE
, PPA.EFFECTIVE_DATE) UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1) CODE
, PBT.BALANCE_NAME
, 'OFLT'
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_WORKERS_COMP_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '27-000-0000'
, 2)
FROM PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBT.BALANCE_NAME ='WORKERS COMP WITHHELD'
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_JD_GRE_YTD'
AND EXISTS (SELECT F.BALANCE_FEED_ID
FROM PAY_BALANCE_FEEDS_F F
WHERE F.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID)
AND EXISTS ( SELECT 'X'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE 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 SUBSTR(FAIC.CONTEXT
, 1
, 2) = '27') UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1) CODE
, PBT.BALANCE_NAME
, 'UI/HC/WF'
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SUI_EE_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '31-000-0000'
, 2)
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBT.BALANCE_NAME ='SUI EE WITHHELD'
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_JD_GRE_YTD'
AND EXISTS (SELECT F.BALANCE_FEED_ID
FROM PAY_BALANCE_FEEDS_F F
WHERE F.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID)
AND EXISTS ( SELECT 'X'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE 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 SUBSTR(FAIC.CONTEXT
, 1
, 2) = '31') UNION ALL SELECT TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1) CODE
, PBT.BALANCE_NAME
, 'NJDI'
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '31-000-0000'
, 2)
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBT.BALANCE_NAME ='SDI EE WITHHELD'
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_JD_GRE_YTD'
AND EXISTS (SELECT F.BALANCE_FEED_ID
FROM PAY_BALANCE_FEEDS_F F
WHERE F.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID)
AND EXISTS ( SELECT 'X'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE 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 SUBSTR(FAIC.CONTEXT
, 1
, 2) = '31')

Columns