APPS.PAY_US_LOC_CHANGE SQL Statements

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

Line: 6

    insert the values into temporary table and delete the values from
    temporary table once the data is printed.
*/

PROCEDURE cnt_print_report
IS
      l6_wait           BOOLEAN;

Line: 27

      select userenv('sessionid')
      into l_session_id
      from dual;

Line: 64

     DELETE
       FROM pay_us_rpt_totals
      WHERE attribute20 = 'LOCATION_CHANGE'
      AND   organization_id = l_session_id;

Line: 79

PROCEDURE get_insert_values (  p_proc_name                VARCHAR2,
                               p_assignment_id            NUMBER,
                               p_location_id              NUMBER,
                               p_gre_name          IN OUT NOCOPY VARCHAR2,
                               p_full_name         IN OUT NOCOPY VARCHAR2,
                               p_assignment_number IN OUT NOCOPY VARCHAR2,
                               p_location_code     IN OUT NOCOPY VARCHAR2)
IS

 /* cursor to get the person's assignment detail  */

CURSOR  csr_assignment_tax_detail
    IS
SELECT  hou.Name,
        ppf.full_name,
        paf.assignment_number
  FROM
        hr_organization_units hou,
        hr_soft_coding_keyflex hsck,
        per_people_f ppf,
        per_assignments_f paf
 WHERE
        paf.assignment_id = p_assignment_id
   AND  paf.person_id     = ppf.person_id
   AND  paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
   AND  hsck.segment1 = hou.organization_id
-- Changing the cursor to pick up the most recent date
   AND  ppf.effective_start_date = (select max(ppf_inner.effective_start_date)
                                    from per_people_f ppf_inner
                                    where ppf_inner.person_id = ppf.person_id) ;

Line: 113

SELECT  location_code
  FROM  hr_locations
 WHERE  location_id = p_location_id;

Line: 117

l_proc_name varchar2(50) := 'get_insert_value';

Line: 120

  hr_utility.trace('In proc : get_insert_values');

Line: 164

END  get_insert_values ;

Line: 194

       select userenv('sessionid')
       into l_session_id
       from dual;

Line: 198

        INSERT
          INTO    pay_us_rpt_totals
               (
                  organization_id,
                  gre_name,
                  location_name,
                  attribute1,
                  attribute2,
                  attribute3,
                  attribute4,
                  attribute5,
                  attribute20
               )
        VALUES (
                  l_session_id,
                  p_tax_unit_name,
                  p_location_code,
                  p_emp_full_name,
                  p_assignment_number,
                  to_char(p_effective_start_date,'mm/dd/yyyy'),
                  to_char(p_effective_end_date,'mm/dd/yyyy'),
                  p_error,
                  'LOCATION_CHANGE');

Line: 231

procedure update_tax(errbuf     OUT     NOCOPY VARCHAR2,
                     retcode    OUT     NOCOPY NUMBER,
                     p_location_id in number) is

 l_ret_code             number;

Line: 265

  select  /*+ index(hsck HR_SOFT_CODING_KEYFLEX_PK) */ paf.assignment_id assignment_id,
	      paf.person_id,
          max(paf.effective_end_date) effective_end_date,
          min(paf.effective_start_date) effective_start_date,
          paf.business_group_id,
          paf.assignment_type
  from    per_all_assignments_f paf,
	      hr_soft_coding_keyflex hsck
  where  (paf.location_id = p_loc_id
  or      hsck.segment18 = to_char(p_loc_id))   -- #3056158
  and 	  hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
  group by assignment_id,
	      paf.person_id,
          paf.business_group_id,
          paf.assignment_type
  ;

Line: 284

  select   paf.location_id,
           hsck.segment18,
           paf.effective_start_date,
           paf.effective_end_date
  from     per_all_assignments_f paf,
           hr_soft_coding_keyflex hsck
  where    paf.assignment_id = p_assignment_id
  and      hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
  and      effective_start_date >= p_def_date
  order by effective_start_date;

Line: 297

    hr_utility.trace('In Proc. UPDATE_TAX');

Line: 337

               hr_utility.set_location('update_tax', 55);

Line: 422

         get_insert_values(
			'LOCATION_CHANGE',
                        emp_rec.assignment_id,
                        p_location_id,
                        l_gre_name,
                        l_full_name,
                        l_assignment_number,
                        l_location_code);

Line: 444

end update_tax;