APPS.IGS_AD_ASSIGN_EVAL_AI_PKG SQL Statements

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

Line: 37

    SELECT apl.person_id,
           apl.admission_appl_number,
           apl.nominated_course_cd,
           apl.sequence_number,
      	   arp.appl_rev_profile_id,
      	   arp.appl_revprof_revgr_id
    FROM 	 igs_ad_ps_appl_inst_all apl,
           igs_ad_appl_arp arp
    WHERE  apl.person_id = arp.person_id
    AND    apl.admission_appl_number = arp.admission_appl_number
    AND    apl.nominated_course_cd = arp.nominated_course_cd
    AND 	 apl.sequence_number = arp.sequence_number
    AND    arp.appl_revprof_revgr_id IS NOT NULL
    AND EXISTS (SELECT '1'
                FROM   igs_ad_ou_stat ou
                WHERE  ou.s_adm_outcome_status = 'PENDING'
                AND    apl.adm_outcome_status = ou.adm_outcome_status )
    AND NOT EXISTS (SELECT  '1'
                    FROM igs_ad_appl_eval aev
                    WHERE aev.person_id = apl.person_id
                    AND aev.admission_appl_number =  apl.admission_appl_number
                    AND aev.nominated_course_cd = apl.nominated_course_cd
                    AND aev.sequence_number = apl.sequence_number );

Line: 63

    SELECT apl.person_id,
           apl.admission_appl_number,
           apl.nominated_course_cd,
           apl.sequence_number,
      	   arp.appl_rev_profile_id,
      	   arp.appl_revprof_revgr_id
    FROM 	 igs_ad_ps_appl_inst_all apl,
           igs_ad_appl_arp arp
    WHERE  apl.person_id = arp.person_id
    AND    apl.admission_appl_number = arp.admission_appl_number
    AND    apl.nominated_course_cd = arp.nominated_course_cd
    AND 	 apl.sequence_number = arp.sequence_number
    AND    arp.appl_revprof_revgr_id IS NOT NULL
    AND EXISTS (SELECT '1'
                FROM   igs_ad_ou_stat ou
                WHERE  ou.s_adm_outcome_status = 'PENDING'
                AND    apl.adm_outcome_status = ou.adm_outcome_status )
    AND NOT EXISTS (SELECT  '1'
                    FROM igs_ad_appl_eval aev
                    WHERE aev.person_id = apl.person_id
                    AND aev.admission_appl_number =  apl.admission_appl_number
                    AND aev.nominated_course_cd = apl.nominated_course_cd
                    AND aev.sequence_number = apl.sequence_number )
    AND    arp.appl_rev_profile_id = cp_review_profile_id;

Line: 90

    SELECT apl.person_id,
           apl.admission_appl_number,
           apl.nominated_course_cd,
           apl.sequence_number,
      	   arp.appl_rev_profile_id,
      	   arp.appl_revprof_revgr_id
    FROM 	 igs_ad_ps_appl_inst_all apl,
           igs_ad_appl_arp arp
    WHERE  apl.person_id = arp.person_id
    AND    apl.admission_appl_number = arp.admission_appl_number
    AND    apl.nominated_course_cd = arp.nominated_course_cd
    AND 	 apl.sequence_number = arp.sequence_number
    AND    arp.appl_revprof_revgr_id IS NOT NULL
    AND EXISTS (SELECT '1'
                FROM   igs_ad_ou_stat ou
                WHERE  ou.s_adm_outcome_status = 'PENDING'
                AND    apl.adm_outcome_status = ou.adm_outcome_status )
    AND NOT EXISTS (SELECT  '1'
                    FROM igs_ad_appl_eval aev
                    WHERE aev.person_id = apl.person_id
                    AND aev.admission_appl_number =  apl.admission_appl_number
                    AND aev.nominated_course_cd = apl.nominated_course_cd
                    AND aev.sequence_number = apl.sequence_number )
    AND    arp.appl_revprof_revgr_id = cp_appl_revprof_revgr_id;

Line: 121

    SELECT review_profile_name
    FROM igs_ad_apl_rev_prf
    WHERE appl_rev_profile_id = cp_appl_rev_profile_id;

Line: 128

    SELECT revprof_revgr_cd
    FROM igs_ad_apl_rprf_rgr
    WHERE appl_revprof_revgr_id = cp_review_group_code;

Line: 159

       FND_MESSAGE.SET_NAME('IGS','IGS_FI_SELECT_COMBN_NORECORDS');

Line: 308

    SELECT   person_id,
             person_number,
  	         evaluation_sequence
    FROM     igs_ad_rvgr_evaltr ev
    WHERE    appl_revprof_revgr_id  = cp_appl_revprof_revgr_id
    ORDER BY evaluation_sequence;

Line: 318

    SELECT  sequential_concurrent_ind
    FROM  igs_ad_apl_rev_prf_all
    WHERE APPL_REV_PROFILE_ID = cp_appl_rev_profile_id;

