APPS.GHR_PA_REQUESTS_PKG2 SQL Statements

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

Line: 21

  SELECT 'Request Number:'||par.request_number||
        ', 1st NOA Code:'||par.first_noa_code||
        DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
  ------      ', Effective Date:'||TO_CHAR(par.effective_date,'DD-MON-YYYY')||
        ', Effective Date:'||fnd_date.date_to_chardate(par.effective_date)||
        DECODE(prh.action_taken,'FUTURE_ACTION', ', APPROVED',
               ', Routed To '||DECODE(gbx.name,null, 'User:'||prh.user_name, 'Groupbox:'||gbx.name))  list_info
  FROM   ghr_groupboxes         gbx
        ,ghr_pa_routing_history prh
        ,ghr_pa_requests        par
  WHERE  gbx.groupbox_id(+) = prh.groupbox_id
  AND    par.person_id = p_person_id
  AND    par.pa_request_id <> NVL(p_pa_request_id,-999)
  AND    prh.pa_request_id = par.pa_request_id
  AND    prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
                                      FROM   ghr_pa_routing_history prh2
                                      WHERE  prh2.pa_request_id = par.pa_request_id)
  AND    NVL(prh.action_taken,'==@@==') NOT IN ('CANCELED','UPDATE_HR_COMPLETE')
  AND    (prh.date_notification_sent is not null
     OR prh.action_taken = 'FUTURE_ACTION')
  ORDER BY par.pa_request_id;

Line: 67

  SELECT DECODE(prh.action_taken,'UPDATE_HR_COMPLETE', 'Processed:','FUTURE_ACTION', 'Pending:')||
        'Request Number:'||par.request_number||
        ', 1st NOA Code:'||par.first_noa_code||
        DECODE(par.first_noa_cancel_or_correct,'CANCEL','(CANCELED)')||
        DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
        DECODE(par.second_noa_cancel_or_correct,'CANCEL','(CANCELED)')||
   ---     ', Effective Date:'||TO_CHAR(par.effective_date,'DD-MON-YYYY') list_info
        ', Effective Date:'||fnd_date.date_to_chardate(par.effective_date) list_info
  FROM   ghr_pa_routing_history prh
        ,ghr_pa_requests        par
  WHERE  par.person_id      = p_person_id
  AND    par.effective_date >= p_effective_date
  AND    prh.pa_request_id  = par.pa_request_id
  AND    prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
                                      FROM   ghr_pa_routing_history prh2
                                      WHERE  prh2.pa_request_id = par.pa_request_id)
  AND    prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
  AND    par.NOA_FAMILY_CODE <> 'CANCEL'
  AND (   ( par.second_noa_code IS NULL
        AND NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
          )
     OR  (  par.second_noa_code IS NOT NULL
        AND  par.NOA_FAMILY_CODE <> 'CORRECT'
        AND ( NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
          OR NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
            )
         )
     OR  (  par.second_noa_code IS NOT NULL
        AND  par.NOA_FAMILY_CODE = 'CORRECT'
        AND  NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
         )
       )
  ORDER BY par.effective_date, par.pa_request_id;

Line: 153

  SELECT par.noa_family_code
  FROM   ghr_pa_requests par
  WHERE  par.pa_request_id = p_altered_pa_request_id;

Line: 180

  SELECT rei.rei_information3 agency_code
  FROM   ghr_pa_request_extra_info rei
  WHERE  rei.pa_request_id = p_pa_request_id
  AND    rei.information_type = 'GHR_US_PAR_APPT_TRANSFER'
  AND EXISTS (SELECT 1
              FROM   ghr_noa_families          naf
                    ,ghr_pa_request_info_types rit
              WHERE  rei.information_type = rit.information_type
              AND    rit.noa_family_code = naf.noa_family_code
              AND    naf.nature_of_action_id = p_noa_id);

Line: 208

  SELECT rei.rei_information3 agency_code
  FROM   ghr_pa_request_extra_info rei
  WHERE  rei.pa_request_id = p_pa_request_id
  AND    rei.information_type = 'GHR_US_PAR_MASS_TERM'
  AND EXISTS (SELECT 1
              FROM   ghr_noa_families          naf
                    ,ghr_pa_request_info_types rit
              WHERE  rei.information_type = rit.information_type
              AND    rit.noa_family_code = naf.noa_family_code
              AND    naf.nature_of_action_id = p_noa_id);

Line: 234

 SELECT pdf.segment3 nfc_agency
       FROM per_position_definitions pdf, hr_all_positions_f pos
  WHERE pos.position_id = p_position_id
  AND   p_effective_date between pos.effective_start_date
        and pos.effective_end_date
  AND   pos.position_definition_id = pdf.position_definition_id;

Line: 255

SELECT pdf.segment4 poi
       FROM per_position_definitions pdf, hr_all_positions_f pos
  WHERE pos.position_id = c_position_id
  AND   c_effective_date between pos.effective_start_date
        and pos.effective_end_date
  AND   pos.position_definition_id = pdf.position_definition_id;

Line: 277

select segment4 poi
 from per_position_definitions ppd,hr_all_positions_f pos
where ppd.position_definition_id = pos.position_definition_id
and pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date and
   pos.effective_end_date;

