The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
decode(tax_type_code, 'MEDICARE','Medicare',tax_type_code)||' '
||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'FIT', 1
,'SS' , 2
,'MEDICARE' , 3
,'FUTA', 4
,'EIC', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
,balance_category_code
FROM pay_us_fed_tax_types_v
WHERE ee_or_er_code = p_ee_er
AND element_name IN ('EIC', 'Medicare_EE', 'FIT', 'SS_EE')
AND balance_category_code IN ('WITHHELD', 'ADVANCED')
AND tax_type_code IN ('EIC', 'MEDICARE', 'FIT', 'SS')
UNION ALL
SELECT
decode(tax_type_code, 'MEDICARE','Medicare' ,tax_type_code)||' '
||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'FIT', 1
,'SS' , 2
,'MEDICARE' , 3
,'FUTA', 4
,'EIC', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
,balance_category_code
FROM pay_us_fed_ee_wage_types_v
WHERE ee_or_er_code = p_ee_er
AND ((tax_type_code in ('MEDICARE', 'SS')
AND balance_category_code = 'TAXABLE')
OR (tax_type_code IN ('FIT','EIC'))
)
UNION ALL
SELECT
decode(tax_type_code, 'MEDICARE','Medicare',tax_type_code)||' '
||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'FIT', 1
,'SS' , 2
,'MEDICARE' , 3
,'FUTA', 4
,'EIC', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
,balance_category_code
FROM pay_us_fed_liability_types_v
WHERE ee_or_er_code = p_ee_er
AND tax_type_code in ('MEDICARE', 'FUTA', 'SS')
AND element_name IN ('Medicare_ER', 'FUTA', 'SS_ER')
UNION ALL
SELECT
decode(tax_type_code, 'MEDICARE','Medicare' ,tax_type_code)||' '
||decode(tax_type_code,'FIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'FIT', 1
,'SS' , 2
,'MEDICARE' , 3
,'FUTA', 4
,'EIC', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
,balance_category_code
FROM pay_us_fed_er_wage_types_v
WHERE ee_or_er_code = p_ee_er
AND tax_type_code in ('MEDICARE', 'SS','FUTA')
AND balance_category_code = 'TAXABLE'
AND element_name in ('Medicare_ER','FUTA','SS_ER')
order by 2,3 ;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
jurisdiction_code
FROM pay_assignment_actions paa
,pay_us_emp_state_tax_rules_f pue
,pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_asact_id
AND pue.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between pue.effective_start_date
AND pue.effective_end_date
AND pue.state_code = p_state_code
AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I');
SELECT DISTINCT
tax_type_code||' '
||decode(tax_type_code,'SIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'SIT', 1
,'SUI', 2
,'SDI' , 3
,'WCE' , 4
,'WC2', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
, balance_category_code
, jurisdiction_code
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_us_state_tax_types_v pstt
,pay_us_emp_state_tax_rules_f pue
WHERE paa.assignment_action_id = p_asact_id
AND pue.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between pue.effective_start_date
AND pue.effective_end_date
AND pue.state_code = p_state_code
AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
AND NVL(ppa.date_earned,ppa.effective_date) between
pstt.effective_start_date AND pstt.effective_end_date
UNION ALL
SELECT DISTINCT
tax_type_code||' '
||decode(tax_type_code,'SIT',decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)), initcap(balance_category_code)) prompt
, decode (tax_type_code
,'SIT', 1
,'SUI', 2
,'SDI' , 3
,'WCE' , 4
,'WC2', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code
FROM pay_us_state_ee_wage_types_v pstt
WHERE p_eff_dt between
pstt.effective_start_date AND pstt.effective_end_date
AND ((tax_type_code = 'SIT'
AND balance_category_code = 'SUBJECT')
OR (tax_type_code <> 'SIT'
AND balance_category_code = 'TAXABLE')
)
AND (element_name like 'SIT%'
OR element_name like 'SDI%'
OR element_name like 'SUI%' )
AND pstt.element_type_id >= 0
ORDER BY 2,3;
SELECT DISTINCT
tax_type_code||' '
||decode(tax_type_code,'SIT',decode(balance_category_code,
'SUBJECT','Taxable',
initcap(balance_category_code)),
initcap(balance_category_code)) prompt
, decode (tax_type_code
,'SIT', 1
,'SUI', 2
,'SDI' , 3
,'WCE' , 4
,'WC2', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
, balance_category_code
, jurisdiction_code
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_us_state_liability_types_v pstt
,pay_us_emp_state_tax_rules_f pue
WHERE paa.assignment_action_id = p_asact_id
AND pue.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between pue.effective_start_date
AND pue.effective_end_date
AND pue.state_code = p_state_code
AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
AND NVL(ppa.date_earned,ppa.effective_date) between
pstt.effective_start_date AND pstt.effective_end_date
AND pstt.ELEMENT_NAME IN ('SDI_ER', 'SUI_ER')
AND pstt.BALANCE_CATEGORY_CODE = 'LIABILITY'
AND pstt.TAX_TYPE_CODE in ('SDI','SUI')
UNION ALL
SELECT DISTINCT
tax_type_code||' '||
decode(tax_type_code,'SIT',decode(balance_category_code,
'SUBJECT','Taxable',
initcap(balance_category_code)),
initcap(balance_category_code)) prompt
, decode (tax_type_code
,'SIT', 1
,'SUI', 2
,'SDI' , 3
,'WCE' , 4
,'WC2', 5
, 6) ordercol1
, decode (balance_category_code
,'GROSS', 1
,'TAXABLE' , 2
,'WITHHELD' , 3
,'SUBJECT', 4
,'ADVANCED', 5
, 6) ordercol2
, tax_type_code
, balance_category_code
, jurisdiction_code
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_us_state_er_wage_types_v pstt
,pay_us_emp_state_tax_rules_f pue
WHERE pue.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between pue.effective_start_date
AND pue.effective_end_date
AND pue.state_code = p_state_code
AND ppa.action_type in ('Q', 'R', 'V', 'B', 'I')
AND NVL(ppa.date_earned,ppa.effective_date) between
pstt.effective_start_date AND pstt.effective_end_date
AND paa.assignment_action_id = p_asact_id
AND ELEMENT_NAME IN ('SDI_ER', 'SUI_ER')
AND tax_type_code in ('SDI','SUI')
AND balance_category_code = 'TAXABLE'
ORDER BY 2,3;
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County',
'CITY', 'City', tax_type_code)||' '
||initcap(balance_category_code) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY' , 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, city.jurisdiction_code
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 ,
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND (city.jurisdiction_code||'' = p_jurisdiction OR
city.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
AND tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
cnty.jurisdiction_code jurisdiction_code
FROM
pay_assignment_actions paa ,
pay_payroll_actions ppa ,
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 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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND (cnty.jurisdiction_code||'' = p_jurisdiction or
cnty.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
||initcap(balance_category_code) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY' , 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code jurisdiction_code
FROM
pay_us_local_tax_types_v petv
WHERE petv.tax_type_code = 'COUNTY'
AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
AND petv.tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
jurisdiction_code
FROM pay_assignment_actions paa ,
pay_payroll_actions ppa ,
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 ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
AND paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND paa.tax_unit_id = p_tax_unit_id
AND (jurisdiction_code||'' = p_jurisdiction or
jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
||initcap(balance_category_code) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY' , 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code jurisdiction_code
FROM pay_us_local_tax_types_v petv
WHERE p_eff_date between petv.effective_start_date AND petv.effective_end_date
AND petv.tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY', 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, city.jurisdiction_code
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND (city.jurisdiction_code||'' = p_jurisdiction or
city.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
AND tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY', 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, cnty.jurisdiction_code
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND (cnty.jurisdiction_code||'' = p_jurisdiction or
cnty.jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000')
AND tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
jurisdiction_code
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, 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 ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
AND paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND paa.tax_unit_id = p_tax_unit_id
AND (jurisdiction_code||'' = p_jurisdiction or
jurisdiction_code||'' = substr(p_jurisdiction,1,6)||'-0000');
SELECT DISTINCT
decode(tax_type_code, 'COUNTY', 'County', 'CITY', 'City', tax_type_code)||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, decode (tax_type_code
,'COUNTY', 1
,'CITY', 2
,'HT' , 3
, 6) ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code jurisdiction_code
FROM pay_us_local_ee_wage_types_v petv
WHERE petv.tax_type_code in ('SCHOOL')
AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
AND petv.tax_type_code <> 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
tax_type_code||' '
||initcap(balance_category_code) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, city.jurisdiction_code
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 ,
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND city.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
AND tax_type_code = 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
cnty.jurisdiction_code jurisdiction_code
FROM pay_assignment_actions paa ,
pay_payroll_actions ppa ,
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 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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND cnty.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school);
SELECT DISTINCT
tax_type_code||' '
||initcap(balance_category_code) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code jurisdiction_code
FROM
pay_us_local_tax_types_v petv
WHERE petv.tax_type_code = 'COUNTY'
AND p_eff_date between petv.effective_start_date AND petv.effective_end_date
AND petv.tax_type_code = 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
tax_type_code||' '
||initcap(balance_category_code) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, jurisdiction_code
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 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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND paa.tax_unit_id = p_tax_unit_id
AND jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
AND tax_type_code = 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
tax_type_code||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, city.jurisdiction_code
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND city.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
AND tax_type_code = 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
tax_type_code||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, cnty.jurisdiction_code
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 paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND tax_unit_id = p_tax_unit_id
AND cnty.jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school)
AND tax_type_code = 'SCHOOL'
ORDER BY 2,3;
SELECT DISTINCT
NVL(ppa.date_earned,ppa.effective_date)effective_date,
jurisdiction_code
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, 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 ppa.action_type in ('Q', 'R', 'V', 'I', 'B')
AND paa.assignment_id = p_assg_id
AND assignment_action_id = p_asact_id
AND paa.tax_unit_id = p_tax_unit_id
AND jurisdiction_code||'' = (substr(p_jurisdiction,1,3)||p_school);
SELECT DISTINCT
tax_type_code||' '
||decode(balance_category_code, 'SUBJECT',
'Taxable', initcap(balance_category_code)) prompt
, 1 ordercol1
, decode (balance_category_code
,'TAXABLE' , 2
,'SUBJECT' , 3
,'WITHHELD' , 4
, 6) ordercol2
, tax_type_code
, balance_category_code
, p_jurisdiction_code jurisdiction_code
FROM pay_us_local_ee_wage_types_v petv
WHERE p_eff_date between petv.effective_start_date AND petv.effective_end_date
AND petv.tax_type_code = 'SCHOOL'
ORDER BY 2,3;