[Home] [Help]
141: RETURN BOOLEAN;
142: --
143: -- Routine to rollover fee cat calendar instances between cal instances
144: FUNCTION finp_ins_roll_fcci(
145: p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
146: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
147: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
148: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
149: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
146: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
147: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
148: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
149: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
150: p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
151: p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
152: p_fee_type IN IGS_FI_FEE_TYPE.fee_type%TYPE ,
153: p_message_name OUT NOCOPY VARCHAR2 ,
154: p_message_warning OUT NOCOPY VARCHAR2 )
350: p_rollover_fee_cat_ci_ind IN VARCHAR ,
351: P_Source_Calendar IN VARCHAR2,
352: P_Dest_Calendar IN VARCHAR2 ,
353: p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
354: p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
355: p_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST_ALL.fee_type_ci_status%TYPE ,
356: p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
357: p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL_ALL.fee_liability_status%TYPE,
358: p_org_id NUMBER
352: P_Dest_Calendar IN VARCHAR2 ,
353: p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
354: p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
355: p_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST_ALL.fee_type_ci_status%TYPE ,
356: p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
357: p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL_ALL.fee_liability_status%TYPE,
358: p_org_id NUMBER
359: ) AS
360: /*----------------------------------------------------------------------------
1171: END finp_ins_roll_ftci;
1172: --
1173: -- Routine to rollover fee cat calendar instances between cal instances
1174: FUNCTION finp_ins_roll_fcci(
1175: p_fee_cat IN IGS_FI_F_CAT_CA_INST.fee_cat%TYPE ,
1176: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1177: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1178: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1179: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1176: p_source_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1177: p_source_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1178: p_dest_cal_type IN IGS_CA_INST.cal_type%TYPE ,
1179: p_dest_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
1180: p_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
1181: p_fee_liability_status IN IGS_FI_F_CAT_FEE_LBL.fee_liability_status%TYPE ,
1182: p_fee_type IN IGS_FI_FEE_TYPE.fee_type%TYPE ,
1183: p_message_name OUT NOCOPY VARCHAR2 ,
1184: p_message_warning OUT NOCOPY VARCHAR2 )
1208: v_message_warning VARCHAR2(30);
1209: v_fcci_inserted_ind BOOLEAN;
1210: v_fcci_exists_ind BOOLEAN;
1211: v_process_next_fcci BOOLEAN;
1212: v_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE;
1213: v_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE;
1214: v_fee_cat_ci_status IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE;
1215: v_valid_dai BOOLEAN;
1216: v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
1210: v_fcci_exists_ind BOOLEAN;
1211: v_process_next_fcci BOOLEAN;
1212: v_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE;
1213: v_fee_type IGS_FI_F_CAT_FEE_LBL.fee_type%TYPE;
1214: v_fee_cat_ci_status IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE;
1215: v_valid_dai BOOLEAN;
1216: v_dummy IGS_CA_DA_INST.dt_alias%TYPE;
1217: l_rowid VARCHAR2(25);
1218:
1224: fcci.end_dt_alias,
1225: fcci.end_dai_sequence_number,
1226: fcci.retro_dt_alias,
1227: fcci.retro_dai_sequence_number
1228: FROM IGS_FI_F_CAT_CA_INST fcci,
1229: IGS_FI_FEE_STR_STAT fss,
1230: IGS_FI_FEE_CAT fc
1231: WHERE (p_fee_cat IS NULL OR
1232: fcci.fee_cat = p_fee_cat) AND
1236: fss.s_fee_structure_status = cst_active AND
1237: fc.fee_cat = fcci.fee_cat AND
1238: fc.closed_ind = 'N';
1239: CURSOR c_fcci (
1240: cp_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE) IS
1241: SELECT fcci.fee_cat
1242: FROM IGS_FI_F_CAT_CA_INST fcci
1243: WHERE fcci.fee_cat= cp_fee_cat AND
1244: fcci.fee_cal_type = p_dest_cal_type AND
1238: fc.closed_ind = 'N';
1239: CURSOR c_fcci (
1240: cp_fee_cat IGS_FI_F_CAT_CA_INST.fee_cat%TYPE) IS
1241: SELECT fcci.fee_cat
1242: FROM IGS_FI_F_CAT_CA_INST fcci
1243: WHERE fcci.fee_cat= cp_fee_cat AND
1244: fcci.fee_cal_type = p_dest_cal_type AND
1245: fcci.fee_ci_sequence_number = p_dest_sequence_number;
1246: CURSOR c_fcfl (
1262: sequence_number = cp_sequence_number AND
1263: cal_type = cp_cal_type AND
1264: ci_sequence_number = cp_ci_sequence_number;
1265: BEGIN
1266: -- This function will roll all IGS_FI_F_CAT_CA_INST records underneath a
1267: -- nominated IGS_CA_INST to beneath another nominated IGS_CA_INST.
1268: -- It also controls the rollover of
1269: -- * fee_refund_schedule
1270: -- * IGS_FI_FEE_RET_SCHD
1272: -- * IGS_FI_F_CAT_FEE_LBL
1273: -- The assumption is being made that the "destination" IGS_CA_INST
1274: -- is open and active - it is the responsibility of the calling routine
1275: -- to check for this.
1276: -- IGS_GE_NOTE: If some of the IGS_FI_F_CAT_CA_INST records already exist then
1277: -- these will remain unaltered.
1278: --------------------------------------------------------------------------
1279: p_message_name := Null;
1280: -- 1. Process the fee cat calendar instance records
1292: IF p_fee_type IS NOT NULL THEN
1293: OPEN c_fcfl(v_fcci_fss_rec.fee_cat);
1294: FETCH c_fcfl INTO v_fee_type;
1295: IF (c_fcfl%NOTFOUND) THEN
1296: -- process next IGS_FI_F_CAT_CA_INST
1297: v_process_next_fcci := TRUE;
1298: END IF;
1299: CLOSE c_fcfl;
1300: END IF;
1298: END IF;
1299: CLOSE c_fcfl;
1300: END IF;
1301: IF (v_process_next_fcci = FALSE) THEN
1302: -- Check for the existence of the IGS_FI_F_CAT_CA_INST
1303: -- record under the destination calendar
1304: OPEN c_fcci(v_fcci_fss_rec.fee_cat);
1305: FETCH c_fcci INTO v_fee_cat;
1306: IF (c_fcci%FOUND) THEN
1388: END IF;
1389: IF (v_process_next_fcci = FALSE AND v_fcci_exists_ind = FALSE) THEN
1390: l_rowid :=NULL;-- initialise l_rowid to null before passing into the TBH
1391: -- l_rowid with a value will throw Un-Handled Exception
1392: IGS_FI_F_CAT_CA_INST_PKG.INSERT_ROW(
1393: X_ROWID=>l_rowid,
1394: X_FEE_CAT=>v_fcci_fss_rec.fee_cat,
1395: X_FEE_CAL_TYPE=>p_dest_cal_type,
1396: X_FEE_CI_SEQUENCE_NUMBER=>p_dest_sequence_number,