APPS.IGS_AD_IMP_015 SQL Statements

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

Line: 13

                         names to IGR_% naming convention.  Also deleted package variables related to inquiry.
******************************************************************/
  -- These are the package variables to hold the value of whether the particular category is included or not.
  g_application_inc               BOOLEAN := FALSE;

Line: 131

    SELECT system_source_type
    FROM   igs_pe_src_types_all
    WHERE  source_type_id = p_source_type_id
    AND    NVL(closed_ind,'N') = 'N';

Line: 176

      UPDATE igs_uc_qual_ints a
      SET    interface_run_id = p_interface_run_id,
             person_id = (SELECT person_id
                          FROM   igs_ad_interface
                          WHERE  interface_id = a.interface_id)
      WHERE  interface_id IN (SELECT interface_id
                              FROM   igs_ad_interface
                              WHERE  interface_run_id = p_interface_run_id
                              AND    status IN ('1','4'));

Line: 194

                                          p_rule             => 'N'); -- Update not yet supported

Line: 208

      UPDATE igs_ad_recruit_int a
      SET    interface_run_id = p_interface_run_id,
             person_id = (SELECT person_id
                          FROM   igs_ad_interface
                          WHERE  interface_id = a.interface_id)
      WHERE  interface_id IN (SELECT interface_id
                              FROM   igs_ad_interface
                              WHERE  interface_run_id = p_interface_run_id
                              AND    status IN ('1','4'));

Line: 271

      UPDATE igs_ad_test_int a
      SET    interface_run_id = p_interface_run_id,
             person_id = (SELECT person_id
                          FROM   igs_ad_interface
                          WHERE  interface_id = a.interface_id)
      WHERE  interface_id IN (SELECT interface_id
                              FROM   igs_ad_interface
                              WHERE  interface_run_id = p_interface_run_id
                              AND    status IN ('1','4'));

Line: 283

      UPDATE igs_ad_test_int
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 296

      UPDATE igs_ad_test_segs_int
      SET    interface_run_id = p_interface_run_id
      WHERE  interface_test_id IN (SELECT interface_test_id
                                   FROM   igs_ad_test_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','2','4'));

Line: 358

      UPDATE igs_ad_acadhis_int_all
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 367

      UPDATE igs_ad_txcpt_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,education_id)
             = (SELECT person_id,NVL(education_id,update_education_id)
                FROM   igs_ad_acadhis_int_all
                WHERE  interface_acadhis_id = a.interface_acadhis_id)
      WHERE  interface_acadhis_id IN (SELECT interface_acadhis_id
                                      FROM   igs_ad_acadhis_int_all
                                      WHERE  interface_run_id = p_interface_run_id
                                      AND    status IN ('1','4'));

Line: 380

      UPDATE igs_ad_txcpt_int
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 410

      UPDATE igs_ad_apl_int a
      SET    interface_run_id = p_interface_run_id,
             person_id = (SELECT person_id
                          FROM   igs_ad_interface
                          WHERE  interface_id = a.interface_id)
      WHERE  interface_id IN (SELECT interface_id
                              FROM   igs_ad_interface
                              WHERE  interface_run_id = p_interface_run_id
                              AND    status IN ('1','4'));

Line: 422

      UPDATE igs_ad_apl_int
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 435

      UPDATE igs_ad_ps_appl_inst_int a
      SET    interface_run_id = p_interface_run_id
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND update_adm_appl_number IS NULL
                                   AND    status IN ('1','2','4'));

Line: 445

      UPDATE igs_ad_ps_appl_inst_int
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 460

      UPDATE igs_ad_apphist_int a
      SET    (person_id,admission_appl_number)
             = (SELECT person_id,update_adm_appl_number
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  status IN ('1','2','4')
      AND    interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','2','4'));

Line: 472

      UPDATE igs_ad_insthist_int a
      SET    (person_id,admission_appl_number,nominated_course_cd,sequence_number)
             = (SELECT person_id,admission_appl_number,nominated_course_cd,update_adm_seq_number
                FROM   igs_ad_ps_appl_inst_int
                WHERE  interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
      WHERE  status IN ('1','2','4')
      AND    interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
                                           FROM   igs_ad_ps_appl_inst_int
                                           WHERE  interface_run_id = p_interface_run_id
                                           AND    status IN ('1','2','4'));

Line: 489

      UPDATE igs_ad_ps_appl_inst_int a
      SET    (person_id,admission_appl_number,admission_application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  status IN ('1','4')
      AND    interface_run_id = p_interface_run_id;

Line: 509

      UPDATE igs_ad_apphist_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 525

      UPDATE igs_ad_insthist_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,nominated_course_cd,sequence_number)
             = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number)
                FROM   igs_ad_ps_appl_inst_int
                WHERE  interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
      WHERE  interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
                                           FROM   igs_ad_ps_appl_inst_int
                                           WHERE  interface_run_id = p_interface_run_id
                                           AND    status IN ('1','4'));