Line: 325

  SELECT *
  FROM   igs_ad_apl_rvpf_rsl arr
  WHERE  arr.appl_rev_profile_id = cp_appl_rev_profile_id;

Line: 333

  SELECT Igs_ad_appl_eval_s.nextval
  FROM DUAL;

Line: 338

    SELECT review_profile_name
    FROM igs_ad_apl_rev_prf
    WHERE appl_rev_profile_id = cp_appl_rev_profile_id;

Line: 350

    SELECT *
    FROM igs_ad_appl_eval_v
    WHERE person_id = cp_person_id
    AND admission_appl_number = cp_admission_appl_number
    AND nominated_course_cd = cp_nominated_course_cd
    AND sequence_number = cp_sequence_number;

Line: 364

    SELECT (max(evaluation_sequence))
    FROM igs_ad_appl_eval
    WHERE person_id = cp_person_id
    AND admission_appl_number = cp_admission_appl_number
    AND nominated_course_cd = cp_nominated_course_cd
    AND sequence_number = cp_sequence_number;

Line: 483

        Igs_ad_appl_eval_pkg.insert_row(
          x_rowid => lv_rowid,
          x_appl_eval_id => l_igs_ad_appl_eval_s,
          x_person_id => p_person_id,
          x_admission_appl_number => p_admission_appl_number,
          x_nominated_course_cd => p_nominated_course_cd,
          x_sequence_number=> p_sequence_number,
          x_evaluator_id => l_appl_rev_prof_evaluator_rec.person_id,
          x_assign_type => 'M',
          x_assign_date => SYSDATE,
          x_evaluation_date => NULL,
          x_rating_type_id => arp_rate_scale_rec.rating_type_id,
          x_rating_values_id => NULL,
          x_rating_notes => NULL,
	        x_evaluation_sequence => l_sequence_number,
          x_rating_scale_id => arp_rate_scale_rec.rating_scale_id,
	        x_closed_ind => 'N'
	      );

Line: 610

         SELECT IGS_AD_WF_EVAL_S.NEXTVAL
         FROM dual;

Line: 641

 	l_parameter_list_t.delete;

Line: 670

	Select count(rowid)
	Into l_no_of_evaluators
	From igs_ad_appl_eval
	Where person_id = p_person_id
	And Admission_appl_number = p_admission_appl_number
	And Nominated_course_cd = p_nominated_course_cd
	And sequence_number = p_sequence_number;

Line: 678

	Select count(rowid)
	Into l_count
	From igs_ad_appl_eval
	Where person_id = p_person_id
	And Admission_appl_number = p_admission_appl_number
	And Nominated_course_cd = p_nominated_course_cd
	And sequence_number = p_sequence_number
	And rating_type_id is not null
	And rating_values_id is not null
	And evaluation_date is not null
	And rating_scale_id is not null;

Line: 703

	Select count(rowid)
	Into l_no_of_evaluators
	From igs_ad_appl_eval
	Where person_id = p_person_id
	And Admission_appl_number = p_admission_appl_number
	And Nominated_course_cd = p_nominated_course_cd
	And sequence_number = p_sequence_number
	AND EVALUATOR_ID = p_faculty_id;

Line: 712

	Select count(rowid)
	Into l_count
	From igs_ad_appl_eval
	Where person_id = p_person_id
	And Admission_appl_number = p_admission_appl_number
	And Nominated_course_cd = p_nominated_course_cd
	And sequence_number = p_sequence_number
	And rating_type_id is not null
	And rating_values_id is not null
	And evaluation_date is not null
	And rating_scale_id is not null
	And evaluator_id = p_faculty_id;

Line: 730

	Select count(rowid)
	Into l_prev_count
	From igs_ad_appl_eval
	Where person_id = p_person_id
	And Admission_appl_number = p_admission_appl_number
	And Nominated_course_cd = p_nominated_course_cd
	And sequence_number = p_sequence_number
	And evaluation_sequence = l_prev_seq_number
	And rating_values_id is  null
	And evaluation_date is null;

Line: 847

        SELECT
                distinct sequential_concurrent_ind
        FROM
                igs_ad_apl_rev_prf_all
        WHERE
                appl_rev_profile_id = (select appl_rev_profile_id
		                                         from igs_ad_appl_arp
							 where person_id = cp_person_id
							 and admission_appl_number = cp_admission_appl_number
							 and nominated_course_cd = cp_nominated_course_cd
							 and sequence_number = cp_sequence_number);

Line: 864

        SELECT
                (max(evaluation_sequence)+1)
        FROM
                igs_ad_appl_eval
        WHERE
                person_id = cp_person_id AND
                admission_appl_number = cp_adm_apl_num AND
                nominated_course_cd = cp_nom_crs_cd AND
                sequence_number = cp_seq_number;

Line: 880

      SELECT *
      FROM igs_ad_appl_eval
      WHERE person_id = cp_person_id
      AND admission_appl_number = cp_adm_apl_num
      AND nominated_course_cd = cp_nom_crs_cd
      AND sequence_number = cp_seq_number
      AND evaluator_id = cp_evaluator_id;