VIEW: APPS.PAY_US_ASG_RUN_LOCAL_TAX_RBR_V

Source

View Text - Preformatted

SELECT /*+ ORDERED */ 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, putt.tax_type_id, ee_or_er_code, limit_tax_flag, tax_domain_code, tax_type_code, balance_category_code, user_reporting_name, ppa.business_group_id, '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, tax_type_code || '-' || DECODE (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 (balance_category_code, tax_type_code, 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 FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_us_emp_city_tax_rules_f city, pay_us_tax_types putt, pay_us_tax_balances putb, pay_us_city_names names, pay_us_city_tax_info_f citf, pay_run_balances prb, pay_defined_balances pdb 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 = city.city_code AND names.county_code = city.county_code AND names.state_code = city.state_code 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 tax_type_code IN ('CITY', 'HT') AND putt.tax_type_id = putb.tax_type_id AND putt.tax_domain_code = 'LOCALITY' AND putb.balance_category_code = 'WITHHELD' AND putb.ee_or_er_code = 'EE' AND prb.assignment_action_id = paa.assignment_action_id AND prb.tax_unit_id = paa.tax_unit_id AND prb.jurisdiction_code = city.jurisdiction_code and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = putb.balance_type_id UNION ALL SELECT /*+ ORDERED */ 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, putt.tax_type_id, ee_or_er_code, limit_tax_flag, tax_domain_code, tax_type_code, balance_category_code, user_reporting_name, ppa.business_group_id, '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, tax_type_code || '-' || DECODE (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 (balance_category_code, tax_type_code, 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 FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_us_emp_county_tax_rules_f cnty, pay_us_tax_types putt, pay_us_tax_balances putb, pay_us_county_tax_info_f ctif, pay_us_counties names, pay_run_balances prb, pay_defined_balances pdb 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 = cnty.county_code AND names.state_code = cnty.state_code AND 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 putt.tax_type_id = putb.tax_type_id AND putt.tax_domain_code = 'LOCALITY' AND putb.balance_category_code = 'WITHHELD' AND putb.ee_or_er_code = 'EE' AND prb.assignment_action_id = paa.assignment_action_id AND prb.tax_unit_id = paa.tax_unit_id AND prb.effective_date BETWEEN TRUNC (ppa.effective_date, 'Y') AND ppa.effective_date AND SUBSTR(prb.jurisdiction_code, 1, 6) || '-0000' = cnty.jurisdiction_code and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = putb.balance_type_id UNION ALL SELECT /*+ ORDERED */ paa.assignment_id, paa.tax_unit_id, paa.assignment_action_id, paa.action_status, paa.payroll_action_id, ppa.action_type, ppa.effective_date, school.jurisdiction_code, putt.tax_type_id, ee_or_er_code, limit_tax_flag, tax_domain_code, tax_type_code, balance_category_code, user_reporting_name, ppa.business_group_id, '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, 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 (balance_category_code, tax_type_code, 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 FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_us_tax_types putt, pay_us_tax_balances putb, pay_us_asg_reporting school, pay_run_balances prb, pay_defined_balances pdb WHERE paa.payroll_action_id = ppa.payroll_action_id AND tax_type_code = 'SCHOOL' AND school.assignment_id = paa.assignment_id AND LENGTH (school.jurisdiction_code) = 8 AND school.tax_unit_id = paa.tax_unit_id AND putt.tax_type_id = putb.tax_type_id AND putt.tax_domain_code = 'LOCALITY' AND putb.balance_category_code = 'WITHHELD' AND putb.ee_or_er_code = 'EE' AND prb.assignment_action_id = paa.assignment_action_id AND prb.tax_unit_id = paa.tax_unit_id AND prb.jurisdiction_code = school.jurisdiction_code and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = putb.balance_type_id

View Text - HTML Formatted

SELECT /*+ ORDERED */ 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
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, '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
, TAX_TYPE_CODE || '-' || DECODE (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 (BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, 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
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_EMP_CITY_TAX_RULES_F CITY
, PAY_US_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_CITY_NAMES NAMES
, PAY_US_CITY_TAX_INFO_F CITF
, PAY_RUN_BALANCES PRB
, PAY_DEFINED_BALANCES PDB
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 = CITY.CITY_CODE
AND NAMES.COUNTY_CODE = CITY.COUNTY_CODE
AND NAMES.STATE_CODE = CITY.STATE_CODE
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 TAX_TYPE_CODE IN ('CITY'
, 'HT')
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PRB.JURISDICTION_CODE = CITY.JURISDICTION_CODE
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID
AND PDB.BALANCE_TYPE_ID = PUTB.BALANCE_TYPE_ID UNION ALL SELECT /*+ ORDERED */ 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
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, '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
, TAX_TYPE_CODE || '-' || DECODE (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 (BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, 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
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_EMP_COUNTY_TAX_RULES_F CNTY
, PAY_US_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_COUNTY_TAX_INFO_F CTIF
, PAY_US_COUNTIES NAMES
, PAY_RUN_BALANCES PRB
, PAY_DEFINED_BALANCES PDB
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 = CNTY.COUNTY_CODE
AND NAMES.STATE_CODE = CNTY.STATE_CODE
AND 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 PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PRB.EFFECTIVE_DATE BETWEEN TRUNC (PPA.EFFECTIVE_DATE
, 'Y')
AND PPA.EFFECTIVE_DATE
AND SUBSTR(PRB.JURISDICTION_CODE
, 1
, 6) || '-0000' = CNTY.JURISDICTION_CODE
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID
AND PDB.BALANCE_TYPE_ID = PUTB.BALANCE_TYPE_ID UNION ALL SELECT /*+ ORDERED */ PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, SCHOOL.JURISDICTION_CODE
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, '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
, 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 (BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, 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
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_US_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_ASG_REPORTING SCHOOL
, PAY_RUN_BALANCES PRB
, PAY_DEFINED_BALANCES PDB
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND TAX_TYPE_CODE = 'SCHOOL'
AND SCHOOL.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND LENGTH (SCHOOL.JURISDICTION_CODE) = 8
AND SCHOOL.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PRB.JURISDICTION_CODE = SCHOOL.JURISDICTION_CODE
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID
AND PDB.BALANCE_TYPE_ID = PUTB.BALANCE_TYPE_ID