Line: 284

SELECT hoi.org_information_context
            , hoi.org_information6
       FROM hr_organization_information hoi
       WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
         AND hoi.organization_id = p_bg_id
         AND hoi.org_information6 = 'Y';

Line: 307

select segment3 nfc_agency
 from per_position_definitions ppd,hr_all_positions_f pos
where ppd.position_definition_id = pos.position_definition_id
and pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date
and pos.effective_end_date;

Line: 314

SELECT hoi.org_information_context
            , hoi.org_information6
       FROM hr_organization_information hoi
       WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
         AND hoi.organization_id = p_bg_id
         AND hoi.org_information6 = 'Y';

Line: 338

select segment7 grade_id
 from per_position_definitions ppd,hr_all_positions_f pos
where ppd.position_definition_id = pos.position_definition_id
and pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date
and pos.effective_end_date;

Line: 346

select name grade_name
from per_grades
where grade_id = c_grade_id;

Line: 372

 SELECT gdf.segment1 || '-' || gdf.segment2 pay_plan
  FROM  per_grade_definitions   gdf
       ,per_grades              grd
  WHERE   grd.grade_id in
(select segment7
 from per_position_definitions ppd,hr_all_positions_f pos
where ppd.position_definition_id = pos.position_definition_id
and pos.position_id = p_position_id
and p_effective_date between pos.effective_start_date and
pos.effective_end_date)
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 384

SELECT hoi.org_information_context
            , hoi.org_information6
       FROM hr_organization_information hoi
       WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
         AND hoi.organization_id = p_bg_id
         AND hoi.org_information6 = 'Y';

Line: 404

  SELECT gdf.segment1 pay_plan
  FROM  per_grade_definitions   gdf
       ,per_grades              grd
       ,per_position_extra_info poi
  WHERE poi.position_id = p_position_id
  AND   poi.information_type = 'GHR_US_POS_VALID_GRADE'
  AND   grd.grade_id = poi.poei_information3
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 425

  SELECT gdf.segment2 grade_or_level
  FROM  per_grade_definitions   gdf
       ,per_grades              grd
       ,per_position_extra_info poi
  WHERE poi.position_id = p_position_id
  AND   poi.information_type = 'GHR_US_POS_VALID_GRADE'
  AND   grd.grade_id = poi.poei_information3
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 446

  SELECT oi.org_information2
  FROM   hr_organization_information oi
  WHERE  oi.organization_id = p_business_group_id
  AND    oi.org_information_context = 'GHR_US_ORG_INFORMATION';

Line: 540

select paf.location_id  location_id
from per_assignments_f           paf,
     per_assignment_status_types ast
where paf.person_id            = p_person_id
and   p_effective_date
      between paf.effective_start_date and paf.effective_end_date
and   ast.assignment_status_type_id = paf.assignment_status_type_id
and   ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN');

Line: 550

select 1 from per_assignments_f
Where person_id            = p_person_id
and   effective_start_date = to_date('19'||'99/01/01','YYYY/MM/DD');

Line: 674

select EMPLOYEE_ASSIGNMENT_ID,
       EFFECTIVE_DATE
from ghr_pa_requests
where pa_request_id = p_altered_pa_request_id;

Line: 740

select pos.effective_start_date,pos.effective_end_date,typ.system_type_cd status
from hr_all_positions_f pos, per_shared_types typ
where p_effective_date
      between pos.effective_start_date and pos.effective_end_date
and   pos.business_group_id = p_business_group_id
and   pos.position_id = p_position_id
and   pos.availability_status_id = typ.shared_type_id
union
select pos1.effective_start_date,pos1.effective_end_date,typ1.system_type_cd status
from hr_all_positions_f pos1, per_shared_types typ1
where p_effective_date <= pos1.effective_start_date
and   pos1.business_group_id = p_business_group_id
and   pos1.position_id = p_position_id
and   pos1.availability_status_id = typ1.shared_type_id
order by 1;

Line: 806

select effective_start_date,effective_end_date,system_type_cd status
from hr_all_positions_f pos, per_shared_types typ
where p_effective_date
      between effective_start_date and effective_end_date
and   position_id = p_position_id
and   pos.business_group_id = p_business_group_id
and   pos.availability_status_id = typ.shared_type_id
order by 1;

Line: 846

select (nvl(REI_INFORMATION9,0)  +
        nvl(REI_INFORMATION10,0) +
        nvl(REI_INFORMATION3,0)  +
        nvl(REI_INFORMATION4,0)  +
        nvl(REI_INFORMATION5,0)  +
        nvl(REI_INFORMATION6,0)  +
        nvl(REI_INFORMATION7,0)  +
        nvl(REI_INFORMATION8,0))  cal_amt,
        nvl(REI_INFORMATION11,0)  tot_amt
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_id
and information_type = 'GHR_US_PAR_MD_DDS_PAY';

Line: 880

      SELECT per.employee_number
        FROM per_people_f per
       WHERE per.person_id = p_person_id
         AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
                                                       AND per.effective_end_date;

Line: 996

   p_dyn_query := 'SELECT 1 FROM '||p_table_name||' WHERE '||p_condition;