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_LOCALITY_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, 'CITY' tax_type, pus.state_abbrev, decode(faic.context,'33-119-3230','YONK' ,fai.value) locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_CITY_LOCAL_WAGES', paa.tax_unit_id,faic.context, 11) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_CITY_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 11) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_us_states pus, 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 = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_CITY_NAME' 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 faic.context not in ('33-005-2010', '33-047-2010', '33-061-2010' , '33-081-2010', '33-085-2010') AND faic.context not in ('26-047-0690','26-037-0690','26-095-0690', '26-165-0690') AND substr(faic.context,1,2) = pus.state_code AND ( pus.state_code<>39 OR (pus.state_code=39 AND ( substr(faic.context,8,4) = '3000' OR to_number(to_char (ppa.effective_date,'YYYY')) < 2012))) 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, 'CITY' tax_type, 'NY', 'NYC' locality_name, '33-000-2010' jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_LOCAL_WAGES', paa.tax_unit_id, '33-000-2010' , 11) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id, '33-000-2010' , 11) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND exists ( select 'Y' from ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi where fdi.user_name = 'A_CITY_NAME' and fdi.user_entity_id = fai.user_entity_id and fai.archive_item_id = faic.archive_item_id and faic.context in ('33-005-2010','33-047-2010', '33-061-2010', '33-081-2010', '33-085-2010') and fai.context1 = paa.assignment_action_id ) 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, 'CITY' tax_type, 'MO', 'Kansas City' locality_name, '26-000-0690' jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_LOCAL_WAGES', paa.tax_unit_id, '26-000-0690' , 11) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id, '26-000-0690' , 11) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND exists ( select 'Y' from ff_archive_item_contexts faic, ff_archive_items fai, ff_database_items fdi where fdi.user_name = 'A_CITY_NAME' and fdi.user_entity_id = fai.user_entity_id and fai.archive_item_id = faic.archive_item_id and faic.context in ('26-047-0690','26-037-0690','26-095-0690','26-165-0690') and fai.context1 = paa.assignment_action_id ) UNION ALL SELECT /*+index(puctif pay_us_county_tax_info_f_n1)*/ DISTINCT /* Bug 12578602 - This will eliminate duplicates when COUNTY_TAX has Y more than once in a year. */ 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, 'COUNTY' tax_type, state_abbrev, decode(state_abbrev,'IN', puctif.cnty_information8||'C '|| fai.value, fai.value) locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_COUNTY_LOCAL_WAGES' , paa.tax_unit_id, faic.context, 6) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 6) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_us_states pus, pay_us_county_tax_info_f puctif, 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 = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name = 'A_COUNTY_NAME' 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 puctif.jurisdiction_code = faic.context /* AND ppa.effective_date between puctif.effective_start_date and puctif.effective_end_date */ /* Bug 12578602 - Below condition becomes true if COUNTY_TAX has Y atleast once in an year. */ AND TO_NUMBER(TO_CHAR(ppa.effective_date,'YYYY')) BETWEEN TO_NUMBER(TO_CHAR(puctif.effective_start_date,'YYYY')) AND TO_NUMBER(TO_CHAR(puctif.effective_end_date,'YYYY')) AND puctif.county_tax = 'Y' AND substr(faic.context,1,2) = pus.state_code AND ( pus.state_code<>39 OR (pus.state_code=39 AND ( substr(faic.context,8,4) = '3000' OR to_number(to_char (ppa.effective_date,'YYYY')) < 2012))) 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, decode(fdi.user_name, 'A_CITY_SD_NAME', 'CITY SCHOOL', 'A_COUNTY_SD_NAME', 'COUNTY SCHOOL') tax_type, pus.state_abbrev, fai.value locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SCHOOL_LOCAL_WAGES', paa.tax_unit_id,faic.context, 8) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD', paa.tax_unit_id,faic.context, 8) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_us_states pus, 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 = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fdi.user_name IN ('A_CITY_SD_NAME', 'A_COUNTY_SD_NAME') 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) = pus.state_code AND ( pus.state_code<>39 OR (pus.state_code=39 AND ( substr(faic.context,8,4) = '3000' OR to_number(to_char (ppa.effective_date,'YYYY')) < 2012))) 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, 'SDI' tax_type, fai.value state_abbrev, fai.value|| ' SDI' locality_name, faic.context jurisdiction_code, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SDI_EE_TAXABLE_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 2) w2_local_wages, hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD' , paa.tax_unit_id, faic.context, 2) w2_local_income_tax, paa.serial_number, ppa.business_group_id 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 = 'YREND' 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) NOT IN ('05', '31', '33') UNION ALL SELECT /*+ ORDERED */ 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, 'AEIC' tax_type, 'IN' state_abbrev, 'IN-AEIC' locality_name, faic.context jurisdiction_code, fnd_number.canonical_to_number('0') w2_local_wages, nvl(fnd_number.canonical_to_number(fai.value),0) w2_local_income_tax, paa.serial_number, ppa.business_group_id FROM pay_assignment_actions paa ,pay_payroll_actions ppa ,ff_database_items fdi ,ff_archive_items fai ,ff_archive_item_contexts faic ,pay_us_states pus WHERE ppa.report_type = 'YREND' AND ppa.payroll_action_id = paa.payroll_action_id AND fai.context1 = paa.assignment_action_id AND fdi.user_name = 'A_STEIC_ADVANCE_PER_JD_GRE_YTD' AND fdi.user_entity_id = fai.user_entity_id AND fai.archive_item_id = faic.archive_item_id AND substr(faic.context,1,2) = pus.state_code AND pus.state_abbrev = 'IN' UNION ALL /* PSD changes: Case I for PA act locations excluding Phily locations */ SELECT DISTINCT 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 ,'PSD' tax_type ,'PA' state_abbrev ,decode (hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA') ,NULL ,lpad((decode(substr(faic.context,11,6) ,'880000' ,substr(faic.context,4,2) ,'990000' ,substr(faic.context,4,2) ,substr(faic.context,11,2))),2,0) ,lpad(to_number(hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')),2,0)) locality_name ,faic.context jurisdiction_code ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) w2_local_wages ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_PSD_WITHHELD_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) w2_local_income_tax ,paa.serial_number ,ppa.business_group_id FROM pay_us_asg_reporting pua ,ff_archive_item_contexts faic ,ff_archive_items fai ,pay_assignment_actions paa ,pay_payroll_actions ppa ,per_all_assignments_f paf WHERE ppa.report_type = 'YREND' AND paf.effective_start_date <= ppa.effective_date AND paf.effective_end_date >= ppa.start_date AND to_number(paa.serial_number) = paf.person_id AND ppa.payroll_action_id = paa.payroll_action_id AND fai.archive_item_id = faic.archive_item_id AND fai.context1 = paa.assignment_action_id AND pua.assignment_id = paf.assignment_id AND faic.context = pua.JURISDICTION_CODE AND length(pua.JURISDICTION_CODE) = 16 AND substr(pua.JURISDICTION_CODE,4,6) <> '510101' AND substr(pua.JURISDICTION_CODE,11,6) <> '510101' AND substr(pua.JURISDICTION_CODE,4,6) <> '880000' AND substr(pua.JURISDICTION_CODE,11,6) <> '880000' AND to_number(to_char (ppa.effective_date,'YYYY')) > 2011 UNION ALL /* PSD changes: Case II for Out of State Resident locations */ SELECT DISTINCT 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 ,'PSD' tax_type ,'PA' state_abbrev ,decode (hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA') ,NULL ,lpad((decode(substr(faic.context,11,6) ,'880000' ,substr(faic.context,4,2) ,'990000' ,substr(faic.context,4,2) ,substr(faic.context,11,2))),2,0) ,lpad(to_number(hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')),2,0)) locality_name ,faic.context jurisdiction_code ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_CITY_WK_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) w2_local_wages ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_CITY_WK_PSD_WITHHELD_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) w2_local_income_tax ,paa.serial_number ,ppa.business_group_id FROM pay_us_asg_reporting pua ,ff_archive_item_contexts faic ,ff_archive_items fai ,pay_assignment_actions paa ,pay_payroll_actions ppa ,per_all_assignments_f paf WHERE ppa.report_type = 'YREND' AND paf.effective_start_date <= ppa.effective_date AND paf.effective_end_date >= ppa.start_date AND to_number(paa.serial_number) = paf.person_id AND ppa.payroll_action_id = paa.payroll_action_id AND fai.archive_item_id = faic.archive_item_id AND fai.context1 = paa.assignment_action_id AND pua.assignment_id = paf.assignment_id AND faic.context = pua.JURISDICTION_CODE AND length(pua.JURISDICTION_CODE) = 16 AND substr(pua.JURISDICTION_CODE,11,6) NOT IN ('510101','880000') AND substr(pua.JURISDICTION_CODE,4,6) IN ('880000','510101') AND to_number(to_char (ppa.effective_date,'YYYY')) > 2011 UNION ALL /* PSD changes: Case III for Out of State Work locations, modified for Bug#14707323 */ SELECT DISTINCT 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 ,'PSD' tax_type ,'PA' state_abbrev ,decode (hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA') ,NULL ,lpad(substr(faic.context,4,2),2,0) ,lpad(to_number(hr_us_w2_rep.get_w2_tax_unit_item(paa.tax_unit_id ,ppa.payroll_action_id ,'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')),2,0)) locality_name ,faic.context jurisdiction_code ,hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_CITY_RS_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) w2_local_wages ,(hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_CITY_RS_PSD_WITHHELD_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16) + hr_us_w2_rep.get_w2_arch_bal (paa.assignment_action_id ,'A_SCHOOL_PSD_WITHHELD_PER_JD_GRE_YTD' ,paa.tax_unit_id ,faic.context ,16)) w2_local_income_tax ,paa.serial_number ,ppa.business_group_id FROM pay_us_asg_reporting pua ,ff_archive_item_contexts faic ,ff_archive_items fai ,pay_assignment_actions paa ,pay_payroll_actions ppa ,per_all_assignments_f paf WHERE ppa.report_type = 'YREND' AND paf.effective_start_date <= ppa.effective_date AND paf.effective_end_date >= ppa.start_date AND to_number(paa.serial_number) = paf.person_id AND ppa.payroll_action_id = paa.payroll_action_id AND fai.archive_item_id = faic.archive_item_id AND fai.context1 = paa.assignment_action_id AND pua.assignment_id = paf.assignment_id AND faic.context = pua.JURISDICTION_CODE AND length(pua.JURISDICTION_CODE) = 16 AND substr(pua.JURISDICTION_CODE,4,6) NOT IN ('510101','880000') AND substr(pua.JURISDICTION_CODE,11,6) IN ('880000','510101') AND to_number(to_char (ppa.effective_date,'YYYY')) > 2011

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
, 'CITY' TAX_TYPE
, PUS.STATE_ABBREV
, DECODE(FAIC.CONTEXT
, '33-119-3230'
, 'YONK'
, FAI.VALUE) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 11) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 11) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_STATES PUS
, 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 = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_CITY_NAME'
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 FAIC.CONTEXT NOT IN ('33-005-2010'
, '33-047-2010'
, '33-061-2010'
, '33-081-2010'
, '33-085-2010')
AND FAIC.CONTEXT NOT IN ('26-047-0690'
, '26-037-0690'
, '26-095-0690'
, '26-165-0690')
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE
AND ( PUS.STATE_CODE<>39 OR (PUS.STATE_CODE=39
AND ( SUBSTR(FAIC.CONTEXT
, 8
, 4) = '3000' OR TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) < 2012))) 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
, 'CITY' TAX_TYPE
, 'NY'
, 'NYC' LOCALITY_NAME
, '33-000-2010' JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, '33-000-2010'
, 11) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '33-000-2010'
, 11) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND EXISTS ( SELECT 'Y'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE FDI.USER_NAME = 'A_CITY_NAME'
AND FDI.USER_ENTITY_ID = FAI.USER_ENTITY_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAIC.CONTEXT IN ('33-005-2010'
, '33-047-2010'
, '33-061-2010'
, '33-081-2010'
, '33-085-2010')
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID ) 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
, 'CITY' TAX_TYPE
, 'MO'
, 'KANSAS CITY' LOCALITY_NAME
, '26-000-0690' JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, '26-000-0690'
, 11) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SPL_CITY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, '26-000-0690'
, 11) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND EXISTS ( SELECT 'Y'
FROM FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, FF_DATABASE_ITEMS FDI
WHERE FDI.USER_NAME = 'A_CITY_NAME'
AND FDI.USER_ENTITY_ID = FAI.USER_ENTITY_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAIC.CONTEXT IN ('26-047-0690'
, '26-037-0690'
, '26-095-0690'
, '26-165-0690')
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID ) UNION ALL SELECT /*+INDEX(PUCTIF PAY_US_COUNTY_TAX_INFO_F_N1)*/ DISTINCT /* BUG 12578602 - THIS WILL ELIMINATE DUPLICATES WHEN COUNTY_TAX HAS Y MORE THAN ONCE IN A YEAR. */ 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
, 'COUNTY' TAX_TYPE
, STATE_ABBREV
, DECODE(STATE_ABBREV
, 'IN'
, PUCTIF.CNTY_INFORMATION8||'C '|| FAI.VALUE
, FAI.VALUE) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_COUNTY_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 6) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 6) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_STATES PUS
, PAY_US_COUNTY_TAX_INFO_F PUCTIF
, 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 = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME = 'A_COUNTY_NAME'
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 PUCTIF.JURISDICTION_CODE = FAIC.CONTEXT /*
AND PPA.EFFECTIVE_DATE BETWEEN PUCTIF.EFFECTIVE_START_DATE
AND PUCTIF.EFFECTIVE_END_DATE */ /* BUG 12578602 - BELOW CONDITION BECOMES TRUE IF COUNTY_TAX HAS Y ATLEAST ONCE IN AN YEAR. */
AND TO_NUMBER(TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) BETWEEN TO_NUMBER(TO_CHAR(PUCTIF.EFFECTIVE_START_DATE
, 'YYYY'))
AND TO_NUMBER(TO_CHAR(PUCTIF.EFFECTIVE_END_DATE
, 'YYYY'))
AND PUCTIF.COUNTY_TAX = 'Y'
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE
AND ( PUS.STATE_CODE<>39 OR (PUS.STATE_CODE=39
AND ( SUBSTR(FAIC.CONTEXT
, 8
, 4) = '3000' OR TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) < 2012))) 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
, DECODE(FDI.USER_NAME
, 'A_CITY_SD_NAME'
, 'CITY SCHOOL'
, 'A_COUNTY_SD_NAME'
, 'COUNTY SCHOOL') TAX_TYPE
, PUS.STATE_ABBREV
, FAI.VALUE LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_LOCAL_WAGES'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 8) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_STATES PUS
, 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 = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FDI.USER_NAME IN ('A_CITY_SD_NAME'
, 'A_COUNTY_SD_NAME')
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) = PUS.STATE_CODE
AND ( PUS.STATE_CODE<>39 OR (PUS.STATE_CODE=39
AND ( SUBSTR(FAIC.CONTEXT
, 8
, 4) = '3000' OR TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) < 2012))) 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
, 'SDI' TAX_TYPE
, FAI.VALUE STATE_ABBREV
, FAI.VALUE|| ' SDI' LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_TAXABLE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL(PAA.ASSIGNMENT_ACTION_ID
, 'A_SDI_EE_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 2) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
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 = 'YREND'
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) NOT IN ('05'
, '31'
, '33') UNION ALL SELECT /*+ ORDERED */ 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
, 'AEIC' TAX_TYPE
, 'IN' STATE_ABBREV
, 'IN-AEIC' LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, FND_NUMBER.CANONICAL_TO_NUMBER('0') W2_LOCAL_WAGES
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(FAI.VALUE)
, 0) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, FF_DATABASE_ITEMS FDI
, FF_ARCHIVE_ITEMS FAI
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, PAY_US_STATES PUS
WHERE PPA.REPORT_TYPE = 'YREND'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND FDI.USER_NAME = 'A_STEIC_ADVANCE_PER_JD_GRE_YTD'
AND FDI.USER_ENTITY_ID = FAI.USER_ENTITY_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND SUBSTR(FAIC.CONTEXT
, 1
, 2) = PUS.STATE_CODE
AND PUS.STATE_ABBREV = 'IN' UNION ALL /* PSD CHANGES: CASE I FOR PA ACT LOCATIONS EXCLUDING PHILY LOCATIONS */ SELECT DISTINCT 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
, 'PSD' TAX_TYPE
, 'PA' STATE_ABBREV
, DECODE (HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')
, NULL
, LPAD((DECODE(SUBSTR(FAIC.CONTEXT
, 11
, 6)
, '880000'
, SUBSTR(FAIC.CONTEXT
, 4
, 2)
, '990000'
, SUBSTR(FAIC.CONTEXT
, 4
, 2)
, SUBSTR(FAIC.CONTEXT
, 11
, 2)))
, 2
, 0)
, LPAD(TO_NUMBER(HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA'))
, 2
, 0)) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_PSD_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_ASG_REPORTING PUA
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PER_ALL_ASSIGNMENTS_F PAF
WHERE PPA.REPORT_TYPE = 'YREND'
AND PAF.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE
AND PAF.EFFECTIVE_END_DATE >= PPA.START_DATE
AND TO_NUMBER(PAA.SERIAL_NUMBER) = PAF.PERSON_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND PUA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND FAIC.CONTEXT = PUA.JURISDICTION_CODE
AND LENGTH(PUA.JURISDICTION_CODE) = 16
AND SUBSTR(PUA.JURISDICTION_CODE
, 4
, 6) <> '510101'
AND SUBSTR(PUA.JURISDICTION_CODE
, 11
, 6) <> '510101'
AND SUBSTR(PUA.JURISDICTION_CODE
, 4
, 6) <> '880000'
AND SUBSTR(PUA.JURISDICTION_CODE
, 11
, 6) <> '880000'
AND TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) > 2011 UNION ALL /* PSD CHANGES: CASE II FOR OUT OF STATE RESIDENT LOCATIONS */ SELECT DISTINCT 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
, 'PSD' TAX_TYPE
, 'PA' STATE_ABBREV
, DECODE (HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')
, NULL
, LPAD((DECODE(SUBSTR(FAIC.CONTEXT
, 11
, 6)
, '880000'
, SUBSTR(FAIC.CONTEXT
, 4
, 2)
, '990000'
, SUBSTR(FAIC.CONTEXT
, 4
, 2)
, SUBSTR(FAIC.CONTEXT
, 11
, 2)))
, 2
, 0)
, LPAD(TO_NUMBER(HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA'))
, 2
, 0)) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_WK_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) W2_LOCAL_WAGES
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_WK_PSD_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_ASG_REPORTING PUA
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PER_ALL_ASSIGNMENTS_F PAF
WHERE PPA.REPORT_TYPE = 'YREND'
AND PAF.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE
AND PAF.EFFECTIVE_END_DATE >= PPA.START_DATE
AND TO_NUMBER(PAA.SERIAL_NUMBER) = PAF.PERSON_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND PUA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND FAIC.CONTEXT = PUA.JURISDICTION_CODE
AND LENGTH(PUA.JURISDICTION_CODE) = 16
AND SUBSTR(PUA.JURISDICTION_CODE
, 11
, 6) NOT IN ('510101'
, '880000')
AND SUBSTR(PUA.JURISDICTION_CODE
, 4
, 6) IN ('880000'
, '510101')
AND TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) > 2011 UNION ALL /* PSD CHANGES: CASE III FOR OUT OF STATE WORK LOCATIONS
, MODIFIED FOR BUG#14707323 */ SELECT DISTINCT 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
, 'PSD' TAX_TYPE
, 'PA' STATE_ABBREV
, DECODE (HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA')
, NULL
, LPAD(SUBSTR(FAIC.CONTEXT
, 4
, 2)
, 2
, 0)
, LPAD(TO_NUMBER(HR_US_W2_REP.GET_W2_TAX_UNIT_ITEM(PAA.TAX_UNIT_ID
, PPA.PAYROLL_ACTION_ID
, 'A_LC_W2_REPORTING_RULES_1_ORG_COMBINED_FILER_TCD_CODE_PA'))
, 2
, 0)) LOCALITY_NAME
, FAIC.CONTEXT JURISDICTION_CODE
, HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_RS_PSD_SUBJ_WHABLE_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) W2_LOCAL_WAGES
, (HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_CITY_RS_PSD_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16) + HR_US_W2_REP.GET_W2_ARCH_BAL (PAA.ASSIGNMENT_ACTION_ID
, 'A_SCHOOL_PSD_WITHHELD_PER_JD_GRE_YTD'
, PAA.TAX_UNIT_ID
, FAIC.CONTEXT
, 16)) W2_LOCAL_INCOME_TAX
, PAA.SERIAL_NUMBER
, PPA.BUSINESS_GROUP_ID
FROM PAY_US_ASG_REPORTING PUA
, FF_ARCHIVE_ITEM_CONTEXTS FAIC
, FF_ARCHIVE_ITEMS FAI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PER_ALL_ASSIGNMENTS_F PAF
WHERE PPA.REPORT_TYPE = 'YREND'
AND PAF.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE
AND PAF.EFFECTIVE_END_DATE >= PPA.START_DATE
AND TO_NUMBER(PAA.SERIAL_NUMBER) = PAF.PERSON_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND FAI.ARCHIVE_ITEM_ID = FAIC.ARCHIVE_ITEM_ID
AND FAI.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND PUA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND FAIC.CONTEXT = PUA.JURISDICTION_CODE
AND LENGTH(PUA.JURISDICTION_CODE) = 16
AND SUBSTR(PUA.JURISDICTION_CODE
, 4
, 6) NOT IN ('510101'
, '880000')
AND SUBSTR(PUA.JURISDICTION_CODE
, 11
, 6) IN ('880000'
, '510101')
AND TO_NUMBER(TO_CHAR (PPA.EFFECTIVE_DATE
, 'YYYY')) > 2011