VIEW: APPS.PAY_US_W2_BOX14_V

Source

View Text - Preformatted

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) AND length(fcl.lookup_code) = 1 /* Bug 7456296 also below union */ 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 ,11) code ,pbt.balance_name ,fcl.meaning ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,fcl.ATTRIBUTE3 ,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 ='W2 BOX 14 USER-DEFINED' AND fcl.application_id = 800 AND fcl.ATTRIBUTE2 = pbt.balance_name AND ppa.effective_date BETWEEN nvl (fcl.start_date_active ,ppa.start_date) AND nvl (fcl.end_date_active ,ppa.effective_date) AND length(fcl.lookup_code) <> 1 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/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) 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')

View Text - HTML Formatted

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)
AND LENGTH(FCL.LOOKUP_CODE) = 1 /* BUG 7456296 ALSO BELOW UNION */ 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
, 11) CODE
, PBT.BALANCE_NAME
, FCL.MEANING
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, FCL.ATTRIBUTE3
, 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 ='W2 BOX 14 USER-DEFINED'
AND FCL.APPLICATION_ID = 800
AND FCL.ATTRIBUTE2 = PBT.BALANCE_NAME
AND PPA.EFFECTIVE_DATE BETWEEN NVL (FCL.START_DATE_ACTIVE
, PPA.START_DATE)
AND NVL (FCL.END_DATE_ACTIVE
, PPA.EFFECTIVE_DATE)
AND LENGTH(FCL.LOOKUP_CODE) <> 1 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/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)
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')