[Home] [Help]
2353: l_conc_flag BOOLEAN;
2354: l_request_id NUMBER;
2355: l_message_name VARCHAR2(30);
2356:
2357: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2358: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2354: l_request_id NUMBER;
2355: l_message_name VARCHAR2(30);
2356:
2357: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2358: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2368:
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2368:
2369:
2528: ********************************************************************************************* */
2529: BEGIN
2530:
2531: /* Primary Key Validation */
2532: IF igs_ps_unit_ofr_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
2533: x_version_number => p_usec_rec.version_number,
2534: x_cal_type => l_c_cal_type ) THEN
2535: p_usec_rec.status := 'K';
2536: RETURN;
2537: END IF;
2538:
2539: /* Validate Check Constraints */
2540: BEGIN
2541: igs_ps_unit_ofr_pkg.check_constraints ( 'UNIT_CD', p_usec_rec.unit_cd );
2542: EXCEPTION
2543: WHEN OTHERS THEN
2544: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CD', 'LEGACY_TOKENS', TRUE);
2545: p_usec_rec.status := 'E';
2545: p_usec_rec.status := 'E';
2546: END;
2547:
2548: BEGIN
2549: igs_ps_unit_ofr_pkg.check_constraints ( 'CAL_TYPE', l_c_cal_type );
2550: EXCEPTION
2551: WHEN OTHERS THEN
2552: igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'ALT_CODE', 'LEGACY_TOKENS', TRUE);
2553: p_usec_rec.status := 'E';
2579: Purpose :
2580: Known limitations,enhancements,remarks:
2581: Change History (in reverse chronological order)
2582: Who When What
2583: sommukhe 14-NOV-2005 Bug # 4730169 addded column abort_flag in the insert to igs_ps_unit_ofr
2584: ********************************************************************************************* */
2585: BEGIN
2586:
2587: validate_uo_parameters ( p_usec_rec );
2642: END IF;
2643: END IF;
2644:
2645: IF ( p_usec_rec.status = 'S' ) THEN
2646: INSERT INTO igs_ps_unit_ofr
2647: (unit_cd,
2648: version_number,
2649: cal_type,
2650: created_by,
2693:
2694: -- Procedure to validate Database constraints for Unit Offering Pattern Records.
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2694: -- Procedure to validate Database constraints for Unit Offering Pattern Records.
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2704: version_number = cp_n_ver_num AND
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2704: version_number = cp_n_ver_num AND
2705: cal_type = cp_c_cal_type AND
2706: ci_sequence_number = cp_n_ci_seq_num;
2707: rec_rowid cur_rowid%ROWTYPE;
2708:
2709: BEGIN
2710:
2711: /* Pk validation for IGS_PS_UNIT_OFR_PAT , having delete_flag implementation*/
2712: l_b_uop_deleted := FALSE;
2713: OPEN cur_rowid( p_usec_rec.unit_cd,p_usec_rec.version_number,l_c_cal_type,l_n_seq_num);
2714: FETCH cur_rowid INTO rec_rowid;
2715: IF cur_rowid%FOUND THEN
2734: p_usec_rec.status := 'E';
2735: END IF;
2736:
2737: -- Check for existence of Unit Offering
2738: IF NOT igs_ps_unit_ofr_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
2739: x_version_number => p_usec_rec.version_number,
2740: x_cal_type => l_c_cal_type ) THEN
2741: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_OFR_PAT', 'LEGACY_TOKENS', FALSE);
2742: p_usec_rec.status := 'E';
2744:
2745: -- Check Constraints checking.
2746: IF p_usec_rec.waitlist_allowed IS NOT NULL THEN
2747: BEGIN
2748: igs_ps_unit_ofr_pat_pkg.check_constraints ( 'WAITLIST_ALLOWED', p_usec_rec.waitlist_allowed);
2749: EXCEPTION
2750: WHEN OTHERS THEN
2751: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'WAITLIST_ALLOWED', 'LEGACY_TOKENS', TRUE);
2752: p_usec_rec.status := 'E';
2754: END IF;
2755:
2756: IF p_usec_rec.max_students_per_waitlist IS NOT NULL THEN
2757: BEGIN
2758: igs_ps_unit_ofr_pat_pkg.check_constraints ( 'MAX_STUDENTS_PER_WAITLIST', p_usec_rec.max_students_per_waitlist);
2759: EXCEPTION
2760: WHEN OTHERS THEN
2761: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'MAX_STUDENTS_PER_WAITLIST', 'LEGACY_TOKENS', TRUE);
2762: p_usec_rec.status := 'E';
2837: END IF;
2838:
2839: IF ( p_usec_rec.status = 'S' ) THEN
2840: IF l_b_uop_deleted THEN
2841: UPDATE igs_ps_unit_ofr_pat_all
2842: SET
2843: waitlist_allowed = p_usec_rec.waitlist_allowed,
2844: max_students_per_waitlist = p_usec_rec.max_students_per_waitlist,
2845: delete_flag = 'N',
2857: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2858: ||p_usec_rec.teach_cal_alternate_code);
2859: END IF;
2860: ELSE
2861: INSERT INTO igs_ps_unit_ofr_pat_all
2862: (unit_cd,
2863: version_number,
2864: cal_type,
2865: ci_sequence_number,
2934: Who When What
2935: sarakshi 11-Sep-2003 Enh#3052452,dervied teh value of sup_uoo_id.
2936: sarakshi 22-Aug-2003 Bug#304509, defaulting the value of Not Multiple Unit Section Flag
2937: ********************************************************************************************* */
2938: l_c_sup_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2939: l_n_sup_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2940: l_d_sup_start_dt igs_ca_inst_all.start_dt%TYPE;
2941: l_d_sup_end_dt igs_ca_inst_all.end_dt%TYPE;
2942: l_c_message VARCHAR2(30);
2935: sarakshi 11-Sep-2003 Enh#3052452,dervied teh value of sup_uoo_id.
2936: sarakshi 22-Aug-2003 Bug#304509, defaulting the value of Not Multiple Unit Section Flag
2937: ********************************************************************************************* */
2938: l_c_sup_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2939: l_n_sup_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2940: l_d_sup_start_dt igs_ca_inst_all.start_dt%TYPE;
2941: l_d_sup_end_dt igs_ca_inst_all.end_dt%TYPE;
2942: l_c_message VARCHAR2(30);
2943:
3001: Purpose :
3002: Known limitations,enhancements,remarks:
3003: Change History (in reverse chronological order)
3004: Who When What
3005: sarakshi 11-Sep-2003 Enh#3052452, Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3006: for validating default_enroll_flag.
3007: vvutukur 19-Aug-2003 Enh#3045069.PSP Enh Build. Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3008: for validating not_multiple_section_flag.
3009: ********************************************************************************************* */
3003: Change History (in reverse chronological order)
3004: Who When What
3005: sarakshi 11-Sep-2003 Enh#3052452, Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3006: for validating default_enroll_flag.
3007: vvutukur 19-Aug-2003 Enh#3045069.PSP Enh Build. Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3008: for validating not_multiple_section_flag.
3009: ********************************************************************************************* */
3010: BEGIN
3011:
3010: BEGIN
3011:
3012: IF p_insert_update = 'I' THEN
3013: /* Check for Unique Key Validation */
3014: IF igs_ps_unit_ofr_opt_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
3015: x_version_number => p_usec_rec.version_number,
3016: x_cal_type => l_c_cal_type,
3017: x_ci_sequence_number => l_n_seq_num,
3018: x_location_cd => p_usec_rec.location_cd,
3026: /* Check for Check Constraints */
3027:
3028: --Validate whether the column not_multiple_section_flag has value other than 'Y' or 'N'.
3029: BEGIN
3030: igs_ps_unit_ofr_opt_pkg.check_constraints('NOT_MULTIPLE_SECTION_FLAG',p_usec_rec.not_multiple_section_flag);
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N',
3034: 'NOT_MULTIPLE_SECTION_FLAG',
3038: END;
3039:
3040: -- Unit Class should be in Upper Case
3041: BEGIN
3042: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_CLASS', p_usec_rec.unit_class );
3043: EXCEPTION
3044: WHEN OTHERS THEN
3045: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CLASS', 'LEGACY_TOKENS', TRUE);
3046: p_usec_rec.status := 'E';
3052: p_usec_rec.status := 'E';
3053: END IF;
3054:
3055: BEGIN
3056: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'GRADING_SCHEMA_CD', p_usec_rec.grading_schema_cd );
3057: EXCEPTION
3058: WHEN OTHERS THEN
3059: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'USEC_GRADING_SCHM_CD', 'LEGACY_TOKENS', TRUE);
3060: p_usec_rec.status := 'E';
3060: p_usec_rec.status := 'E';
3061: END;
3062:
3063: BEGIN
3064: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'LOCATION_CD', p_usec_rec.location_cd );
3065: EXCEPTION
3066: WHEN OTHERS THEN
3067: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'LOCATION_CD', 'LEGACY_TOKENS', TRUE);
3068: p_usec_rec.status := 'E';
3068: p_usec_rec.status := 'E';
3069: END;
3070:
3071: BEGIN
3072: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_QUOTA', p_usec_rec.unit_quota );
3073: EXCEPTION
3074: WHEN OTHERS THEN
3075: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'UNIT_QUOTA', 'LEGACY_TOKENS', TRUE);
3076: p_usec_rec.status := 'E';
3077: END;
3078:
3079: IF p_usec_rec.unit_quota_reserved_places IS NOT NULL THEN
3080: BEGIN
3081: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_QUOTA_RESERVED_PLACES', p_usec_rec.unit_quota_reserved_places );
3082: EXCEPTION
3083: WHEN OTHERS THEN
3084: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'UNIT_QUOTA_RESERVED_PLACES', 'LEGACY_TOKENS', TRUE);
3085: p_usec_rec.status := 'E';
3086: END;
3087: END IF;
3088:
3089: BEGIN
3090: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'CI_SEQUENCE_NUMBER', l_n_seq_num );
3091: EXCEPTION
3092: WHEN OTHERS THEN
3093: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'CAL_SEQ_NUM', 'LEGACY_TOKENS', TRUE);
3094: p_usec_rec.status := 'E';
3094: p_usec_rec.status := 'E';
3095: END;
3096:
3097: BEGIN
3098: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'GRADING_SCHEMA_PRCDNCE_IND', p_usec_rec.grading_schema_prcdnce_ind );
3099: EXCEPTION
3100: WHEN OTHERS THEN
3101: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'GRADING_SCHEMA_PRCDNCE_IND', 'LEGACY_TOKENS', TRUE);
3102: p_usec_rec.status := 'E';
3102: p_usec_rec.status := 'E';
3103: END;
3104:
3105: BEGIN
3106: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'IVRS_AVAILABLE_IND', p_usec_rec.ivrs_available_ind );
3107: EXCEPTION
3108: WHEN OTHERS THEN
3109: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'IVRS_AVAILABLE_IND', 'LEGACY_TOKENS', TRUE);
3110: p_usec_rec.status := 'E';
3110: p_usec_rec.status := 'E';
3111: END;
3112:
3113: BEGIN
3114: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'OFFERED_IND', p_usec_rec.offered_ind );
3115: EXCEPTION
3116: WHEN OTHERS THEN
3117: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'OFFERED_IND', 'LEGACY_TOKENS', TRUE);
3118: p_usec_rec.status := 'E';
3118: p_usec_rec.status := 'E';
3119: END;
3120:
3121: BEGIN
3122: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'NON_STD_USEC_IND', p_usec_rec.non_std_usec_ind );
3123: EXCEPTION
3124: WHEN OTHERS THEN
3125: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'NON_STD_USEC_IND', 'LEGACY_TOKENS', TRUE);
3126: p_usec_rec.status := 'E';
3126: p_usec_rec.status := 'E';
3127: END;
3128:
3129: BEGIN
3130: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'AUDITABLE_IND', p_usec_rec.auditable_ind );
3131: EXCEPTION
3132: WHEN OTHERS THEN
3133: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'AUDITABLE_IND', 'LEGACY_TOKENS', TRUE);
3134: p_usec_rec.status := 'E';
3134: p_usec_rec.status := 'E';
3135: END;
3136:
3137: BEGIN
3138: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'AUDIT_PERMISSION_IND', p_usec_rec.audit_permission_ind );
3139: EXCEPTION
3140: WHEN OTHERS THEN
3141: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'AUDIT_PERMISSION_IND', 'LEGACY_TOKENS', TRUE);
3142: p_usec_rec.status := 'E';
3160:
3161: /* Check for Foreign Key Validations */
3162:
3163: -- Check for existence of Unit Offering Pattern
3164: IF NOT igs_ps_unit_ofr_pat_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
3165: x_version_number => p_usec_rec.version_number,
3166: x_cal_type => l_c_cal_type,
3167: x_ci_sequence_number => l_n_seq_num ) THEN
3168: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_OFR_PAT', 'LEGACY_TOKENS', FALSE);
3202: END IF;
3203:
3204: --Validate default enroll flag
3205: BEGIN
3206: igs_ps_unit_ofr_opt_pkg.check_constraints('DEFAULT_ENROLL_FLAG',p_usec_rec.default_enroll_flag);
3207: EXCEPTION
3208: WHEN OTHERS THEN
3209: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','DEFAULT_ENROLL_FLAG','LEGACY_TOKENS',TRUE);
3210: p_usec_rec.status :='E';
3233: vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Added new column not_multiple_section_flag while insertion.
3234: ********************************************************************************************* */
3235: AS
3236:
3237: CURSOR c_usec_check(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
3238: SELECT 'X'
3239: FROM igs_ps_unit_ofr_opt_all
3240: WHERE uoo_id=cp_uoo_id
3241: AND relation_type='SUPERIOR';
3235: AS
3236:
3237: CURSOR c_usec_check(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
3238: SELECT 'X'
3239: FROM igs_ps_unit_ofr_opt_all
3240: WHERE uoo_id=cp_uoo_id
3241: AND relation_type='SUPERIOR';
3242: l_c_var VARCHAR2(1);
3243:
3272: l_cur_int_usec cur_int_usec%ROWTYPE;
3273:
3274:
3275: FUNCTION check_insert_update ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type) RETURN VARCHAR2 IS
3276: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3277: SELECT 'X'
3278: FROM igs_ps_unit_ofr_opt_all
3279: WHERE unit_cd = p_usec_rec.unit_cd
3280: AND version_number = p_usec_rec.version_number
3274:
3275: FUNCTION check_insert_update ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type) RETURN VARCHAR2 IS
3276: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3277: SELECT 'X'
3278: FROM igs_ps_unit_ofr_opt_all
3279: WHERE unit_cd = p_usec_rec.unit_cd
3280: AND version_number = p_usec_rec.version_number
3281: AND ci_sequence_number =cp_seq_num
3282: AND unit_class = p_usec_rec.unit_class
3298: END check_insert_update;
3299:
3300: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
3301:
3302: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3303: SELECT *
3304: FROM igs_ps_unit_ofr_opt_all
3305: WHERE unit_cd = p_usec_rec.unit_cd
3306: AND version_number = p_usec_rec.version_number
3300: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
3301:
3302: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3303: SELECT *
3304: FROM igs_ps_unit_ofr_opt_all
3305: WHERE unit_cd = p_usec_rec.unit_cd
3306: AND version_number = p_usec_rec.version_number
3307: AND ci_sequence_number =cp_seq_num
3308: AND unit_class = p_usec_rec.unit_class
3775: IF l_n_sup_uoo_id IS NOT NULL THEN
3776: OPEN c_usec_check(l_n_sup_uoo_id);
3777: FETCH c_usec_check INTO l_c_var;
3778: IF c_usec_check%NOTFOUND THEN
3779: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
3780: WHERE uoo_id = l_n_sup_uoo_id;
3781: END IF;
3782: CLOSE c_usec_check;
3783: l_c_relation_type := 'SUBORDINATE';
3793: cp_location_cd IN VARCHAR2,
3794: cp_unit_class IN VARCHAR2
3795: ) IS
3796: SELECT relation_type,sup_uoo_id
3797: FROM igs_ps_unit_ofr_opt_all
3798: WHERE unit_cd = cp_unit_cd
3799: AND version_number = cp_version_number
3800: AND ci_sequence_number =cp_seq_num
3801: AND unit_class = cp_unit_class
3804: l_cur_usec cur_usec%ROWTYPE;
3805:
3806: CURSOR c_count_sup(cp_uoo_id NUMBER) IS
3807: SELECT COUNT(*)
3808: FROM igs_ps_unit_ofr_opt_all
3809: WHERE sup_uoo_id= cp_uoo_id;
3810: l_c_count NUMBER;
3811: BEGIN
3812:
3818: IF l_n_sup_uoo_id IS NOT NULL THEN
3819: OPEN c_usec_check(l_n_sup_uoo_id);
3820: FETCH c_usec_check INTO l_c_var;
3821: IF c_usec_check%NOTFOUND THEN
3822: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
3823: WHERE uoo_id = l_n_sup_uoo_id;
3824: END IF;
3825: CLOSE c_usec_check;
3826: l_c_relation_type := 'SUBORDINATE';
3831: OPEN c_count_sup(l_cur_usec.sup_uoo_id);
3832: FETCH c_count_sup INTO l_c_count;
3833: IF c_count_sup%FOUND THEN
3834: IF l_c_count < 2 THEN
3835: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'NONE'
3836: WHERE uoo_id = l_cur_usec.sup_uoo_id;
3837: END IF;
3838: END IF;
3839: CLOSE c_count_sup;
3850: --Set the value of the call_number if the profile option is AUTO
3851:
3852: IF l_insert_update = 'I' THEN
3853: IF FND_PROFILE.VALUE('IGS_PS_CALL_NUMBER') = 'AUTO' THEN
3854: p_usec_rec.call_number := igs_ps_unit_ofr_opt_pkg.get_call_number( l_c_cal_type, l_n_seq_num);
3855: END IF;
3856:
3857: INSERT INTO igs_ps_unit_ofr_opt_all
3858: (unit_cd,
3853: IF FND_PROFILE.VALUE('IGS_PS_CALL_NUMBER') = 'AUTO' THEN
3854: p_usec_rec.call_number := igs_ps_unit_ofr_opt_pkg.get_call_number( l_c_cal_type, l_n_seq_num);
3855: END IF;
3856:
3857: INSERT INTO igs_ps_unit_ofr_opt_all
3858: (unit_cd,
3859: version_number,
3860: cal_type,
3861: ci_sequence_number,
3906: l_c_cal_type,
3907: l_n_seq_num,
3908: p_usec_rec.location_cd,
3909: p_usec_rec.unit_class,
3910: igs_ps_unit_ofr_opt_uoo_id_s.NEXTVAL,
3911: p_usec_rec.ivrs_available_ind,
3912: p_usec_rec.call_number,
3913: p_usec_rec.unit_section_status,
3914: p_usec_rec.unit_section_start_date,
3972:
3973: END IF;
3974:
3975: ELSE --update
3976: UPDATE igs_ps_unit_ofr_opt_all SET
3977: ivrs_available_ind = p_usec_rec.ivrs_available_ind,
3978: call_number=p_usec_rec.call_number,
3979: unit_section_status=p_usec_rec.unit_section_status,
3980: unit_section_start_date=p_usec_rec.unit_section_start_date,
4083: END IF;
4084: END IF;
4085:
4086: /* Check for Foreign Key Constraints */
4087: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
4088: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
4089: p_usec_rec.status := 'E';
4090: END IF;
4091:
4668: END IF;
4669: END IF;
4670:
4671: /* Check for Foreign Key Constraints */
4672: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
4673: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
4674: p_usec_rec.status := 'E';
4675: END IF;
4676:
5137: END check_insert_update;
5138:
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5138:
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5148: AND version_number = cp_n_ver_num
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5148: AND version_number = cp_n_ver_num
5149: AND cal_type = cp_c_cal_type
5150: AND ci_sequence_number = cp_n_seq_num ;
5299: END IF;
5300:
5301: IF p_usec_rec.usec_waitlist_allowed IS NOT NULL THEN
5302: BEGIN
5303: igs_ps_unit_ofr_pat_pkg.check_constraints ('WAITLIST_ALLOWED', p_usec_rec.usec_waitlist_allowed);
5304: EXCEPTION
5305: WHEN OTHERS THEN
5306: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','WAITLIST_ALLOWED', 'LEGACY_TOKENS', TRUE);
5307: p_usec_rec.status := 'E';
5309: END IF;
5310:
5311: IF p_usec_rec.usec_max_students_per_waitlist IS NOT NULL THEN
5312: BEGIN
5313: igs_ps_unit_ofr_pat_pkg.check_constraints('MAX_STUDENTS_PER_WAITLIST',p_usec_rec.usec_max_students_per_waitlist);
5314: EXCEPTION
5315: WHEN OTHERS THEN
5316: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','MAX_STUDENTS_PER_WAITLIST','LEGACY_TOKENS',TRUE);
5317: p_usec_rec.status :='E';
5317: p_usec_rec.status :='E';
5318: END;
5319: END IF;
5320:
5321: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
5322: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
5323: p_usec_rec.status := 'E';
5324: END IF;
5325:
5667: Change History (in reverse chronological order)
5668: Who When What
5669: ********************************************************************************************** */
5670: l_insert_update VARCHAR2(1);
5671: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
5672: l_tbl_uoo igs_ps_create_generic_pkg.uoo_tbl_type;
5673:
5674: /* Private Procedures for create_usec_grd_sch */
5675: PROCEDURE trim_values ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5748: END check_insert_update;
5749:
5750: -- Carry out derivations and validate them
5751: PROCEDURE validate_derivations ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5752: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
5753: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
5754: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
5755: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
5756: l_c_message VARCHAR2(30);
5749:
5750: -- Carry out derivations and validate them
5751: PROCEDURE validate_derivations ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5752: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
5753: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
5754: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
5755: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
5756: l_c_message VARCHAR2(30);
5757: BEGIN
5815: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'USEC_GRADING_SCHM', 'LEGACY_TOKENS', FALSE);
5816: p_usec_gs_rec.status := 'E';
5817: END IF;
5818:
5819: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
5820: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
5821: p_usec_gs_rec.status := 'E';
5822: END IF;
5823: END validate_db_cons;
6052: For PSP Scheduling Enhancements TD:
6053: Modified the local procedure's validate_derivation,validate_db_cons.
6054: ********************************************************************************************** */
6055:
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6053: Modified the local procedure's validate_derivation,validate_db_cons.
6054: ********************************************************************************************** */
6055:
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6061: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6061: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6062: l_n_usec_occurs_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6063:
6064: l_n_building_code igs_ps_usec_occurs_all.building_code%TYPE;
6382:
6383: /* Validate FK Constraints */
6384:
6385: -- Check for the existence of Unit Section
6386: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
6387: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
6388: p_uso_rec.status := 'E';
6389: END IF;
6390:
7337:
7338:
7339: l_c_cal_type igs_ca_inst_all.cal_type%TYPE; -- Holds Calendar Type
7340: l_n_seq_num igs_ca_inst_all.sequence_number%TYPE; -- Holds Calendar Instance Sequence Number
7341: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE; -- Holds Unit Offering Options Identifier
7342: l_n_usec_ref_id igs_ps_usec_ref.unit_section_reference_id%TYPE; -- Holds Unit Section Reference Identifier
7343: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE; -- Holds the Unit Section Occurrence Identifier
7344:
7345: --
7445:
7446: -- Derivation of values required to create Unit Section Referrence.
7447: PROCEDURE validate_usec_derivations( p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type) AS
7448:
7449: CURSOR c_usec_ref_id (cp_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
7450: SELECT A.unit_section_reference_id
7451: FROM igs_ps_usec_ref A
7452: WHERE A.uoo_id = cp_uoo_id;
7453:
8290: ***********************************************************************************************/
8291:
8292: l_n_ins_id igs_ps_uso_instrctrs.instructor_id%TYPE;
8293: l_n_uso_id igs_ps_uso_instrctrs.unit_section_occurrence_id%TYPE;
8294: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
8295: l_d_start_dt igs_ps_usec_occurs_all.start_date%TYPE;
8296: l_d_end_dt igs_ps_usec_occurs_all.end_date%TYPE;
8297:
8298: CURSOR c_occurs(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
8503:
8504: PROCEDURE validate_db_cons(p_uso_ins_rec IN OUT NOCOPY igs_ps_generic_pub.uso_ins_rec_type) AS
8505: CURSOR c_unit_ver (cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
8506: SELECT a.unit_cd, a.version_number
8507: FROM igs_ps_unit_ofr_opt_all a, igs_ps_usec_occurs_all b
8508: WHERE a.uoo_id = b.uoo_id
8509: AND b.unit_section_occurrence_id = cp_n_uso_id;
8510:
8511: rec_unit_ver c_unit_ver%ROWTYPE;
8548: fnd_message.set_name('IGS','IGS_PS_INS_PERSON_NUMBER');
8549: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', fnd_message.get, NULL, FALSE);
8550: p_uso_ins_rec.status := 'E';
8551: END IF;
8552: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation (l_n_uoo_id ) THEN
8553: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
8554: p_uso_ins_rec.status := 'E';
8555: END IF;
8556: