APPS.GHR_COMPLAINTS2_PKG SQL Statements

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

Line: 16

  SELECT per.full_name
        ,per.national_identifier
  FROM   per_people_v      per
  WHERE  p_complainant_person_id = per.person_id
  AND    p_effective_date between per.effective_start_date and per.effective_end_date; */

Line: 23

  SELECT per.full_name
        ,per.national_identifier
  FROM   per_people_f      per
  WHERE per.person_id = p_complainant_person_id
  AND p_effective_date between per.effective_start_date and per.effective_end_date;

Line: 73

  SELECT pas.assignment_id, pas.position_id, pas.grade_id, pas.job_id
  FROM   per_assignments_f pas
  WHERE  p_complainant_person_id = pas.person_id
  AND    assignment_type <> 'B'
  AND    p_effective_date between pas.effective_start_date and pas.effective_end_date;

Line: 552

  SELECT poi.description
  FROM   ghr_pois poi
  WHERE  poi.personnel_office_id = p_personnel_office_id;

Line: 573

  SELECT gdf.segment1 pay_plan
        ,gdf.segment2 grade_or_level
        ,grd.name     grade_name
  FROM  per_grade_definitions gdf
       ,per_grades            grd
  WHERE grd.grade_id = p_grade_id
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 607

  SELECT count(*) rec_count
  FROM   ghr_complaints2 cmp
  WHERE  cmp.consolidated_complaint_id = p_consolidated_complaint_id
  AND    (cmp.complaint_id <> p_complaint_id OR p_complaint_id IS NULL);

Line: 627

  SELECT docket_number
  FROM   ghr_complaints2 cmp
  WHERE  cmp.consolidated_complaint_id = p_consolidated_complaint_id;

Line: 646

  SELECT 1
  FROM   ghr_compl_claims clm
  WHERE  clm.complaint_id = p_complaint_id
  AND    clm.mixed_flag   = 'Y';

Line: 667

SELECT 1
  FROM   ghr_compl_appeals cap
  WHERE  cap.complaint_id = p_complaint_id
  AND    cap.appeal_date  = (SELECT MAX(cmp.appeal_date)
                             FROM ghr_compl_appeals cmp
                             WHERE cmp.complaint_id = p_complaint_id)
  AND    cap.decision     IN ('30', '40');

Line: 696

  SELECT complainant_person_id,docket_number
  FROM   ghr_complaints2 cmp
  WHERE  cmp.complaint_id = p_complaint_id;

Line: 728

  SELECT loc.lookup_code
  FROM   fnd_common_lookups loc
  WHERE  loc.application_id = p_application_id
  AND    loc.lookup_type = p_lookup_type
  AND    loc.meaning     = p_meaning;

Line: 755

  SELECT loc.meaning
  FROM   fnd_common_lookups loc
  WHERE  loc.application_id = p_application_id
  AND    loc.lookup_type    = p_lookup_type
  AND    loc.lookup_code    = p_lookup_code;

Line: 778

SELECT 1
 FROM  ghr_compl_ca_details cdt
 WHERE cdt.compl_ca_header_id in (select compl_ca_header_id
                                 from ghr_compl_ca_headers
                                 where complaint_id = p_complaint_id);

Line: 800

SELECT 1
 FROM  ghr_compl_claims clm, ghr_compl_bases cba
 WHERE clm.complaint_id = p_complaint_id
 AND clm.compl_claim_id = cba.compl_claim_id
 AND clm.phase in ('20','30')
 AND cba.basis is NOT NULL;

Line: 822

SELECT 1
 FROM  ghr_compl_claims clm, ghr_compl_bases cba
 WHERE clm.complaint_id = p_complaint_id
 AND clm.compl_claim_id = cba.compl_claim_id
 AND clm.phase in ('10','30')
 AND cba.basis is NOT NULL;

Line: 844

SELECT 1
 FROM  ghr_complaints2 cmp
 WHERE cmp.complaint_id = p_complaint_id
 AND cmp.pcom_init is NOT NULL;

Line: 864

SELECT 1
 FROM  ghr_compl_bases cba
 WHERE cba.compl_claim_id = p_compl_claim_id;

Line: 883

SELECT 1
 FROM  ghr_compl_claims clm
 WHERE clm.complaint_id = p_complaint_id;