APPS.IGS_AD_IMP_028 SQL Statements

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

Line: 42

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

Line: 43

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

Line: 96

 UPDATE IGS_UC_QUAL_INTS  qints
 SET error_code = 'E683'
        ,error_text =  igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E683', 8405)
        , status ='3'
 WHERE STATUS = '2'
 AND interface_run_id = p_interface_run_id
 AND  EXISTS ( SELECT 1  FROM IGS_UC_QUAL_DETS
                 WHERE  person_id = qints.person_id);

Line: 107

 UPDATE IGS_UC_QUAL_INTS  qints
 SET error_code = 'E684'
        ,error_text =  igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E684', 8405)
        , status ='3'
 WHERE STATUS = '2'
 AND interface_run_id = p_interface_run_id
 AND EXISTS (SELECT 1 FROM IGS_UC_QUAL_INTS  qints2
              WHERE   qints2.person_id = qints.person_id
	  AND qints2.exam_level = qints.exam_level
	  AND NVL(qints2.subject_code, '-1')  = NVL(qints.subject_code, '-1')
          AND NVL(qints2.awarding_body,'-1') =  NVL(qints.awarding_body,'-1')
          AND NVL(qints2.year,1700)  = NVL(qints.year,1700)
          AND NVL(qints2.sitting,'-1') = NVL(qints.sitting,'-1')
          AND NVL( qints2.approved_result,'-1') = NVL( qints.approved_result,'-1')
          AND    qints2.rowid <> qints.rowid
          AND    qints2.interface_run_id = p_interface_run_id
         AND    qints2.status ='2' );

Line: 126

 UPDATE IGS_UC_QUAL_INTS  qints
 SET error_code = 'E684'
        ,error_text =  igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E684', 8405)
        , status ='3'
 WHERE STATUS = '2'
 AND interface_run_id = p_interface_run_id
 AND EXISTS (SELECT 1 FROM IGS_UC_QUAL_INTS  qints2
       WHERE STATUS = '3'
       AND    qints2.person_id = qints.person_id
        AND    qints2.interface_run_id = p_interface_run_id) ;

Line: 160

           igs_uc_qual_dets_pkg.insert_row(
		x_rowid                => l_rowid,
		 x_qual_dets_id    => l_qual_dets_id,
		x_person_id            => uc_qual_rec.person_id,
		x_exam_level       => uc_qual_rec.exam_level,
		x_subject_code      => uc_qual_rec.subject_code,
		x_year                 => uc_qual_rec.year,
		x_sitting              => uc_qual_rec.sitting,
		x_awarding_body        => uc_qual_rec.awarding_body,
		x_grading_schema_cd    => uc_qual_rec.grading_schema_cd,
		x_version_number       => uc_qual_rec.version_number,
		x_predicted_result     => uc_qual_rec.predicted_result,
		x_approved_result      => uc_qual_rec.approved_result,
		x_claimed_result       => uc_qual_rec.claimed_result,
		x_ucas_tariff          => uc_qual_rec.ucas_tariff,
		x_imported_flag        => uc_qual_rec.imported_flag,
		x_imported_date        => TRUNC(uc_qual_rec.imported_date),
		x_mode                 => 'R');

Line: 178

                UPDATE igs_uc_qual_ints
		SET status = cst_s_val_1,
                       error_code = cst_ec_val_NULL
		WHERE   interface_qual_id = uc_qual_rec.interface_qual_id;

Line: 189

               UPDATE igs_uc_qual_ints
 		SET status = cst_s_val_3,
                       error_code = l_error_code,
                      error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
                WHERE  interface_qual_id = uc_qual_rec.interface_qual_id;

Line: 199

              UPDATE igs_uc_qual_ints
		SET status = cst_s_val_3
                       , error_code = l_error_code
                       , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
               WHERE  person_id = uc_qual_rec.person_id
               AND status  = cst_s_val_2
               AND interface_qual_id <> uc_qual_rec.interface_qual_id;

Line: 246

               UPDATE igs_uc_qual_ints
 		SET status = cst_s_val_3,  error_code = l_error_code, error_text = l_error_text
                WHERE  interface_qual_id = uc_qual_rec.interface_qual_id;

Line: 251

              UPDATE igs_uc_qual_ints
		SET status = cst_s_val_3,
                       error_code = l_error_code,
                       error_text  =  igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405)
               WHERE  person_id = uc_qual_rec.person_id
               AND status  =cst_s_val_2
               AND interface_qual_id <> uc_qual_rec.interface_qual_id;