VIEW: APPS.PAY_US_LOCAL_TAXES_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_tax_types_v petv ,pay_us_emp_city_tax_rules_f city ,pay_us_city_names names 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 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', 'B') /* Added 'B' for Bug 12970173 */ ) AND EXISTS (select 'x' from pay_us_city_tax_info_f citf where decode (tax_type_code ,'CITY' ,citf.city_tax ,'HT' ,citf.head_tax ,'N') = 'Y' and citf.effective_start_date <= ppa.effective_date and citf.effective_end_date >= trunc(ppa.effective_date,'Y') AND citf.jurisdiction_code = city.jurisdiction_code ) 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_tax_types_v petv ,pay_us_emp_county_tax_rules_f cnty ,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 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', 'B') /* Added 'B' for Bug 12970173 */ ) AND EXISTS (select 'x' from pay_us_county_tax_info_f ctif where ctif.county_tax = 'Y' and ctif.effective_start_date <= ppa.effective_date and ctif.effective_end_date >= trunc(ppa.effective_date,'Y') AND ctif.jurisdiction_code = cnty.jurisdiction_code ) 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_tax_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', 'B') /* Added 'B' for Bug 12970173 */ )

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_TAX_TYPES_V PETV
, PAY_US_EMP_CITY_TAX_RULES_F CITY
, PAY_US_CITY_NAMES NAMES
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 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'
, 'B') /* ADDED 'B' FOR BUG 12970173 */ )
AND EXISTS (SELECT 'X'
FROM PAY_US_CITY_TAX_INFO_F CITF
WHERE DECODE (TAX_TYPE_CODE
, 'CITY'
, CITF.CITY_TAX
, 'HT'
, CITF.HEAD_TAX
, 'N') = 'Y'
AND CITF.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE
AND CITF.EFFECTIVE_END_DATE >= TRUNC(PPA.EFFECTIVE_DATE
, 'Y')
AND CITF.JURISDICTION_CODE = CITY.JURISDICTION_CODE ) 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_TAX_TYPES_V PETV
, PAY_US_EMP_COUNTY_TAX_RULES_F CNTY
, 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 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'
, 'B') /* ADDED 'B' FOR BUG 12970173 */ )
AND EXISTS (SELECT 'X'
FROM PAY_US_COUNTY_TAX_INFO_F CTIF
WHERE CTIF.COUNTY_TAX = 'Y'
AND CTIF.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE
AND CTIF.EFFECTIVE_END_DATE >= TRUNC(PPA.EFFECTIVE_DATE
, 'Y')
AND CTIF.JURISDICTION_CODE = CNTY.JURISDICTION_CODE ) 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_TAX_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'
, 'B') /* ADDED 'B' FOR BUG 12970173 */ )