APPS.IGS_PE_GEN_001 SQL Statements

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

Line: 44

    SELECT  max(dg.lvl) Max_Level, lvl.data_group_id, lvl_description
    FROM IGS_PE_PRIV_LEVEL lvl, IGS_PE_DATA_GROUPS DG
    WHERE lvl.person_id =  lnpersonid
    AND TRUNC(SYSDATE) BETWEEN lvl.start_date AND NVL(lvl.end_date,TRUNC(SYSDATE))
    and lvl.DATA_GROUP_ID = DG.DATA_GROUP_ID
    GROUP BY lvl.data_group_id, lvl_description
    ORDER BY 1 desc;

Line: 53

    SELECT meaning
	FROM igs_lookup_values
    WHERE  lookup_type = cp_lookup_type AND lookup_code = cp_lookup_code;

Line: 116

    SELECT DECODE(pp.date_of_death, NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_flag,
           igs_en_gen_003.enrp_get_encmbrd_ind(p.party_id)  encumbered_ind,
       p.status status
    FROM   hz_parties p,
           igs_pe_hz_parties pd,
           hz_person_profiles pp
    WHERE  p.party_id = pp.party_id AND
           p.party_id = pd.party_id (+) AND
           sysdate between  pp.effective_start_date AND
                        NVL(pp.effective_end_date,sysdate) AND
       p.party_id = cp_person_id;

Line: 171

  SELECT person_party_id
  FROM   fnd_user
  WHERE  user_id = p_fnd_user_id AND
  SYSDATE between start_date AND NVL(end_date,SYSDATE);

Line: 178

  SELECT person_number,full_name
  FROM   igs_pe_person_base_v
  WHERE  person_id = cp_person_id;

Line: 242

  SELECT auth_resp_id
  FROM   igs_pe_pers_encumb
  WHERE  person_id  = p_person_id AND
         encumbrance_type = p_encumbrance_type AND
         start_dt = p_start_dt;

Line: 330

  SELECT ROWID,pen.*
  FROM   igs_pe_pers_encumb pen
  WHERE  pen.person_id  = p_person_id AND
         pen.encumbrance_type = p_encumbrance_type AND
     pen.start_dt = p_start_dt;

Line: 401

        igs_pe_pers_encumb_pkg.update_row(
                                x_rowid        => hold_rec.rowid   ,
                                x_person_id    => hold_rec.person_id,
                                x_encumbrance_type => hold_rec.encumbrance_type   ,
                                x_start_dt     => hold_rec.start_dt,
                                x_expiry_dt    => p_expiry_dt   ,
                                x_authorising_person_id => hold_rec.authorising_person_id ,
                                x_comments     => NVL(p_comments, hold_rec.comments) ,
                                x_spo_course_cd => hold_rec.spo_course_cd,
                                x_spo_sequence_number => hold_rec.spo_sequence_number,
                                x_auth_resp_id => hold_rec.auth_resp_id,
                                x_external_reference => hold_rec.external_reference,
                                x_mode         =>  'R' );

Line: 440

SELECT residency_status_cd
FROM igs_pe_res_dtls_all
WHERE person_id      = cp_person_id AND
residency_class_cd   = cp_residency_class AND
cal_type             = cp_cal_type AND
sequence_number      = cp_sequence_number;

Line: 452

SELECT cal.start_dt, cal.end_dt FROM igs_ca_inst cal
WHERE  cal.cal_type             = cp_cal_type AND
       cal.sequence_number      = cp_sequence_number;

Line: 461

SELECT res.residency_status_cd, ci.start_dt
FROM igs_pe_res_dtls_all res,IGS_CA_INST_ALL ci
WHERE  res.person_id  = cp_person_id AND
res.residency_class_cd = cp_residency_class AND
res.start_dt <= cp_start_dt AND
res.CAL_TYPE = CI.CAL_TYPE AND
res.SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER
ORDER BY res.start_dt desc;

Line: 560

    SELECT  max(dg.lvl) Max_Level, lvl.DATA_GROUP_ID , dg.lvl_description
    FROM IGS_PE_PRIV_LEVEL lvl, IGS_PE_DATA_GROUPS DG
    WHERE lvl.person_id =  lnpersonid
    AND TRUNC(SYSDATE) BETWEEN lvl.start_date AND NVL(lvl.end_date,TRUNC(SYSDATE))
    and lvl.DATA_GROUP_ID = DG.DATA_GROUP_ID
    GROUP BY lvl.data_group_id ,dg.lvl_description
    ORDER BY 1 desc;

Line: 615

                SELECT  count(*)
                FROM    IGS_PE_PERS_ENCUMB
                WHERE   person_id = cp_person_id AND
                        (cp_sysdate BETWEEN start_dt AND (expiry_dt - 1) OR
                        (expiry_dt IS NULL AND start_dt <= cp_sysdate));