[Home] [Help]
2468: -- Letter
2469: -- admp_get_aal_sent_dt
2470: DECLARE
2471: cst_spl_seqnum CONSTANT VARCHAR2(10) := 'SPL_SEQNUM';
2472: v_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE;
2473: v_issue_dt IGS_CO_OU_CO_REF.issue_dt%TYPE;
2474: CURSOR c_aal IS
2475: SELECT spl_sequence_number
2476: FROM IGS_AD_APPL_LTR
2472: v_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE;
2473: v_issue_dt IGS_CO_OU_CO_REF.issue_dt%TYPE;
2474: CURSOR c_aal IS
2475: SELECT spl_sequence_number
2476: FROM IGS_AD_APPL_LTR
2477: WHERE person_id = p_person_id AND
2478: admission_appl_number = p_admission_appl_number AND
2479: correspondence_type = p_correspondence_type AND
2480: sequence_number = p_sequence_number;
2478: admission_appl_number = p_admission_appl_number AND
2479: correspondence_type = p_correspondence_type AND
2480: sequence_number = p_sequence_number;
2481: CURSOR c_ocr (
2482: p_person_id IGS_AD_APPL_LTR.person_id%TYPE,
2483: p_correspondence_type IGS_AD_APPL_LTR.correspondence_type%TYPE,
2484: cp_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE) IS
2485: SELECT issue_dt
2486: FROM IGS_CO_OU_CO_REF
2479: correspondence_type = p_correspondence_type AND
2480: sequence_number = p_sequence_number;
2481: CURSOR c_ocr (
2482: p_person_id IGS_AD_APPL_LTR.person_id%TYPE,
2483: p_correspondence_type IGS_AD_APPL_LTR.correspondence_type%TYPE,
2484: cp_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE) IS
2485: SELECT issue_dt
2486: FROM IGS_CO_OU_CO_REF
2487: WHERE person_id = p_person_id AND
2480: sequence_number = p_sequence_number;
2481: CURSOR c_ocr (
2482: p_person_id IGS_AD_APPL_LTR.person_id%TYPE,
2483: p_correspondence_type IGS_AD_APPL_LTR.correspondence_type%TYPE,
2484: cp_spl_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE) IS
2485: SELECT issue_dt
2486: FROM IGS_CO_OU_CO_REF
2487: WHERE person_id = p_person_id AND
2488: correspondence_type = p_correspondence_type AND
2568: v_completed_appl BOOLEAN DEFAULT FALSE;
2569: v_acai_records_found BOOLEAN DEFAULT FALSE;
2570: v_rej_lap BOOLEAN DEFAULT FALSE;
2571: v_s_appl_inst_stat igs_ad_ps_appl_inst.appl_inst_status%TYPE;
2572: v_s_adm_appl_status igs_ad_appl_stat.s_adm_appl_status%TYPE;
2573: v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
2574: v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
2575: -- v_exit_loop NUMBER DEFAULT 0;
2576: v_s_adm_offer_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
2698: -- Routine to return the date an admission application was created
2699: -- and the Oracle user name of the IGS_PE_PERSON that created the admission
2700: -- application.
2701: DECLARE
2702: v_create_who IGS_AD_APPL.LAST_UPDATED_BY%TYPE DEFAULT NULL;
2703: v_create_on IGS_AD_APPL.LAST_UPDATE_DATE%TYPE DEFAULT NULL;
2704: CURSOR c_aah IS
2705: SELECT aah.hist_who,
2706: aah.hist_start_dt
2699: -- and the Oracle user name of the IGS_PE_PERSON that created the admission
2700: -- application.
2701: DECLARE
2702: v_create_who IGS_AD_APPL.LAST_UPDATED_BY%TYPE DEFAULT NULL;
2703: v_create_on IGS_AD_APPL.LAST_UPDATE_DATE%TYPE DEFAULT NULL;
2704: CURSOR c_aah IS
2705: SELECT aah.hist_who,
2706: aah.hist_start_dt
2707: FROM IGS_AD_APPL_HIST aah
2703: v_create_on IGS_AD_APPL.LAST_UPDATE_DATE%TYPE DEFAULT NULL;
2704: CURSOR c_aah IS
2705: SELECT aah.hist_who,
2706: aah.hist_start_dt
2707: FROM IGS_AD_APPL_HIST aah
2708: WHERE person_id = p_person_id AND
2709: admission_appl_number = p_admission_appl_number
2710: ORDER BY
2711: hist_start_dt ASC;
2711: hist_start_dt ASC;
2712: CURSOR c_aa IS
2713: SELECT LAST_UPDATED_BY,
2714: LAST_UPDATE_DATE
2715: FROM IGS_AD_APPL
2716: WHERE person_id = p_person_id AND
2717: admission_appl_number = p_admission_appl_number;
2718: BEGIN
2719: -- Determine who the admission application was created by and on what date.
2752: IS
2753: BEGIN --admp_get_aa_dtl
2754: --Return admission application details
2755: DECLARE
2756: v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2753: BEGIN --admp_get_aa_dtl
2754: --Return admission application details
2755: DECLARE
2756: v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2754: --Return admission application details
2755: DECLARE
2756: v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2755: DECLARE
2756: v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2756: v_admission_cat IGS_AD_APPL.admission_cat%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764: v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2757: v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764: v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2765: CURSOR c_aa IS
2758: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764: v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2765: CURSOR c_aa IS
2766: SELECT aa.admission_cat,
2759: v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE;
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764: v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2765: CURSOR c_aa IS
2766: SELECT aa.admission_cat,
2767: aa.s_admission_process_type,
2760: v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE;
2761: v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE;
2762: v_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2763: v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
2764: v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE;
2765: CURSOR c_aa IS
2766: SELECT aa.admission_cat,
2767: aa.s_admission_process_type,
2768: aa.acad_cal_type,
2771: aa.adm_ci_sequence_number,
2772: aa.appl_dt,
2773: aa.adm_appl_status,
2774: aa.adm_fee_status
2775: FROM IGS_AD_APPL aa
2776: WHERE aa.person_id = p_person_id AND
2777: aa.admission_appl_number = p_admission_appl_number;
2778: BEGIN
2779: OPEN c_aa;
2828: -- relating the Admission course Application Instance admission period.
2829: DECLARE
2830: CURSOR c_aa IS
2831: SELECT aa.acad_cal_type
2832: FROM IGS_AD_APPL aa
2833: WHERE person_id = p_person_id AND
2834: admission_appl_number = p_admission_appl_number;
2835: CURSOR c_cir (
2836: cp_acad_cal_type IN IGS_AD_APPL.acad_cal_type%TYPE,
2832: FROM IGS_AD_APPL aa
2833: WHERE person_id = p_person_id AND
2834: admission_appl_number = p_admission_appl_number;
2835: CURSOR c_cir (
2836: cp_acad_cal_type IN IGS_AD_APPL.acad_cal_type%TYPE,
2837: cp_adm_cal_type IN IGS_AD_PS_APPL_INST.adm_cal_type%TYPE,
2838: cp_adm_ci_sequence_number IN
2839: IGS_AD_PS_APPL_INST.adm_ci_sequence_number%TYPE) IS
2840: SELECT ci.alternate_code, ci.start_dt
2845: cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
2846: cir.sup_cal_type = ci.cal_type AND
2847: cir.sup_ci_sequence_number = ci.sequence_number
2848: ORDER BY ci.start_dt;
2849: v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE;
2850: v_alternate_code IGS_CA_INST.alternate_code%TYPE;
2851: v_start_dt IGS_CA_INST.start_dt%TYPE;
2852: BEGIN
2853: IF p_acad_cal_type IS NULL THEN
2970: Who When What
2971: *******************************************************************************/
2972: CURSOR c_application_id IS
2973: SELECT application_id
2974: FROM igs_ad_appl_all
2975: WHERE person_id = p_person_id
2976: AND admission_appl_number = p_admission_appl_number;
2977: l_application_id igs_ad_appl_all.application_id%TYPE;
2978: BEGIN
2973: SELECT application_id
2974: FROM igs_ad_appl_all
2975: WHERE person_id = p_person_id
2976: AND admission_appl_number = p_admission_appl_number;
2977: l_application_id igs_ad_appl_all.application_id%TYPE;
2978: BEGIN
2979: IF p_person_id IS NOT NULL AND
2980: p_admission_appl_number IS NOT NULL THEN
2981: FOR c_application_id_rec IN c_application_id
2998: Known limitations/enhancements and/or remarks:
2999: Change History: (who, when, what: )
3000: Who When What
3001: *******************************************************************************/
3002: l_FeeAmt igs_ad_appl_all.appl_fee_amt%TYPE :=0;
3003: l_TotalRemitt igs_ad_app_req.fee_amount%TYPE :=0;
3004: l_WaivedTemp igs_ad_app_req.fee_amount%TYPE :=0;
3005: lOverallStatus igs_lookup_values.meaning%TYPE;
3006: CURSOR c_adm_fee_amt IS
3004: l_WaivedTemp igs_ad_app_req.fee_amount%TYPE :=0;
3005: lOverallStatus igs_lookup_values.meaning%TYPE;
3006: CURSOR c_adm_fee_amt IS
3007: SELECT appl_fee_amt
3008: FROM igs_ad_appl_all
3009: WHERE person_id = p_person_id
3010: AND admission_appl_number = p_admission_appl_number;
3011: CURSOR c_appl_fee_status_meaning (p_sys_fee_status igs_ad_code_classes.class%TYPE) IS
3012: SELECT meaning
3084: Change History: (who, when, what: )
3085: Who When What
3086: *******************************************************************************/
3087: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE;
3088: l_adm_appl_status_old igs_ad_appl.adm_appl_status%TYPE;
3089: l_adm_appl_status_new igs_ad_appl.adm_appl_status%TYPE;
3090: l_appl_inst_status igs_ad_ps_appl_inst.appl_inst_status%TYPE;
3091: cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3092: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3085: Who When What
3086: *******************************************************************************/
3087: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE;
3088: l_adm_appl_status_old igs_ad_appl.adm_appl_status%TYPE;
3089: l_adm_appl_status_new igs_ad_appl.adm_appl_status%TYPE;
3090: l_appl_inst_status igs_ad_ps_appl_inst.appl_inst_status%TYPE;
3091: cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3092: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3093: cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3090: l_appl_inst_status igs_ad_ps_appl_inst.appl_inst_status%TYPE;
3091: cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3092: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3093: cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3094: CURSOR c_adm_appl_status (cp_person_id igs_ad_appl.person_id%TYPE,
3095: cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
3096: SELECT adm_appl_status
3097: FROM igs_ad_appl
3098: WHERE person_id = cp_person_id
3091: cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3092: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
3093: cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3094: CURSOR c_adm_appl_status (cp_person_id igs_ad_appl.person_id%TYPE,
3095: cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
3096: SELECT adm_appl_status
3097: FROM igs_ad_appl
3098: WHERE person_id = cp_person_id
3099: AND admission_appl_number = cp_admission_appl_number;
3093: cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
3094: CURSOR c_adm_appl_status (cp_person_id igs_ad_appl.person_id%TYPE,
3095: cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
3096: SELECT adm_appl_status
3097: FROM igs_ad_appl
3098: WHERE person_id = cp_person_id
3099: AND admission_appl_number = cp_admission_appl_number;
3100: CURSOR c_adm_outcome_status (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
3101: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
3153: IF (p_nominated_course_cd IS NOT NULL and p_sequence_number IS NOT NULL)
3154: THEN
3155:
3156: IF NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(l_appl_inst_status),'-1') = cst_withdrawn THEN
3157: fnd_message.set_name('IGS','IGS_AD_APPL_INST_WITHD');
3158: igs_ge_msg_stack.add;
3159: app_exception.raise_exception; -- application instance is withdrawn
3160: END IF;
3161:
3173: IF ( l_offer_inst = 'Y' AND l_is_inst_complete)
3174: AND
3175: (l_def_term_adm_appl_num IS NOT NULL OR l_def_appl_sequence_num IS NOT NULL)
3176: THEN
3177: fnd_message.set_name('IGS','IGS_AD_APPL_INST_COMPL');
3178: igs_ge_msg_stack.add;
3179: app_exception.raise_exception;
3180: END IF;
3181:
3200: IF (p_nominated_course_cd IS NULL and p_sequence_number IS NULL)
3201: THEN
3202: fnd_message.set_name('IGS','IGS_AD_CANNOT_CHG_APPL_DTL');
3203: ELSE
3204: fnd_message.set_name('IGS','IGS_AD_APPL_INST_COMPL');
3205: END IF;
3206:
3207: igs_ge_msg_stack.add;
3208: app_exception.raise_exception;
3283: *******************************************************************************/
3284: BEGIN
3285: DECLARE
3286: l_fee_exists VARCHAR2(1);
3287: CURSOR c_igs_ad_appl_all (cp_application_id igs_ad_appl_all.application_id%TYPE) IS
3288: SELECT person_id, admission_appl_number, appl_fee_amt
3289: FROM igs_ad_appl_all
3290: WHERE application_id = cp_application_id;
3291: rec_igs_ad_appl_all c_igs_ad_appl_all%ROWTYPE;
3285: DECLARE
3286: l_fee_exists VARCHAR2(1);
3287: CURSOR c_igs_ad_appl_all (cp_application_id igs_ad_appl_all.application_id%TYPE) IS
3288: SELECT person_id, admission_appl_number, appl_fee_amt
3289: FROM igs_ad_appl_all
3290: WHERE application_id = cp_application_id;
3291: rec_igs_ad_appl_all c_igs_ad_appl_all%ROWTYPE;
3292: CURSOR c_igs_ad_app_req (cp_person_id igs_ad_app_req.person_id%TYPE,
3293: cp_admission_appl_number igs_ad_app_req.admission_appl_number%TYPE) IS
3287: CURSOR c_igs_ad_appl_all (cp_application_id igs_ad_appl_all.application_id%TYPE) IS
3288: SELECT person_id, admission_appl_number, appl_fee_amt
3289: FROM igs_ad_appl_all
3290: WHERE application_id = cp_application_id;
3291: rec_igs_ad_appl_all c_igs_ad_appl_all%ROWTYPE;
3292: CURSOR c_igs_ad_app_req (cp_person_id igs_ad_app_req.person_id%TYPE,
3293: cp_admission_appl_number igs_ad_app_req.admission_appl_number%TYPE) IS
3294: SELECT 'X'
3295: FROM igs_ad_app_req
3298: BEGIN
3299: IF p_application_id IS NULL THEN
3300: RETURN NULL;
3301: ELSE
3302: OPEN c_igs_ad_appl_all(p_application_id);
3303: FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304: CLOSE c_igs_ad_appl_all;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3299: IF p_application_id IS NULL THEN
3300: RETURN NULL;
3301: ELSE
3302: OPEN c_igs_ad_appl_all(p_application_id);
3303: FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304: CLOSE c_igs_ad_appl_all;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307: RETURN 'NOFEEDUE';
3300: RETURN NULL;
3301: ELSE
3302: OPEN c_igs_ad_appl_all(p_application_id);
3303: FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304: CLOSE c_igs_ad_appl_all;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307: RETURN 'NOFEEDUE';
3308: ELSE
3301: ELSE
3302: OPEN c_igs_ad_appl_all(p_application_id);
3303: FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304: CLOSE c_igs_ad_appl_all;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307: RETURN 'NOFEEDUE';
3308: ELSE
3309: OPEN c_igs_ad_app_req(rec_igs_ad_appl_all.person_id, rec_igs_ad_appl_all.admission_appl_number);
3302: OPEN c_igs_ad_appl_all(p_application_id);
3303: FETCH c_igs_ad_appl_all INTO rec_igs_ad_appl_all;
3304: CLOSE c_igs_ad_appl_all;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307: RETURN 'NOFEEDUE';
3308: ELSE
3309: OPEN c_igs_ad_app_req(rec_igs_ad_appl_all.person_id, rec_igs_ad_appl_all.admission_appl_number);
3310: FETCH c_igs_ad_app_req INTO l_fee_exists;
3305: IF (rec_igs_ad_appl_all.appl_fee_amt is NULL OR
3306: rec_igs_ad_appl_all.appl_fee_amt = 0) THEN
3307: RETURN 'NOFEEDUE';
3308: ELSE
3309: OPEN c_igs_ad_app_req(rec_igs_ad_appl_all.person_id, rec_igs_ad_appl_all.admission_appl_number);
3310: FETCH c_igs_ad_app_req INTO l_fee_exists;
3311: CLOSE c_igs_ad_app_req;
3312: IF l_fee_exists is NULL THEN
3313: RETURN 'PENDING';
3687: AND rownum <= 1 ;
3688: CURSOR c_aa IS
3689: SELECT aa.admission_cat,
3690: aa.s_admission_process_type
3691: FROM IGS_AD_APPL aa
3692: WHERE aa.person_id = p_person_id AND
3693: aa.admission_appl_number = p_admission_appl_number;
3694: CURSOR c_apcs ( cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
3695: cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE
3700: s_admission_process_type = cp_s_admission_process_type AND
3701: s_admission_step_type = 'RECONSIDER' AND
3702: step_group_type <> 'TRACK' ;
3703: l_other_offer_inst_cnt NUMBER DEFAULT 0;
3704: l_admission_cat IGS_AD_APPL.admission_cat%TYPE;
3705: l_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
3706: l_is_reconisder_allowed BOOLEAN DEFAULT FALSE;
3707: l_dummy varchar2(30) DEFAULT NULL;
3708: BEGIN
3701: s_admission_step_type = 'RECONSIDER' AND
3702: step_group_type <> 'TRACK' ;
3703: l_other_offer_inst_cnt NUMBER DEFAULT 0;
3704: l_admission_cat IGS_AD_APPL.admission_cat%TYPE;
3705: l_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
3706: l_is_reconisder_allowed BOOLEAN DEFAULT FALSE;
3707: l_dummy varchar2(30) DEFAULT NULL;
3708: BEGIN
3709: OPEN c_other_offer_inst ;