APPS.PAY_ASG_GEO_PKG SQL Statements

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

Line: 14

SELECT effective_start_date, effective_end_date
FROM per_assignments_f
WHERE assignment_id = P_assignment_id;

Line: 19

SELECT distinct --Bug 4671218 paf.effective_start_date, paf.effective_end_date,
       hsck.segment1 tax_unit_id
FROM per_assignments_f paf, hr_soft_coding_keyflex hsck
WHERE paf.assignment_id = P_assignment_id
  AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;

Line: 50

   SELECT 'Y'
   INTO v_exists
   FROM dual
   WHERE EXISTS (
   SELECT 'Y'
   FROM pay_us_asg_reporting
   WHERE assignment_id = P_assignment_id
     AND P_jurisdiction = substr(jurisdiction_code,1,2)||'-000-0000'
     AND v_tax_unit_id  = tax_unit_id);

Line: 67

   SELECT 'Y'
   INTO v_exists
   FROM dual
   WHERE EXISTS (
   SELECT 'Y'
   FROM pay_us_asg_reporting
   WHERE assignment_id = P_assignment_id
     AND P_jurisdiction = substr(jurisdiction_code,1,6)||'-0000'
     AND v_tax_unit_id  = tax_unit_id);

Line: 83

   SELECT 'Y'
   INTO v_exists
   FROM dual
   WHERE EXISTS(
   SELECT 'Y'
   FROM pay_us_asg_reporting
   WHERE assignment_id = P_assignment_id
     AND P_jurisdiction = jurisdiction_code
     AND v_tax_unit_id  = tax_unit_id);

Line: 94

   SELECT 'Y'
   INTO v_exists
   FROM dual
   WHERE EXISTS(
   SELECT 'Y'
   FROM pay_us_asg_reporting
   WHERE assignment_id = P_assignment_id
     AND P_jurisdiction = jurisdiction_code
     AND v_tax_unit_id  = tax_unit_id);

Line: 109

/* Update the table if nessesary*/
hr_utility.set_location('PAY_ASG_GEO_PKG', 4);

Line: 115

/* Update the table if nessesary*/
hr_utility.set_location('PAY_ASG_GEO_PKG', 4);

Line: 118

   IF v_level = 'County'  THEN  /* look for state to update */
     UPDATE pay_us_asg_reporting
     SET jurisdiction_code = P_jurisdiction
     WHERE assignment_id = P_assignment_id
       AND v_tax_unit_id  = tax_unit_id
       AND jurisdiction_code = substr(P_jurisdiction,1,2)||'-000-0000';

Line: 125

   ELSIF v_level = 'City'  THEN  /* look for state or county to update */
     UPDATE pay_us_asg_reporting
     SET jurisdiction_code = P_jurisdiction
     WHERE assignment_id = P_assignment_id
       AND v_tax_unit_id  = tax_unit_id
       AND (jurisdiction_code = substr(P_jurisdiction,1,2)||'-000-0000'
            OR
           jurisdiction_code = substr(P_jurisdiction,1,6)||'-0000');

Line: 137

     INSERT INTO pay_us_asg_reporting
         (assignment_id, effective_start_date, effective_end_date,
          jurisdiction_code, tax_unit_id)
       VALUES
         (P_assignment_id, v_start_date, v_end_date,
          P_jurisdiction, v_tax_unit_id);

Line: 163

  SELECT DISTINCT
         paf.assignment_id, hsck.segment1 tax_unit_id, pect.jurisdiction_code
  FROM   per_all_assignments_f          paf,
         hr_soft_coding_keyflex      hsck,
         pay_us_emp_city_tax_rules_f pect
  WHERE  paf.assignment_id = p_assignment_id
  AND    paf.assignment_id = pect.assignment_id
  AND    paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
UNION ALL -- get school districts (CITY)
  SELECT DISTINCT
         paf.assignment_id, hsck.segment1 tax_unit_id,
         substr(jurisdiction_code,1,2)||'-'||pect.school_district_code
  FROM   per_all_assignments_f        paf,
         hr_soft_coding_keyflex      hsck,
         pay_us_emp_city_tax_rules_f pect
  WHERE paf.assignment_id = p_assignment_id
    AND pect.school_district_code IS NOT NULL
    AND paf.assignment_id = pect.assignment_id
    AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;

Line: 184

 SELECT DISTINCT
         paf.assignment_id, hsck.segment1 tax_unit_id, pect.jurisdiction_code
  FROM  per_all_assignments_f           paf,
         hr_soft_coding_keyflex        hsck,
         pay_us_emp_county_tax_rules_f pect
  WHERE  paf.assignment_id = p_assignment_id
  AND    paf.assignment_id = pect.assignment_id
  AND    paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
UNION ALL -- get school districts (COUNTY)
  SELECT DISTINCT
         paf.assignment_id, hsck.segment1 tax_unit_id,
         substr(jurisdiction_code,1,2)||'-'||pect.school_district_code
  FROM  per_all_assignments_f          paf,
         hr_soft_coding_keyflex        hsck,
         pay_us_emp_county_tax_rules_f pect
  WHERE  paf.assignment_id = p_assignment_id
  AND    pect.school_district_code IS NOT NULL
  AND    paf.assignment_id = pect.assignment_id
  AND    paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;

Line: 206

SELECT DISTINCT
         paf.assignment_id, hsck.segment1 tax_unit_id, pest.jurisdiction_code
  FROM  per_all_assignments_f         paf,
         hr_soft_coding_keyflex       hsck,
         pay_us_emp_state_tax_rules_f pest
  WHERE  paf.assignment_id = p_assignment_id
  AND    paf.assignment_id = pest.assignment_id
  AND    paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;