[Home] [Help]
244: RETURN BOOLEAN;
245: --
246: -- Routine to rollover IGS_PS_COURSE fee triggers between cal instances
247: FUNCTION finp_ins_roll_cft(
248: p_fee_cat IN IGS_PS_FEE_TRG.fee_cat%TYPE ,
249: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
250: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
251: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
252: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
249: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
250: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
251: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
252: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
253: p_fee_type IN IGS_PS_FEE_TRG.fee_type%TYPE ,
254: p_message_name OUT NOCOPY VARCHAR2 )
255: RETURN BOOLEAN;
256:
257: -- Function to roll over Unit Fee Triggers between Calendar Instances
2946: END finp_ins_roll_cgft;
2947: --
2948: -- Routine to rollover IGS_PS_COURSE fee triggers between cal instances
2949: FUNCTION finp_ins_roll_cft(
2950: p_fee_cat IN IGS_PS_FEE_TRG.fee_cat%TYPE ,
2951: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2952: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2953: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2954: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2951: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2952: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2953: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
2954: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
2955: p_fee_type IN IGS_PS_FEE_TRG.fee_type%TYPE ,
2956: p_message_name OUT NOCOPY VARCHAR2 )
2957: RETURN BOOLEAN AS
2958: /*******************************************************************/
2959: --Change History
2963: /*******************************************************************/
2964: gv_other_detail VARCHAR2(255);
2965: BEGIN
2966: DECLARE
2967: v_course_cd IGS_PS_FEE_TRG.course_cd%TYPE;
2968: v_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE;
2969: v_logical_delete_dt IGS_PS_FEE_TRG.logical_delete_dt%TYPE;
2970: v_record_inserted_ind BOOLEAN;
2971: v_record_exists_ind BOOLEAN;
2964: gv_other_detail VARCHAR2(255);
2965: BEGIN
2966: DECLARE
2967: v_course_cd IGS_PS_FEE_TRG.course_cd%TYPE;
2968: v_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE;
2969: v_logical_delete_dt IGS_PS_FEE_TRG.logical_delete_dt%TYPE;
2970: v_record_inserted_ind BOOLEAN;
2971: v_record_exists_ind BOOLEAN;
2972: v_message_name VARCHAR2(30);
2965: BEGIN
2966: DECLARE
2967: v_course_cd IGS_PS_FEE_TRG.course_cd%TYPE;
2968: v_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE;
2969: v_logical_delete_dt IGS_PS_FEE_TRG.logical_delete_dt%TYPE;
2970: v_record_inserted_ind BOOLEAN;
2971: v_record_exists_ind BOOLEAN;
2972: v_message_name VARCHAR2(30);
2973: l_rowid VARCHAR2(25);
2979: cft.location_cd,
2980: cft.attendance_mode,
2981: cft.attendance_type,
2982: cft.fee_trigger_group_number
2983: FROM IGS_PS_FEE_TRG cft
2984: WHERE cft.fee_cat = p_fee_cat AND
2985: cft.fee_cal_type = p_source_cal_type AND
2986: cft.fee_ci_sequence_number = p_source_sequence_number AND
2987: cft.fee_type = p_fee_type AND
2986: cft.fee_ci_sequence_number = p_source_sequence_number AND
2987: cft.fee_type = p_fee_type AND
2988: cft.logical_delete_dt IS NULL;
2989: CURSOR c_cft_dest (
2990: cp_course_cd IGS_PS_FEE_TRG.course_cd%TYPE,
2991: cp_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE) IS
2992: SELECT cft.course_cd,
2993: cft.sequence_number,
2994: cft.logical_delete_dt
2987: cft.fee_type = p_fee_type AND
2988: cft.logical_delete_dt IS NULL;
2989: CURSOR c_cft_dest (
2990: cp_course_cd IGS_PS_FEE_TRG.course_cd%TYPE,
2991: cp_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE) IS
2992: SELECT cft.course_cd,
2993: cft.sequence_number,
2994: cft.logical_delete_dt
2995: FROM IGS_PS_FEE_TRG cft
2991: cp_sequence_number IGS_PS_FEE_TRG.sequence_number%TYPE) IS
2992: SELECT cft.course_cd,
2993: cft.sequence_number,
2994: cft.logical_delete_dt
2995: FROM IGS_PS_FEE_TRG cft
2996: WHERE cft.fee_cat = p_fee_cat AND
2997: cft.fee_cal_type = p_dest_cal_type AND
2998: cft.fee_ci_sequence_number = p_dest_sequence_number AND
2999: cft.fee_type = p_fee_type AND
2999: cft.fee_type = p_fee_type AND
3000: cft.course_cd = cp_course_cd AND
3001: cft.sequence_number = cp_sequence_number;
3002: BEGIN
3003: -- This function will roll all IGS_PS_FEE_TRG records underneath a
3004: -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
3005: -- The assumption is being made that the "destination" IGS_CA_INST
3006: -- is open and active - it is the responsibility of the calling routine to
3007: -- check for this.
3004: -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
3005: -- The assumption is being made that the "destination" IGS_CA_INST
3006: -- is open and active - it is the responsibility of the calling routine to
3007: -- check for this.
3008: -- IGS_GE_NOTE: If some of the IGS_PS_FEE_TRG records already exist then these will
3009: -- remain unaltered.
3010: p_message_name := Null;
3011: -- 1. Process the IGS_PS_COURSE fee trigger records matching the source calendar
3012: -- instance
3012: -- instance
3013: v_record_inserted_ind := FALSE;
3014: v_record_exists_ind := FALSE;
3015: FOR v_cft_source_rec IN c_cft_source LOOP
3016: -- Check for the existence of the IGS_PS_FEE_TRG
3017: -- record under the destination calendar
3018: OPEN c_cft_dest(
3019: v_cft_source_rec.course_cd,
3020: v_cft_source_rec.sequence_number);
3030: CLOSE c_cft_dest;
3031: BEGIN
3032: l_rowid := NULL; -- initialise l_rowid to null before passing into the TBH
3033: -- l_rowid with a value will throw Un-Handled Exception
3034: IGS_PS_FEE_TRG_PKG.INSERT_ROW(
3035: X_ROWID=>l_rowid,
3036: X_FEE_CAT=>p_fee_cat,
3037: X_FEE_TYPE=>p_fee_type,
3038: X_COURSE_CD=>v_cft_source_rec.course_cd,