[Home] [Help]
1986:
1987: DECLARE
1988: CURSOR c_ve IS
1989: SELECT venue_cd
1990: FROM IGS_GR_VENUE
1991: WHERE closed_ind = 'N';
1992: CURSOR c_es (
1993: cp_venue_cd IGS_GR_VENUE.venue_cd%TYPE) IS
1994: SELECT exam_cal_type,
1989: SELECT venue_cd
1990: FROM IGS_GR_VENUE
1991: WHERE closed_ind = 'N';
1992: CURSOR c_es (
1993: cp_venue_cd IGS_GR_VENUE.venue_cd%TYPE) IS
1994: SELECT exam_cal_type,
1995: exam_ci_sequence_number,
1996: dt_alias,
1997: dai_sequence_number,
2049: p_message_name OUT NOCOPY VARCHAR2 )
2050: IS
2051: BEGIN --assp_ins_ese_sprvsr
2052: --This module will insert a supervisor to exam sessions at a
2053: --IGS_GR_VENUE within a calendar period.
2054: --The module will be called from ASSF4630 and with two scenarios:
2055: --1. Default a supervisor to sessions within a IGS_GR_VENUE. (p_person_id not null)
2056: --2. Default supervisors to Off-campus venues. (p_person_id is null)
2057: --It will provide the ability to enforce that if warnings exist
2051: BEGIN --assp_ins_ese_sprvsr
2052: --This module will insert a supervisor to exam sessions at a
2053: --IGS_GR_VENUE within a calendar period.
2054: --The module will be called from ASSF4630 and with two scenarios:
2055: --1. Default a supervisor to sessions within a IGS_GR_VENUE. (p_person_id not null)
2056: --2. Default supervisors to Off-campus venues. (p_person_id is null)
2057: --It will provide the ability to enforce that if warnings exist
2058: --when validating the IGS_PE_PERSON, then either allocate (p_ignore_warnings = 'Y')
2059: --or do not allocate (p_ignore_warnings = 'N') the supervisor.
2068: SELECT esvv.ese_id,
2069: esvv.venue_cd,
2070: ve.exam_location_cd
2071: FROM IGS_AS_ESE_VENUE_V esvv,
2072: IGS_GR_VENUE ve
2073: WHERE esvv.exam_cal_type = p_exam_cal_type AND
2074: esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
2075: esvv.venue_cd = ve.venue_cd;
2076: CURSOR c_lr(
2073: WHERE esvv.exam_cal_type = p_exam_cal_type AND
2074: esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
2075: esvv.venue_cd = ve.venue_cd;
2076: CURSOR c_lr(
2077: cp_exam_location_cd IGS_GR_VENUE.exam_location_cd%TYPE) IS
2078: SELECT 'X'
2079: FROM IGS_AD_LOCATION_REL lr,
2080: IGS_AD_LOCATION loc,
2081: IGS_AD_LOCATION_TYPE lot
2086: CURSOR c_els (
2087: cp_venue_cd IGS_AS_ESE_VENUE_V.venue_cd%TYPE) IS
2088: SELECT els.person_id
2089: FROM IGS_AS_EXM_LOC_SPVSR els,
2090: IGS_GR_VENUE ve
2091: WHERE els.exam_location_cd = ve.exam_location_cd AND
2092: ve.venue_cd = cp_venue_cd;
2093: CURSOR c_ve IS
2094: SELECT esvv.ese_id,
2189: RETURN FALSE;
2190: END IF;
2191: -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2192: END IF;
2193: -- Validate if the limit exceeded for the session and IGS_GR_VENUE.
2194: -- Warning only.
2195: --w.r.t BUG #1956374 , Procedure assp_val_esu_ese_lmt reference is changed
2196: IF (IGS_AS_VAL_EIS.assp_val_esu_ese_lmt(
2197: p_person_id,
2229: RETURN FALSE;
2230: END IF;
2231: -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2232: END IF;
2233: -- Validate IGS_GR_VENUE is within supervisor's exam locations.
2234: -- Warning only.
2235: IF IGS_AS_VAL_ESVS.assp_val_els_venue(
2236: p_person_id,
2237: p_venue_cd,
2241: RETURN FALSE;
2242: END IF;
2243: -- At a later date, this may insert an IGS_GE_S_LOG_ENTRY for a report.
2244: END IF;
2245: -- Validate if more than one IGS_PE_PERSON in-charge at a session and IGS_GR_VENUE.
2246: -- Warning only.
2247: IF IGS_AS_VAL_EIS.assp_val_ese_inchrg(
2248: p_person_id,
2249: v_exam_cal_type,
2327: -- At a later date, a IGS_GE_S_LOG_ENTRY may be created to indicate successful
2328: -- insertion.
2329: ELSE
2330: -- Insert IGS_PE_PERSON into the IGS_AS_EXM_INS_SPVSR table. Put them into all exams
2331: -- for the IGS_GR_VENUE and session.
2332: FOR v_ei_rec IN c_ei LOOP
2333: BEGIN
2334: IGS_AS_EXM_INS_SPVSR_PKG.INSERT_ROW(
2335: X_ROWID => x_rowid,
2377: --Perform processing to default the supervisor's
2378: --to OFF-CAMPUS exam venues at sessions within the exam
2379: --period (p_exam_cal_type/p_exam_ci_sequence_number).
2380: --Select all exam session venues for the exam period
2381: --(IGS_AS_ESE_VENUE_V) where the IGS_GR_VENUE is not linked to a campus.
2382: FOR v_esvv_rec IN c_esvv LOOP
2383: OPEN c_lr(
2384: v_esvv_rec.exam_location_cd);
2385: FETCH c_lr INTO v_lr_exists;
2385: FETCH c_lr INTO v_lr_exists;
2386: IF (c_lr%NOTFOUND) THEN
2387: CLOSE c_lr;
2388: --The exam IGS_AD_LOCATION is an off campus IGS_AD_LOCATION
2389: --For each IGS_GR_VENUE, select the supervisors for which
2390: -- the exam venues are within their nominated exam locations.
2391: FOR v_els_rec IN c_els(
2392: v_esvv_rec.venue_cd) LOOP
2393: --Call a local function to validate the IGS_PE_PERSON being allocated
2415: CLOSE c_lr;
2416: END IF;
2417: END LOOP;
2418: ELSE
2419: --p_person_id is not null so default the IGS_PE_PERSON for all sessions at the IGS_GR_VENUE.
2420: --Select all exam sessions for p_venue_cd within the exam period.
2421: FOR v_ve_rec IN c_ve LOOP
2422: --Call a local function to validate the IGS_PE_PERSON being allocated.
2423: IF (asspl_val_sprvsr(