APPS.IGF_AP_PROFILE_GEN_PKG SQL Statements

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

Line: 46

  ||          1. Insert a New FA BASE Record if it doesn't exist.
  ||          2. Create a record in PROFILE matched table.
  ||          3. Create a record in FNAR table.
  ||          4. Update PROFILE interface record status to "MATCHED".
  ||          5. Deletes corresponding records in match details and person match table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who           When            What
  ||  ridas         14-Feb-2006     Bug #5021084. Removed trunc function from cursor C_SSN.
  */

  -- Cursor to get cal_type and sequence_number from batch_year
  CURSOR cur_get_cal_sequence (cp_batch_year igf_ap_batch_aw_map.batch_year%TYPE) IS
    SELECT ibm.ci_cal_type, ibm.ci_sequence_number
      FROM IGF_AP_BATCH_AW_MAP ibm
     WHERE ibm.batch_year = cp_batch_year;

Line: 74

    SELECT api_person_id,
           api_person_id_uf,
           end_dt
      FROM igs_pe_alt_pers_id
     WHERE pe_person_id = cp_person_id
       AND person_id_type LIKE 'SSN'
       AND SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE);

Line: 154

  ||          6. Updates PROFILE interface record status to "MATCHED".
  ||          7. Deletes corresponding records in match details and person match table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  */

  lv_message2   VARCHAR2(4000);

Line: 191

PROCEDURE delete_person_match ( p_css_id   IN    NUMBER)
IS
  /*
  ||  Created By : ugummall
  ||  Created On : 05-Aug-2004
  ||  Purpose : This Procedure does the following tasks.
  ||          1. Deletes records in match details table (child records)
  ||          2. Deletes the person match record (parent record)
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  */

  -- Cursor to fetch apm_id from person match table of an profile interface record.
  CURSOR cur_get_person_match ( cp_css_id  igf_ap_person_match_all.css_id%TYPE) IS
    SELECT ROWID row_id, apm_id
      FROM IGF_AP_PERSON_MATCH_ALL permatch
     WHERE permatch.css_id = cp_css_id;

Line: 212

    SELECT ROWID row_id
      FROM IGF_AP_MATCH_DETAILS matchdtls
     WHERE matchdtls.apm_id = cp_apm_id;

Line: 230

       igf_ap_match_details_pkg.delete_row(rec_get_match_detail.row_id);

Line: 233

    igf_ap_person_match_pkg.delete_row(rec_get_person_match.row_id);

Line: 242

      fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_person_match.exception','The exception is : ' || SQLERRM );

Line: 245

    fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_person_match');

Line: 248

END delete_person_match;

Line: 250

PROCEDURE delete_interface_record ( p_css_id       IN          NUMBER,
                                    x_return_status     OUT NOCOPY  VARCHAR2
                                  )
IS
  /*
  ||  Created By : ugummall
  ||  Created On : 05-Aug-2004
  ||  Purpose : This Procedure does the following tasks.
  ||          1. Deletes the record in PROFILE interface table.
  ||          2. Deletes the corresponding match detail records.
  ||          3. Deletes the corresponding record in person match table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  */

  -- Cursor to get rowid of the PROFILE interface record.
  CURSOR cur_get_rowid_interface ( cp_css_id igf_ap_css_interface_all.css_id%TYPE) IS
    SELECT ROWID row_id
      FROM IGF_AP_CSS_INTERFACE_ALL intface
     WHERE intface.css_id = cp_css_id;

Line: 287

  igf_ap_css_interface_pkg.delete_row(rec_get_rowid_interface.row_id);

Line: 290

  delete_person_match(p_css_id => p_css_id);

Line: 297

      fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_interface_record.exception','The exception is : ' || SQLERRM );

Line: 300

    fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_interface_record');

Line: 304

END delete_interface_record;

Line: 306

PROCEDURE delete_int_records  ( p_css_ids IN  VARCHAR2 )
IS
  /*
  ||  Created By : ugummall
  ||  Created On : 05-Aug-2004
  ||  Purpose : This Procedure does the following tasks.
  ||          1. Deletes the record in PROFILE interface table.
  ||          2. Deletes the corresponding match detail records.
  ||          3. Deletes the corresponding record in person match table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  */

  l_del_css_id  VARCHAR2(10);

Line: 339

    delete_interface_record( p_css_id => l_del_css_id, x_return_status => x_return_status);

Line: 349

      fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_profile_gen_pkg.delete_int_records.exception','The exception is : ' || SQLERRM );

Line: 352

    fnd_message.set_token('NAME','igf_ap_profile_gen_pkg.delete_int_records');

Line: 355

END delete_int_records;

Line: 367

  ||          2. Update PROFILE interface record status to "MATCHED".
  ||          3. Deletes corresponding records in match details and person match table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  */


BEGIN

  fnd_msg_pub.initialize;