VIEW: APPS.PAY_US_LOCAL_EE_WAGES_V

Source

View Text - Preformatted

SELECT PAA.ASSIGNMENT_ID , PAA.TAX_UNIT_ID , PAA.ASSIGNMENT_ACTION_ID , PAA.ACTION_STATUS , PAA.PAYROLL_ACTION_ID , PPA.ACTION_TYPE , PPA.EFFECTIVE_DATE , CITY.JURISDICTION_CODE , PETV.TAX_TYPE_ID , PETV.EE_OR_ER_CODE , PETV.LIMIT_TAX_FLAG , PETV.TAX_DOMAIN_CODE , PETV.TAX_TYPE_CODE , PETV.BALANCE_CATEGORY_CODE , PETV.USER_REPORTING_NAME , PETV.ELEMENT_NAME , PETV.ELEMENT_TYPE_ID , PETV.EFFECTIVE_START_DATE , PETV.EFFECTIVE_END_DATE , PETV.BUSINESS_GROUP_ID , PETV.LEGISLATION_CODE , PETV.PROCESSING_PRIORITY , 'INVALID' SCHOOL_DISTRICT_NAME , 'INVALID' SCHOOL_DISTRICT_CODE , NAMES.CITY_NAME , NAMES.CITY_CODE , 'INVALID' STATE_NAME , 'INVALID' STATE_ABBREV , NAMES.STATE_CODE , 'INVALID' COUNTY_NAME , 'INVALID' COUNTY_ABBREV , NAMES.COUNTY_CODE , PETV.TAX_TYPE_CODE || '-' || DECODE(PETV.TAX_TYPE_CODE, 'CITY', NAMES.CITY_NAME, 'HT', NAMES.CITY_NAME, CITY.JURISDICTION_CODE) LOCAL_TAX_NAME , PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE , PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'CURRENT', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) CURRENT_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'RUN', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) RUN_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PTD', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PTD_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PYDATE', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PYDATE_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'MONTH', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) MONTH_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'QTD', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) QTD_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'YTD', PAA.TAX_UNIT_ID, CITY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) YTD_VAL FROM PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PAY_US_LOCAL_EE_WAGE_TYPES_V PETV , PAY_US_CITY_NAMES NAMES , PAY_US_EMP_CITY_TAX_RULES_F CITY , PAY_US_CITY_TAX_INFO_F CITF WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE BETWEEN CITY.EFFECTIVE_START_DATE AND CITY.EFFECTIVE_END_DATE AND CITY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND NAMES.CITY_CODE = SUBSTR(CITY.JURISDICTION_CODE,8,4) AND NAMES.COUNTY_CODE = SUBSTR(CITY.JURISDICTION_CODE,4,3) AND NAMES.STATE_CODE = SUBSTR(CITY.JURISDICTION_CODE,1,2) AND NAMES.PRIMARY_FLAG = 'Y' AND CITF.JURISDICTION_CODE = CITY.JURISDICTION_CODE AND DECODE(TAX_TYPE_CODE, 'CITY', CITF.CITY_TAX, 'HT', CITF.HEAD_TAX, 'N') = 'Y' AND PPA.EFFECTIVE_DATE BETWEEN CITF.EFFECTIVE_START_DATE AND CITF.EFFECTIVE_END_DATE AND PETV.TAX_TYPE_CODE IN ('CITY', 'HT') AND PPA.ACTION_TYPE IN ('Q', 'R', 'V', 'I', 'B') AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE AND PETV.EFFECTIVE_END_DATE AND EXISTS (SELECT 'x' FROM PAY_ELEMENT_TYPES_F ELE, PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASG, PAY_RUN_RESULTS RR WHERE RR.JURISDICTION_CODE = CITY.JURISDICTION_CODE AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE,'CITY', 'City_WK'), DECODE(PETV.TAX_TYPE_CODE,'CITY', 'City_RS'), DECODE(PETV.TAX_TYPE_CODE,'HT', 'City_HT_WK'), DECODE(PETV.TAX_TYPE_CODE,'HT', 'City_HT_RS')) AND RR.ELEMENT_TYPE_ID + 0 = ELE.ELEMENT_TYPE_ID AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE, 'YEAR') AND PPA.EFFECTIVE_DATE AND PACT.ACTION_TYPE IN ('R', 'Q') ) UNION ALL SELECT PAA.ASSIGNMENT_ID, PAA.TAX_UNIT_ID, PAA.ASSIGNMENT_ACTION_ID, PAA.ACTION_STATUS, PAA.PAYROLL_ACTION_ID, PPA.ACTION_TYPE, PPA.EFFECTIVE_DATE, CNTY.JURISDICTION_CODE, PETV.TAX_TYPE_ID, PETV.EE_OR_ER_CODE, PETV.LIMIT_TAX_FLAG, PETV.TAX_DOMAIN_CODE, PETV.TAX_TYPE_CODE, PETV.BALANCE_CATEGORY_CODE, PETV.USER_REPORTING_NAME, PETV.ELEMENT_NAME, PETV.ELEMENT_TYPE_ID, PETV.EFFECTIVE_START_DATE, PETV.EFFECTIVE_END_DATE, PETV.BUSINESS_GROUP_ID, PETV.LEGISLATION_CODE, PETV.PROCESSING_PRIORITY, 'INVALID' SCHOOL_DISTRICT_NAME, 'INVALID' SCHOOL_DISTRICT_CODE, 'INVALID' CITY_NAME, 'INVALID' CITY_CODE, 'INVALID' STATE_NAME, 'INVALID' STATE_ABBREV, NAMES.STATE_CODE, NAMES.COUNTY_NAME COUNTY_NAME, 'INVALID' COUNTY_ABBREV, NAMES.COUNTY_CODE, PETV.TAX_TYPE_CODE || '-' || DECODE(PETV.TAX_TYPE_CODE, 'COUNTY', NAMES.COUNTY_NAME, CNTY.JURISDICTION_CODE) LOCAL_TAX_NAME, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'CURRENT', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) CURRENT_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'RUN', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) RUN_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PTD', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PYDATE', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PYDATE_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'MONTH', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) MONTH_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'QTD', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) QTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'YTD', PAA.TAX_UNIT_ID, CNTY.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) YTD_VAL FROM PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PAY_US_LOCAL_EE_WAGE_TYPES_V PETV , PAY_US_EMP_COUNTY_TAX_RULES_F CNTY , PAY_US_COUNTY_TAX_INFO_F CTIF , PAY_US_COUNTIES NAMES WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE BETWEEN CNTY.EFFECTIVE_START_DATE AND CNTY.EFFECTIVE_END_DATE AND CNTY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND NAMES.COUNTY_CODE = SUBSTR(CNTY.JURISDICTION_CODE,4,3) AND NAMES.STATE_CODE = SUBSTR(CNTY.JURISDICTION_CODE,1,2) AND PETV.TAX_TYPE_CODE = 'COUNTY' AND CTIF.JURISDICTION_CODE = CNTY.JURISDICTION_CODE AND CTIF.COUNTY_TAX = 'Y' AND PPA.EFFECTIVE_DATE BETWEEN CTIF.EFFECTIVE_START_DATE AND CTIF.EFFECTIVE_END_DATE AND PPA.ACTION_TYPE IN ('Q', 'R', 'V', 'I', 'B') AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE AND PETV.EFFECTIVE_END_DATE AND EXISTS (SELECT 'x' FROM PAY_ELEMENT_TYPES_F ELE, PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASG, PAY_RUN_RESULTS RR WHERE RR.JURISDICTION_CODE = CNTY.JURISDICTION_CODE AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE,'COUNTY', 'County_WK'), DECODE(PETV.TAX_TYPE_CODE,'COUNTY', 'County_RS'), DECODE(PETV.TAX_TYPE_CODE,'HT', 'County_HT_WK'), DECODE(PETV.TAX_TYPE_CODE,'HT', 'County_HT_RS')) AND RR.ELEMENT_TYPE_ID +0 = ELE.ELEMENT_TYPE_ID AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE, 'YEAR') AND PPA.EFFECTIVE_DATE AND PACT.ACTION_TYPE IN ('R', 'Q') ) UNION ALL SELECT PAA.ASSIGNMENT_ID, SCHOOL.TAX_UNIT_ID, PAA.ASSIGNMENT_ACTION_ID, PAA.ACTION_STATUS, PAA.PAYROLL_ACTION_ID, PPA.ACTION_TYPE, PPA.EFFECTIVE_DATE, SCHOOL.JURISDICTION_CODE, PETV.TAX_TYPE_ID, PETV.EE_OR_ER_CODE, PETV.LIMIT_TAX_FLAG, PETV.TAX_DOMAIN_CODE, PETV.TAX_TYPE_CODE, PETV.BALANCE_CATEGORY_CODE, PETV.USER_REPORTING_NAME, PETV.ELEMENT_NAME, PETV.ELEMENT_TYPE_ID, PETV.EFFECTIVE_START_DATE, PETV.EFFECTIVE_END_DATE, PETV.BUSINESS_GROUP_ID, PETV.LEGISLATION_CODE, PETV.PROCESSING_PRIORITY, 'INVALID' SCHOOL_DISTRICT_NAME, 'INVALID' SCHOOL_DISTRICT_CODE, 'INVALID' CITY_NAME, 'INVALID' CITY_CODE, 'INVALID' STATE_NAME, 'INVALID' STATE_ABBREV, 'INVALID' STATE_CODE, 'INVALID' COUNTY_NAME, 'INVALID' COUNTY_ABBREV, 'INVALID' COUNTY_CODE, PETV.TAX_TYPE_CODE || '-' || SCHOOL.JURISDICTION_CODE LOCAL_TAX_NAME, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'CURRENT', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) CURRENT_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'RUN', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) RUN_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PTD', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PYDATE', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PYDATE_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'MONTH', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) MONTH_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'QTD', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) QTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'YTD', PAA.TAX_UNIT_ID, SCHOOL.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) YTD_VAL FROM PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PAY_US_LOCAL_EE_WAGE_TYPES_V PETV , PAY_US_ASG_SCHOOLS_V SCHOOL WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND SCHOOL.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND SCHOOL.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND PETV.TAX_TYPE_CODE IN ('SCHOOL') AND PPA.ACTION_TYPE IN ('Q', 'R', 'V', 'I', 'B') AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE AND PETV.EFFECTIVE_END_DATE AND EXISTS (SELECT 'x' FROM PAY_ELEMENT_TYPES_F ELE, PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASG, PAY_RUN_RESULTS RR WHERE RR.JURISDICTION_CODE = SCHOOL.JURISDICTION_CODE AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE,'SCHOOL', 'City_SC_WK'), DECODE(PETV.TAX_TYPE_CODE,'SCHOOL', 'City_SC_RS'), DECODE(PETV.TAX_TYPE_CODE,'SCHOOL', 'County_SC_WK'), DECODE(PETV.TAX_TYPE_CODE,'SCHOOL', 'County_SC_RS')) AND RR.ELEMENT_TYPE_ID +0 = ELE.ELEMENT_TYPE_ID AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE, 'YEAR') AND PPA.EFFECTIVE_DATE AND PACT.ACTION_TYPE IN ('R', 'Q') )

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, CITY.JURISDICTION_CODE
, PETV.TAX_TYPE_ID
, PETV.EE_OR_ER_CODE
, PETV.LIMIT_TAX_FLAG
, PETV.TAX_DOMAIN_CODE
, PETV.TAX_TYPE_CODE
, PETV.BALANCE_CATEGORY_CODE
, PETV.USER_REPORTING_NAME
, PETV.ELEMENT_NAME
, PETV.ELEMENT_TYPE_ID
, PETV.EFFECTIVE_START_DATE
, PETV.EFFECTIVE_END_DATE
, PETV.BUSINESS_GROUP_ID
, PETV.LEGISLATION_CODE
, PETV.PROCESSING_PRIORITY
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, NAMES.CITY_NAME
, NAMES.CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, NAMES.STATE_CODE
, 'INVALID' COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, NAMES.COUNTY_CODE
, PETV.TAX_TYPE_CODE || '-' || DECODE(PETV.TAX_TYPE_CODE
, 'CITY'
, NAMES.CITY_NAME
, 'HT'
, NAMES.CITY_NAME
, CITY.JURISDICTION_CODE) LOCAL_TAX_NAME
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PTD'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PYDATE'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'MONTH'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, CITY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) YTD_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_LOCAL_EE_WAGE_TYPES_V PETV
, PAY_US_CITY_NAMES NAMES
, PAY_US_EMP_CITY_TAX_RULES_F CITY
, PAY_US_CITY_TAX_INFO_F CITF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN CITY.EFFECTIVE_START_DATE
AND CITY.EFFECTIVE_END_DATE
AND CITY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND NAMES.CITY_CODE = SUBSTR(CITY.JURISDICTION_CODE
, 8
, 4)
AND NAMES.COUNTY_CODE = SUBSTR(CITY.JURISDICTION_CODE
, 4
, 3)
AND NAMES.STATE_CODE = SUBSTR(CITY.JURISDICTION_CODE
, 1
, 2)
AND NAMES.PRIMARY_FLAG = 'Y'
AND CITF.JURISDICTION_CODE = CITY.JURISDICTION_CODE
AND DECODE(TAX_TYPE_CODE
, 'CITY'
, CITF.CITY_TAX
, 'HT'
, CITF.HEAD_TAX
, 'N') = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN CITF.EFFECTIVE_START_DATE
AND CITF.EFFECTIVE_END_DATE
AND PETV.TAX_TYPE_CODE IN ('CITY'
, 'HT')
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'I'
, 'B')
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE
AND PETV.EFFECTIVE_END_DATE
AND EXISTS (SELECT 'X'
FROM PAY_ELEMENT_TYPES_F ELE
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
WHERE RR.JURISDICTION_CODE = CITY.JURISDICTION_CODE
AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE
, 'CITY'
, 'CITY_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'CITY'
, 'CITY_RS')
, DECODE(PETV.TAX_TYPE_CODE
, 'HT'
, 'CITY_HT_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'HT'
, 'CITY_HT_RS'))
AND RR.ELEMENT_TYPE_ID + 0 = ELE.ELEMENT_TYPE_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q') ) UNION ALL SELECT PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, CNTY.JURISDICTION_CODE
, PETV.TAX_TYPE_ID
, PETV.EE_OR_ER_CODE
, PETV.LIMIT_TAX_FLAG
, PETV.TAX_DOMAIN_CODE
, PETV.TAX_TYPE_CODE
, PETV.BALANCE_CATEGORY_CODE
, PETV.USER_REPORTING_NAME
, PETV.ELEMENT_NAME
, PETV.ELEMENT_TYPE_ID
, PETV.EFFECTIVE_START_DATE
, PETV.EFFECTIVE_END_DATE
, PETV.BUSINESS_GROUP_ID
, PETV.LEGISLATION_CODE
, PETV.PROCESSING_PRIORITY
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, 'INVALID' CITY_NAME
, 'INVALID' CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, NAMES.STATE_CODE
, NAMES.COUNTY_NAME COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, NAMES.COUNTY_CODE
, PETV.TAX_TYPE_CODE || '-' || DECODE(PETV.TAX_TYPE_CODE
, 'COUNTY'
, NAMES.COUNTY_NAME
, CNTY.JURISDICTION_CODE) LOCAL_TAX_NAME
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PTD'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PYDATE'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'MONTH'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, CNTY.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) YTD_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_LOCAL_EE_WAGE_TYPES_V PETV
, PAY_US_EMP_COUNTY_TAX_RULES_F CNTY
, PAY_US_COUNTY_TAX_INFO_F CTIF
, PAY_US_COUNTIES NAMES
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN CNTY.EFFECTIVE_START_DATE
AND CNTY.EFFECTIVE_END_DATE
AND CNTY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND NAMES.COUNTY_CODE = SUBSTR(CNTY.JURISDICTION_CODE
, 4
, 3)
AND NAMES.STATE_CODE = SUBSTR(CNTY.JURISDICTION_CODE
, 1
, 2)
AND PETV.TAX_TYPE_CODE = 'COUNTY'
AND CTIF.JURISDICTION_CODE = CNTY.JURISDICTION_CODE
AND CTIF.COUNTY_TAX = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN CTIF.EFFECTIVE_START_DATE
AND CTIF.EFFECTIVE_END_DATE
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'I'
, 'B')
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE
AND PETV.EFFECTIVE_END_DATE
AND EXISTS (SELECT 'X'
FROM PAY_ELEMENT_TYPES_F ELE
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
WHERE RR.JURISDICTION_CODE = CNTY.JURISDICTION_CODE
AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE
, 'COUNTY'
, 'COUNTY_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'COUNTY'
, 'COUNTY_RS')
, DECODE(PETV.TAX_TYPE_CODE
, 'HT'
, 'COUNTY_HT_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'HT'
, 'COUNTY_HT_RS'))
AND RR.ELEMENT_TYPE_ID +0 = ELE.ELEMENT_TYPE_ID
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q') ) UNION ALL SELECT PAA.ASSIGNMENT_ID
, SCHOOL.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, SCHOOL.JURISDICTION_CODE
, PETV.TAX_TYPE_ID
, PETV.EE_OR_ER_CODE
, PETV.LIMIT_TAX_FLAG
, PETV.TAX_DOMAIN_CODE
, PETV.TAX_TYPE_CODE
, PETV.BALANCE_CATEGORY_CODE
, PETV.USER_REPORTING_NAME
, PETV.ELEMENT_NAME
, PETV.ELEMENT_TYPE_ID
, PETV.EFFECTIVE_START_DATE
, PETV.EFFECTIVE_END_DATE
, PETV.BUSINESS_GROUP_ID
, PETV.LEGISLATION_CODE
, PETV.PROCESSING_PRIORITY
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, 'INVALID' CITY_NAME
, 'INVALID' CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, 'INVALID' STATE_CODE
, 'INVALID' COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, 'INVALID' COUNTY_CODE
, PETV.TAX_TYPE_CODE || '-' || SCHOOL.JURISDICTION_CODE LOCAL_TAX_NAME
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PTD'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PYDATE'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'MONTH'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, SCHOOL.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) YTD_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_LOCAL_EE_WAGE_TYPES_V PETV
, PAY_US_ASG_SCHOOLS_V SCHOOL
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND SCHOOL.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND SCHOOL.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PETV.TAX_TYPE_CODE IN ('SCHOOL')
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'I'
, 'B')
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE
AND PETV.EFFECTIVE_END_DATE
AND EXISTS (SELECT 'X'
FROM PAY_ELEMENT_TYPES_F ELE
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
WHERE RR.JURISDICTION_CODE = SCHOOL.JURISDICTION_CODE
AND ELE.ELEMENT_NAME IN (DECODE(PETV.TAX_TYPE_CODE
, 'SCHOOL'
, 'CITY_SC_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'SCHOOL'
, 'CITY_SC_RS')
, DECODE(PETV.TAX_TYPE_CODE
, 'SCHOOL'
, 'COUNTY_SC_WK')
, DECODE(PETV.TAX_TYPE_CODE
, 'SCHOOL'
, 'COUNTY_SC_RS'))
AND RR.ELEMENT_TYPE_ID +0 = ELE.ELEMENT_TYPE_ID
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q') )