eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_014 SQL Statements

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

Line: 40


cst_insert     CONSTANT VARCHAR2(6) :=  'INSERT';

Line: 41


cst_update     CONSTANT VARCHAR2(6) :=  'UPDATE';

Line: 72


  ||                                Removed the processing for 'probability' in the call to TBH igs_ad_recruitments_pkg.insert_row, update_row
  ||                                and in the cursor for discrepancy check
  ||  (reverse chronological order - newest change first)
  */

    l_prog_label  VARCHAR2(100);

Line: 90


    SELECT  cst_insert dmlmode, rowid, in_rec.* FROM igs_ad_recruit_int in_rec
    WHERE interface_run_id = p_interface_run_id
    AND status = cst_s_val_2
    AND (  ( NVL(match_ind,'15') = '15'
    AND NOT EXISTS (SELECT 1
                         FROM igs_ad_recruitments mn_rec
                         WHERE mn_rec.person_id = in_rec.person_id))
    OR (          p_rule = cst_rule_val_R
    AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
    UNION ALL
    SELECT  cst_update dmlmode, rowid, in_rec.* FROM igs_ad_recruit_int in_rec
    WHERE interface_run_id = p_interface_run_id
    AND status = cst_s_val_2
    AND (       (p_rule = cst_rule_val_I)
    OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
    AND EXISTS (  SELECT 1
                           FROM igs_ad_recruitments mn_rec
                           WHERE mn_rec.person_id = in_rec.person_id);

Line: 111


   SELECT ROWID, ar.*
   FROM   igs_ad_recruitments ar
   WHERE  person_id  = cp_person_id;

Line: 150


     SAVEPOINT before_creatupdate;

Line: 153


     IF recruit_rec.dmlmode = cst_insert THEN
     igs_ad_recruitments_pkg.INSERT_ROW (
      X_ROWID                      =>  l_rowid,
      x_CERTAINTY_OF_CHOICE_ID     =>  recruit_rec.CERTAINTY_OF_CHOICE_ID,
      x_religion_cd                =>  recruit_rec.religion_cd,
      x_ADV_STUDIES_CLASSES        =>  recruit_rec.ADV_STUDIES_CLASSES,
      x_HONORS_CLASSES             =>  recruit_rec.HONORS_CLASSES,
      x_CLASS_SIZE                 =>  recruit_rec.CLASS_SIZE,
      x_SEC_SCHOOL_LOCATION_ID     =>  recruit_rec.SEC_SCHOOL_LOCATION_ID,
      x_PERCENT_PLAN_HIGHER_EDU    =>  recruit_rec.PERCENT_PLAN_HIGHER_EDU,
      x_RECRUITMENT_ID             =>  l_recruitments_ID,
      x_PERSON_ID                  =>  recruit_rec.PERSON_ID,
      x_SPECIAL_INTEREST_ID        =>  recruit_rec.SPECIAL_INTEREST_ID,
      x_PRIORITY                   =>  recruit_rec.PRIORITY,
      x_VIP                        =>  recruit_rec.VIP,
      x_DEACTIVATE_RECRUIT_STATUS  =>  recruit_rec.DEACTIVATE_RECRUIT_STATUS,
      x_PROGRAM_INTEREST_ID        =>  recruit_rec.PROGRAM_INTEREST_ID,
      x_INSTITUTION_SIZE_ID        =>  recruit_rec.INSTITUTION_SIZE_ID,
      x_INSTITUTION_CONTROL_ID     =>  recruit_rec.INSTITUTION_CONTROL_ID,
      x_INSTITUTION_SETTING_ID     =>  recruit_rec.INSTITUTION_SETTING_ID,
      x_INSTITUTION_LOCATION_ID    =>  recruit_rec.INSTITUTION_LOCATION_ID,
      x_SPECIAL_SERVICES_ID        =>  recruit_rec.SPECIAL_SERVICES_ID,
      x_EMPLOYMENT_ID              =>  recruit_rec.EMPLOYMENT_ID,
      x_HOUSING_ID                 =>  recruit_rec.HOUSING_ID,
      x_DEGREE_GOAL_ID             =>  recruit_rec.DEGREE_GOAL_ID,
      x_UNIT_SET_ID                =>  recruit_rec.UNIT_SET_ID,
      X_MODE                       => 'R'
     );

Line: 182


     ELSIF recruit_rec.dmlmode = cst_update THEN

     OPEN   c_null_hdlg_recru_cur(recruit_rec.person_id);

Line: 188


     igs_ad_recruitments_pkg.UPDATE_ROW(
       x_rowid                     =>  c_null_hdlg_recru_rec.rowid,
       x_CERTAINTY_OF_CHOICE_ID    =>  NVL(recruit_rec.CERTAINTY_OF_CHOICE_ID, c_null_hdlg_recru_rec.CERTAINTY_OF_CHOICE_ID),
       x_religion_cd               =>  NVL(recruit_rec.religion_cd, c_null_hdlg_recru_rec.religion_cd),
       x_ADV_STUDIES_CLASSES       =>  NVL(recruit_rec.ADV_STUDIES_CLASSES, c_null_hdlg_recru_rec.ADV_STUDIES_CLASSES),
       x_HONORS_CLASSES            =>  NVL(recruit_rec.HONORS_CLASSES, c_null_hdlg_recru_rec.HONORS_CLASSES),
       x_CLASS_SIZE                =>  NVL(recruit_rec.CLASS_SIZE, c_null_hdlg_recru_rec.CLASS_SIZE),
       x_SEC_SCHOOL_LOCATION_ID    =>  NVL(recruit_rec.SEC_SCHOOL_LOCATION_ID, c_null_hdlg_recru_rec.SEC_SCHOOL_LOCATION_ID),
       x_PERCENT_PLAN_HIGHER_EDU   =>  NVL(recruit_rec.PERCENT_PLAN_HIGHER_EDU, c_null_hdlg_recru_rec.PERCENT_PLAN_HIGHER_EDU),
       x_RECRUITMENT_ID            =>  c_null_hdlg_recru_rec.recruitment_ID,
       x_PERSON_ID                 =>  NVL(recruit_rec.PERSON_ID,c_null_hdlg_recru_rec.PERSON_ID),
       x_SPECIAL_INTEREST_ID       =>  NVL(recruit_rec.SPECIAL_INTEREST_ID, c_null_hdlg_recru_rec.SPECIAL_INTEREST_ID),
       x_PRIORITY                  =>  NVL(recruit_rec.PRIORITY, c_null_hdlg_recru_rec.PRIORITY),
       x_VIP                       =>  NVL(recruit_rec.VIP, c_null_hdlg_recru_rec.VIP),
       x_DEACTIVATE_RECRUIT_STATUS =>  NVL(recruit_rec.DEACTIVATE_RECRUIT_STATUS, c_null_hdlg_recru_rec.DEACTIVATE_RECRUIT_STATUS),
       x_PROGRAM_INTEREST_ID       =>  NVL(recruit_rec.PROGRAM_INTEREST_ID, c_null_hdlg_recru_rec.PROGRAM_INTEREST_ID),
       x_INSTITUTION_SIZE_ID       =>  NVL(recruit_rec.INSTITUTION_SIZE_ID, c_null_hdlg_recru_rec.INSTITUTION_SIZE_ID),
       x_INSTITUTION_CONTROL_ID    =>  NVL(recruit_rec.INSTITUTION_CONTROL_ID, c_null_hdlg_recru_rec.INSTITUTION_CONTROL_ID),
       x_INSTITUTION_SETTING_ID    =>  NVL(recruit_rec.INSTITUTION_SETTING_ID, c_null_hdlg_recru_rec.INSTITUTION_SETTING_ID),
       x_INSTITUTION_LOCATION_ID   =>  NVL(recruit_rec.INSTITUTION_LOCATION_ID, c_null_hdlg_recru_rec.INSTITUTION_LOCATION_ID),
       x_SPECIAL_SERVICES_ID       =>  NVL(recruit_rec.SPECIAL_SERVICES_ID, c_null_hdlg_recru_rec.SPECIAL_SERVICES_ID),
       x_EMPLOYMENT_ID             =>  NVL(recruit_rec.EMPLOYMENT_ID, c_null_hdlg_recru_rec.EMPLOYMENT_ID),
       x_HOUSING_ID                =>  NVL(recruit_rec.HOUSING_ID, c_null_hdlg_recru_rec.HOUSING_ID),
       x_DEGREE_GOAL_ID            =>  NVL(recruit_rec.DEGREE_GOAL_ID, c_null_hdlg_recru_rec.DEGREE_GOAL_ID),
       x_UNIT_SET_ID               =>  NVL(recruit_rec.UNIT_SET_ID, c_null_hdlg_recru_rec.UNIT_SET_ID),
       x_mode                      =>'R'
      );

Line: 217


     UPDATE igs_ad_recruit_int
     SET
     status = cst_s_val_1
     , match_ind = DECODE (recruit_rec.dmlmode,cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
     WHERE rowid = recruit_rec.rowid;

Line: 232


      ROLLBACK TO before_creatupdate;

Line: 237


      IF recruit_rec.dmlmode = cst_insert THEN
        l_error_code := 'E322'; -- Person Recruitment Insertion Failed

Line: 239


      ELSIF recruit_rec.dmlmode = cst_update THEN
        l_error_code := 'E014'; -- Could not update Person Recruitment

Line: 275


      UPDATE igs_ad_recruit_int
      SET
      status = cst_s_val_3
      , match_ind = DECODE ( recruit_rec.dmlmode, cst_update, DECODE ( match_ind, NULL, cst_mi_val_12, match_ind)
                                      ,cst_insert, DECODE ( p_rule, cst_rule_val_R,
                                                            DECODE ( match_ind, NULL, cst_mi_val_11, match_ind), cst_mi_val_11))
      , error_code = l_error_code
      , error_text = l_error_text
      WHERE rowid = recruit_rec.rowid;

Line: 322


     UPDATE igs_ad_recruit_int
     SET
     status = cst_s_val_3
     , error_code = cst_ec_val_E700
     , error_text = cst_et_val_E700
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;

Line: 336


     UPDATE igs_ad_recruit_int
     SET
     status = cst_s_val_1
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
                       cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);

Line: 349


     UPDATE igs_ad_recruit_int in_rec
     SET
     status = cst_s_val_1
     , match_ind = cst_mi_val_19
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND     EXISTS ( SELECT 1
                           FROM igs_ad_recruitments mn_rec
                           WHERE mn_rec.person_id = in_rec.person_id);

Line: 380


     UPDATE igs_ad_recruit_int in_rec
     SET
     status = cst_s_val_1
     , match_ind = cst_mi_val_23
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
     AND EXISTS (
       SELECT 1
          FROM igs_ad_recruitments mn_rec
          WHERE NVL(mn_rec.adv_studies_classes, -99)     = NVL(in_rec.adv_studies_classes,NVL(mn_rec.adv_studies_classes, -99) )
          AND  NVL(mn_rec.certainty_of_choice_id, -99)   = NVL(in_rec.certainty_of_choice_id, NVL(mn_rec.certainty_of_choice_id, -99))
          AND  NVL(mn_rec.class_size, -99)               = NVL(in_rec.class_size,NVL(mn_rec.class_size, -99) )
          AND  NVL(mn_rec.deactivate_recruit_status,'~') = NVL(in_rec.deactivate_recruit_status, NVL(mn_rec.deactivate_recruit_status,'~') )
          AND  NVL(mn_rec.degree_goal_id, -99)           = NVL(in_rec.degree_goal_id, NVL(mn_rec.degree_goal_id, -99))
          AND  NVL(mn_rec.employment_id, -99)            = NVL(in_rec.employment_id,  NVL(mn_rec.employment_id, -99))
          AND  NVL(mn_rec.honors_classes, -99)           = NVL(in_rec.honors_classes,NVL(mn_rec.honors_classes, -99) )
          AND  NVL(mn_rec.housing_id, -99)               = NVL(in_rec.housing_id, NVL(mn_rec.housing_id, -99))
          AND  NVL(mn_rec.institution_control_id, -99)   = NVL(in_rec.institution_control_id, NVL(mn_rec.institution_control_id, -99))
          AND  NVL(mn_rec.institution_location_id, -99)  = NVL(in_rec.institution_location_id, NVL(mn_rec.institution_location_id, -99))
          AND  NVL(mn_rec.institution_setting_id, -99)   = NVL(in_rec.institution_setting_id,NVL(mn_rec.institution_setting_id, -99) )
          AND  NVL(mn_rec.institution_size_id, -99)      = NVL(in_rec.institution_size_id, NVL(mn_rec.institution_size_id, -99))
          AND  NVL(mn_rec.percent_plan_higher_edu, -99)  = NVL(in_rec.percent_plan_higher_edu, NVL(mn_rec.percent_plan_higher_edu, -99))
          AND  NVL(mn_rec.person_id, -99)                = NVL(in_rec.person_id,NVL(mn_rec.person_id, -99) )
          AND  NVL(mn_rec.priority,'~')                  = NVL(in_rec.priority,NVL(mn_rec.priority,'~'))
          AND  NVL(mn_rec.program_interest_id, -99)      = NVL(in_rec.program_interest_id,NVL(mn_rec.program_interest_id,-99))
          AND  NVL(mn_rec.religion_cd, -99)              = NVL(in_rec.religion_cd, NVL(mn_rec.religion_cd, -99))
          AND  NVL(mn_rec.sec_school_location_id, -99)   = NVL(in_rec.sec_school_location_id,NVL(mn_rec.sec_school_location_id, -99) )
          AND  NVL(mn_rec.special_interest_id, -99)      = NVL(in_rec.special_interest_id,NVL(mn_rec.special_interest_id, -99) )
          AND  NVL(mn_rec.special_services_id, -99)      = NVL(in_rec.special_services_id,NVL(mn_rec.special_services_id, -99) )
          AND  NVL(mn_rec.unit_set_id, -99)              = NVL(in_rec.unit_set_id, NVL(mn_rec.unit_set_id, -99))
          AND  NVL(mn_rec.vip,'~')                       = NVL(in_rec.vip, NVL(mn_rec.vip,'~'))
         );

Line: 419


     UPDATE igs_ad_recruit_int in_rec
     SET
     status = cst_s_val_3
     , match_ind = cst_mi_val_20
     , dup_recruitment_id = ( SELECT recruitment_id FROM igs_ad_recruitments mn_rec
                              WHERE mn_rec.person_id = in_rec.person_id)
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
     AND EXISTS (  SELECT rowid FROM igs_ad_recruitments mn_rec
                              WHERE mn_rec.person_id = in_rec.person_id);

Line: 435


     UPDATE igs_ad_recruit_int
     SET
     status = cst_s_val_3
     , error_code = cst_ec_val_E700
     , error_text = cst_et_val_E700
     WHERE interface_run_id = p_interface_run_id
     AND status = cst_s_val_2
     AND match_ind IS NOT NULL;