APPS.IGS_AV_VAL_ASU SQL Statements

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

Line: 48

	SELECT ihp.oss_org_unit_cd exemption_institution_cd
	  FROM igs_pe_hz_parties ihp
	WHERE ihp.inst_org_ind = 'I'
	AND ihp.oss_org_unit_cd = cp_exempt_inst
	UNION ALL
	SELECT lk.lookup_code exemption_institution_cd
	FROM igs_lookup_values lk
	WHERE lk.lookup_type = 'OR_INSTITUTION_ADVSTEX'
	 AND lk.enabled_flag = 'Y'
	 AND lk.lookup_code = cp_exempt_inst;

Line: 103

        SELECT sca.version_number,
               sca.course_attempt_status
        FROM   igs_en_stdnt_ps_att sca
        WHERE  sca.person_id = cp_person_id
        AND    sca.course_cd = cp_course_cd;

Line: 168

        IF (v_course_attempt_status IN('UNCONFIRM', 'LAPSED', 'DELETED')) THEN
          p_message_name := 'IGS_AV_GRANTED_STUD_ENR';

Line: 299

        SELECT closed_ind
        FROM   igs_lookups_view
        WHERE  lookup_type = 'ADV_STND_RECOGNITION_TYPE'
        AND    lookup_code = p_recognition_type;

Line: 531

        SELECT NVL (cv.external_adv_stnd_limit, 0) external_adv_stnd_limit,
               NVL (cv.internal_adv_stnd_limit, 0) internal_adv_stnd_limit,
               NVL (cv.credit_points_required, 0) credit_points_required
        FROM   igs_ps_ver cv
        WHERE  cv.course_cd = cp_course_cd
        AND    cv.version_number = cp_version_number;

Line: 539

        SELECT ins.local_institution_ind
        FROM   igs_or_institution ins
        WHERE  ins.institution_cd = cp_ins_cd;

Line: 548

        SELECT /* asu.credit_percentage, */
               asu.exemption_institution_cd,
               asu.s_adv_stnd_granting_status,
               asu.unit_cd,
               asu.version_number,
               -- asu.prev_unit_cd,
               asu.unit_details_id,
               -- asu.test_segment_id
               asu.tst_rslt_dtls_id
        FROM   igs_av_stnd_unit asu
        WHERE  asu.person_id = cp_person_id
        AND    asu.as_course_cd = cp_course_cd
        AND    asu.as_version_number = cp_version_number
    	AND    asu.s_adv_stnd_granting_status IN (cst_approved, cst_granted)
        AND    asu.s_adv_stnd_recognition_type = cst_credit;

Line: 570

        SELECT NVL (asul.credit_points, 0) credit_points,
               asul.exemption_institution_cd,
               asul.unit_level,
               asul.s_adv_stnd_granting_status
        FROM   igs_av_stnd_unit_lvl asul
        WHERE  asul.person_id = cp_person_id
        AND    asul.as_course_cd = cp_course_cd
        AND    asul.as_version_number = cp_version_num
	AND    asul.s_adv_stnd_granting_status IN (cst_approved, cst_granted);

Line: 987

        SELECT daiv.alias_val
        FROM   igs_ca_da_inst_v daiv,
               igs_ge_s_gen_cal_con sgcc
        WHERE  daiv.cal_type = cp_cal_type
        AND    daiv.ci_sequence_number = cp_ci_seq_num
        AND    daiv.dt_alias = sgcc.census_dt_alias
        AND    sgcc.s_control_num = 1;

Line: 1034

        SELECT sca.version_number,
               sca.course_attempt_status
        FROM   igs_en_stdnt_ps_att sca
        WHERE  sca.person_id = cp_person_id
        AND    sca.course_cd = cp_course_cd;

Line: 1098

        IF (v_course_attempt_status IN('UNCONFIRM', 'LAPSED', 'DELETED')) THEN
          p_message_name := 'IGS_AV_GRANTED_STUD_ENR';

Line: 1139

      SELECT asu.achievable_credit_points advance_standing_cp,
             asu.exemption_institution_cd,
             /* asu.credit_percentage, */
             NVL (puv.achievable_credit_points, puv.enrolled_credit_points) enrolled_cp
      FROM   igs_av_stnd_unit asu,
             igs_ps_unit_ver puv
      WHERE  asu.person_id = p_person_id
  AND        asu.as_course_cd = p_course_cd
  AND        asu.as_version_number = p_version_number
  AND        asu.unit_cd = p_unit_cd
  AND        asu.version_number = p_unit_version
  AND
  --**            asu.prev_unit_cd                = p_previous_unit AND
             asu.unit_details_id = p_unit_details_id
  AND        asu.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED')
  AND        asu.s_adv_stnd_recognition_type = 'CREDIT'
  AND        puv.unit_cd = asu.unit_cd
  AND        puv.version_number = asu.version_number;