Line: 544

                                    p_rule             => 'N'); -- Update not yet supported

Line: 548

                                         p_rule             => 'N'); -- Update not yet supported

Line: 562

      UPDATE igs_ad_othinst_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,admission_Application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number), admission_Application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 582

                                              p_rule             => 'N'); -- Update not yet supported

Line: 596

      UPDATE igs_ad_acadint_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number, admission_Application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 616

                                   p_rule             => 'N'); -- Update not yet supported

Line: 630

      UPDATE igs_ad_appint_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,admission_Application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 650

                                     p_rule             => 'N'); -- Update not yet supported

Line: 664

      UPDATE igs_ad_splint_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,admission_Application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 684

                                           p_rule             => 'N'); -- Update not yet supported

Line: 698

      UPDATE igs_ad_spltal_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,admission_Application_type)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 718

                                        p_rule             => 'N'); -- Update not yet supported

Line: 732

      UPDATE igs_ad_perstmt_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 751

                                              p_rule             => 'N'); -- Update not yet supported

Line: 765

      UPDATE igs_ad_fee_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number)
             = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
                FROM   igs_ad_apl_int
                WHERE  interface_appl_id = a.interface_appl_id)
      WHERE  interface_appl_id IN (SELECT interface_appl_id
                                   FROM   igs_ad_apl_int
                                   WHERE  interface_run_id = p_interface_run_id
                                   AND    status IN ('1','4'));

Line: 798

      UPDATE igs_ad_notes_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type)
             = (SELECT person_id,admission_appl_number,nominated_course_cd,
                            NVL(sequence_number,update_adm_seq_number),admission_Application_type
                FROM   igs_ad_ps_appl_inst_int
                WHERE  interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
      WHERE  interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
                                           FROM   igs_ad_ps_appl_inst_int
                                           WHERE  interface_run_id = p_interface_run_id
                                           AND    status IN ('1','4'));

Line: 819

                                          p_rule             => 'N'); -- Update not yet supported

Line: 833

      UPDATE igs_ad_unitsets_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type )
             = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number),admission_Application_type
                FROM   igs_ad_ps_appl_inst_int
                WHERE  interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
      WHERE  interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
                                           FROM   igs_ad_ps_appl_inst_int
                                           WHERE  interface_run_id = p_interface_run_id
                                           AND    status IN ('1','4'));

Line: 867

      UPDATE igs_ad_edugoal_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type )
             = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number),admission_Application_type
                FROM   igs_ad_ps_appl_inst_int
                WHERE  interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
      WHERE  interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
                                           FROM   igs_ad_ps_appl_inst_int
                                           WHERE  interface_run_id = p_interface_run_id
                                           AND    status IN ('1','4'));

Line: 887

                                                p_rule             => 'N'); -- Update not yet supported

Line: 1012

      DELETE FROM igs_ad_insthist_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1019

      DELETE FROM igs_ad_notes_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1026

      DELETE FROM igs_ad_unitsets_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1033

      DELETE FROM igs_ad_edugoal_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1040

      DELETE FROM igs_ad_ps_appl_inst_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1047

      DELETE FROM igs_ad_apphist_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1054

      DELETE FROM igs_ad_othinst_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1061

      DELETE FROM igs_ad_acadint_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1068

      DELETE FROM igs_ad_appint_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1075

      DELETE FROM igs_ad_splint_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1082

      DELETE FROM igs_ad_spltal_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1089

      DELETE FROM igs_ad_perstmt_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1096

      DELETE FROM igs_ad_fee_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1103

      DELETE FROM igs_ad_apl_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1110

      DELETE FROM igs_ad_tundt_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1115

      DELETE FROM igs_ad_trmdt_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1120

      DELETE FROM igs_ad_txcpt_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1127

      DELETE FROM igs_ad_test_segs_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1132

      DELETE FROM igs_ad_test_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1152

      DELETE FROM igs_ad_recruit_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 1159

      DELETE FROM igs_uc_qual_ints
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;