DBA Data[Home] [Help]

APPS.PAY_US_TAX_BAL_SUMMARY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 69

  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 ;
Line: 275

  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');
Line: 292

  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;
Line: 365

  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;
Line: 620

   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;
Line: 669

     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');
Line: 696

   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;
Line: 721

      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');
Line: 740

   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;
Line: 767

   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;
Line: 813

   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;
Line: 857

       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');
Line: 875

   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;
Line: 903

   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;
Line: 943

    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);
Line: 967

   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;
Line: 988

    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;
Line: 1020

   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;
Line: 1061

   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;
Line: 1100

       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);
Line: 1117

   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;