Line: 1159

      SELECT asu.achievable_credit_points advance_standing_cp,
             asu.exemption_institution_cd,
             /* asu.credit_percentage, */
             NVL (puv.achievable_credit_points, puv.enrolled_credit_points) enrolled_cp
      FROM   igs_av_stnd_unit asu,
             igs_ps_unit_ver puv
      WHERE  asu.person_id = p_person_id
  AND        asu.as_course_cd = p_course_cd
  AND        asu.as_version_number = p_version_number
  AND        asu.unit_cd = p_unit_cd
  AND        asu.version_number = p_unit_version
  AND
  --            asu.test_Segment_id             = p_test_segment AND
             asu.tst_rslt_dtls_id = p_tst_rslt_dtls_id
  AND        asu.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED')
  AND        asu.s_adv_stnd_recognition_type = 'CREDIT'
  AND        puv.unit_cd = asu.unit_cd
  AND        puv.version_number = asu.version_number;

Line: 1237

      SELECT NVL (achievable_credit_points, enrolled_credit_points) enrolled_cp
      FROM   igs_ps_unit_ver
      WHERE  unit_cd = p_unit_cd
  AND        (version_number = p_version_number
              OR p_version_number IS NULL
             );

Line: 1245

      SELECT DECODE (
               p_s_adv_stnd_granting_status,
               s_adv_stnd_granting_status, NVL (asu.achievable_credit_points, 0),
               'BOTH', NVL (asu.achievable_credit_points, 0),
               0
             ) advance_standing_credits,
             asu.unit_cd,
             asu.version_number,
             s_adv_stnd_granting_status
      FROM   igs_av_stnd_unit asu
      WHERE  asu.person_id = p_person_id
  AND        asu.as_course_cd = p_asu_course_cd
  AND        asu.as_version_number = p_asu_version_number
  AND        asu.unit_cd = p_unit_cd
  AND        (asu.version_number = p_version_number
              OR p_version_number IS NULL
             )
  AND        asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
  AND        asu.s_adv_stnd_recognition_type = 'CREDIT'
  AND        (p_effective_dt IS NULL
              OR asu.granted_dt <= TRUNC (p_effective_dt)
             );

Line: 1269

      SELECT s_adv_stnd_granting_status
      FROM   igs_av_stnd_unit asu
      WHERE  asu.person_id = p_person_id
             AND asu.as_course_cd = p_asu_course_cd
             AND as_version_number = p_asu_version_number
             AND asu.unit_cd = p_unit_cd
             AND (asu.version_number = p_version_number
                  OR p_version_number IS NULL
                 )
             AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
             AND asu.s_adv_stnd_recognition_type = 'CREDIT'
             AND (p_effective_dt IS NULL
	          OR asu.granted_dt <= TRUNC (p_effective_dt));

Line: 1363

      SELECT NVL (achievable_credit_points, enrolled_credit_points) enrolled_cp
      FROM   igs_ps_unit_ver
      WHERE  unit_cd = p_unit_cd
  AND        (version_number = p_version_number
              OR p_version_number IS NULL
             );

Line: 1371

      SELECT DECODE (
               p_s_adv_stnd_granting_status,
               s_adv_stnd_granting_status, NVL (asu.achievable_credit_points, 0),
               'BOTH', NVL (asu.achievable_credit_points, 0),
               0
             ) advance_standing_credits,
             asu.unit_cd,
             asu.version_number,
             s_adv_stnd_granting_status
      FROM   igs_av_stnd_unit asu
      WHERE  asu.person_id = p_person_id
  AND        asu.as_course_cd = p_asu_course_cd
  AND        asu.as_version_number = p_asu_version_number
  AND        asu.unit_cd = p_unit_cd
  AND        (asu.version_number = p_version_number
              OR p_version_number IS NULL
             )
  AND        asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
  AND        asu.s_adv_stnd_recognition_type = 'CREDIT'
  AND        (p_effective_dt IS NULL
              OR asu.granted_dt <= TRUNC (p_effective_dt)
             );

Line: 1395

      SELECT s_adv_stnd_granting_status
      FROM   igs_av_stnd_unit asu
      WHERE  asu.person_id = p_person_id
             AND asu.as_course_cd = p_asu_course_cd
             AND as_version_number = p_asu_version_number
             AND asu.unit_cd = p_unit_cd
             AND (asu.version_number = p_version_number
                  OR p_version_number IS NULL
                 )
             AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
             AND asu.s_adv_stnd_recognition_type = 'CREDIT'
             AND p_effective_dt IS NULL
  OR         asu.granted_dt <= TRUNC (p_effective_dt)
             /* AND credit_percentage = 100 */;