VIEW: APPS.PAY_US_CODE_W2_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(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,instr(hl.lookup_code,'_',-1)-1)) ||'_PER_GRE_YTD', paa.tax_unit_id,'00-000-0000', 0) w2_balance_code_value, paa.serial_number, ppa.business_group_id 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 = 'YREND' 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 (pbt.business_group_id = ppa.business_group_id or pbt.legislation_code = 'US') 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)

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(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
, INSTR(HL.LOOKUP_CODE
, '_'
, -1)-1)) ||'_PER_GRE_YTD'
, PAA.TAX_UNIT_ID
, '00-000-0000'
, 0) W2_BALANCE_CODE_VALUE
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 = 'YREND'
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 (PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID OR PBT.LEGISLATION_CODE = 'US')
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)