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_OTHER_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, substr(pbt.balance_name,10,1) w2_other_code, pbt.balance_name w2_other_bal_name, fcl.meaning w2_other_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) w2_other_value, ppa.report_type 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 in ('YREND', 'W2C_PRE_PROCESS') 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, ppa.effective_date, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, substr(pbt.balance_name,10,1), pbt.balance_name, 'UI/WF/SWF', 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) , ppa.report_type 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 in ('YREND', 'W2C_PRE_PROCESS') 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, ppa.effective_date, paa.payroll_action_id, paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, paa.action_status, substr(pbt.balance_name,10,1), 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), ppa.report_type 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 in ('YREND', 'W2C_PRE_PROCESS') 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') UNION ALL 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(pbt.balance_name,10,1), pbt.balance_name, 'NY SDI', hr_us_w2_rep.get_w2_arch_bal( paa.assignment_action_id, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id, '33-000-0000',2) , ppa.report_type 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 in ('YREND', 'W2C_PRE_PROCESS') 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) = '33') UNION ALL 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, 'X', 'STEIC Advance', 'WEIC', hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id ,'A_STEIC_ADVANCE_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,2), ppa.report_type 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 IN ( 'YREND' , 'W2C_PRE_PROCESS' ) 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 substr(faic.context ,1 ,2) IN ( '50' )

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
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1) W2_OTHER_CODE
, PBT.BALANCE_NAME W2_OTHER_BAL_NAME
, FCL.MEANING W2_OTHER_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) W2_OTHER_VALUE
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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
, PPA.EFFECTIVE_DATE
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1)
, PBT.BALANCE_NAME
, 'UI/WF/SWF'
, 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)
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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
, PPA.EFFECTIVE_DATE
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_STATUS
, SUBSTR(PBT.BALANCE_NAME
, 10
, 1)
, 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)
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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') UNION ALL 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(PBT.BALANCE_NAME
, 10
, 1)
, PBT.BALANCE_NAME
, 'NY SDI'
, HR_US_W2_REP.GET_W2_ARCH_BAL( PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '33-000-0000'
, 2)
, PPA.REPORT_TYPE
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 IN ('YREND'
, 'W2C_PRE_PROCESS')
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) = '33') UNION ALL 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
, 'X'
, 'STEIC ADVANCE'
, 'WEIC'
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_STEIC_ADVANCE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2)
, PPA.REPORT_TYPE
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 IN ( 'YREND'
, 'W2C_PRE_PROCESS' )
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 SUBSTR(FAIC.CONTEXT
, 1
, 2) IN ( '50' )