183: p_hesa_mode := l_hesa_mode ;
184:
185: EXCEPTION
186: WHEN OTHERS THEN
187: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
188: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.coo_type');
189: IGS_GE_MSG_STACK.ADD ;
190: APP_EXCEPTION.RAISE_EXCEPTION;
191:
184:
185: EXCEPTION
186: WHEN OTHERS THEN
187: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
188: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.coo_type');
189: IGS_GE_MSG_STACK.ADD ;
190: APP_EXCEPTION.RAISE_EXCEPTION;
191:
192: END coo_type ;
497:
498:
499: EXCEPTION
500: WHEN OTHERS THEN
501: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
502: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.fte_type_intensity');
503: IGS_GE_MSG_STACK.ADD ;
504: APP_EXCEPTION.RAISE_EXCEPTION ;
505:
498:
499: EXCEPTION
500: WHEN OTHERS THEN
501: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
502: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.fte_type_intensity');
503: IGS_GE_MSG_STACK.ADD ;
504: APP_EXCEPTION.RAISE_EXCEPTION ;
505:
506: END fte_type_intensity ;
522: --Who When What
523: -------------------------------------------------------------------
524: BEGIN
525:
526: FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
527: FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
528: FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
529: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
530:
523: -------------------------------------------------------------------
524: BEGIN
525:
526: FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
527: FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
528: FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
529: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
530:
531: END log_messages ;
524: BEGIN
525:
526: FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
527: FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
528: FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
529: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
530:
531: END log_messages ;
532:
525:
526: FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
527: FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
528: FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
529: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
530:
531: END log_messages ;
532:
533:
1041: l_selection_dt_to := TO_DATE(p_selection_dt_to, 'yyyy/mm/dd hh24:mi:ss');
1042:
1043: IF (l_selection_dt_from IS NULL AND l_selection_dt_to IS NOT NULL) OR
1044: (l_selection_dt_from IS NOT NULL AND l_selection_dt_to IS NULL) THEN
1045: fnd_message.set_name('IGS','IGS_HE_FTE_US_SEL_DT_ERR');
1046: errbuf := fnd_message.get;
1047: fnd_file.put_line(fnd_file.log, fnd_message.get);
1048: retcode := 2 ;
1049: RETURN ;
1042:
1043: IF (l_selection_dt_from IS NULL AND l_selection_dt_to IS NOT NULL) OR
1044: (l_selection_dt_from IS NOT NULL AND l_selection_dt_to IS NULL) THEN
1045: fnd_message.set_name('IGS','IGS_HE_FTE_US_SEL_DT_ERR');
1046: errbuf := fnd_message.get;
1047: fnd_file.put_line(fnd_file.log, fnd_message.get);
1048: retcode := 2 ;
1049: RETURN ;
1050: END IF;
1043: IF (l_selection_dt_from IS NULL AND l_selection_dt_to IS NOT NULL) OR
1044: (l_selection_dt_from IS NOT NULL AND l_selection_dt_to IS NULL) THEN
1045: fnd_message.set_name('IGS','IGS_HE_FTE_US_SEL_DT_ERR');
1046: errbuf := fnd_message.get;
1047: fnd_file.put_line(fnd_file.log, fnd_message.get);
1048: retcode := 2 ;
1049: RETURN ;
1050: END IF;
1051:
1058: CLOSE c_fte_prd ;
1059:
1060: /** logs all the parameters in the LOG **/
1061: --
1062: Fnd_Message.Set_Name('IGS','IGS_FI_ANC_LOG_PARM');
1063: Fnd_File.Put_Line(Fnd_File.LOG,FND_MESSAGE.GET);
1064: log_messages('P_FTE_CAL ',p_fte_cal);
1065: log_messages('P_FTE_START_DT ',p_fte_start_dt);
1066: log_messages('P_FTE_END_DT ',p_fte_end_dt);
1059:
1060: /** logs all the parameters in the LOG **/
1061: --
1062: Fnd_Message.Set_Name('IGS','IGS_FI_ANC_LOG_PARM');
1063: Fnd_File.Put_Line(Fnd_File.LOG,FND_MESSAGE.GET);
1064: log_messages('P_FTE_CAL ',p_fte_cal);
1065: log_messages('P_FTE_START_DT ',p_fte_start_dt);
1066: log_messages('P_FTE_END_DT ',p_fte_end_dt);
1067: log_messages('P_PERSON_ID ',p_person_id);
1126: -- Use library to get dynamic person id group members
1127: l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.IGS_GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status);
1128:
1129: IF l_prs_grp_status <> 'S' THEN
1130: fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
1131: fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
1132: errbuf := fnd_message.get();
1133: fnd_file.put_line(fnd_file.log, errbuf); -- this message need to be displayed to user.
1134: retcode := '2';
1127: l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.IGS_GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status);
1128:
1129: IF l_prs_grp_status <> 'S' THEN
1130: fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
1131: fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
1132: errbuf := fnd_message.get();
1133: fnd_file.put_line(fnd_file.log, errbuf); -- this message need to be displayed to user.
1134: retcode := '2';
1135: RETURN;
1128:
1129: IF l_prs_grp_status <> 'S' THEN
1130: fnd_message.set_name('IGS','IGS_HE_UT_PRSN_ID_GRP_ERR');
1131: fnd_message.set_token('PRSNIDGRP',p_person_id_grp);
1132: errbuf := fnd_message.get();
1133: fnd_file.put_line(fnd_file.log, errbuf); -- this message need to be displayed to user.
1134: retcode := '2';
1135: RETURN;
1136: END IF;
1200: -- check if there are no student programs satisfying the passed parameters
1201: -- fetch a row
1202: IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
1203: DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1204: FND_MESSAGE.SET_NAME('IGS','IGS_UC_HE_NO_DATA');
1205: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1206: RETURN ;
1207:
1208: ELSE
1201: -- fetch a row
1202: IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
1203: DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1204: FND_MESSAGE.SET_NAME('IGS','IGS_UC_HE_NO_DATA');
1205: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1206: RETURN ;
1207:
1208: ELSE
1209:
1206: RETURN ;
1207:
1208: ELSE
1209:
1210: FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_PROC');
1211: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1212: FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------') ;
1213:
1214: -- loop through all the student program attempts and calculate FTE for each program attempt
1207:
1208: ELSE
1209:
1210: FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_PROC');
1211: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1212: FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------') ;
1213:
1214: -- loop through all the student program attempts and calculate FTE for each program attempt
1215: LOOP
1326: -- If the course_cat and coo_id parameter validations have passed
1327: -- then calculate fte for this program attempt
1328: IF NOT l_exit_flag THEN
1329:
1330: FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA');
1331: -- smaddali moved set token to after set message name for bug 2429893
1332: FND_MESSAGE.SET_TOKEN('PERSON_ID',c_sca_rec.person_number);
1333: FND_MESSAGE.SET_TOKEN('COURSE_CD',c_sca_rec.course_cd);
1334: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1328: IF NOT l_exit_flag THEN
1329:
1330: FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA');
1331: -- smaddali moved set token to after set message name for bug 2429893
1332: FND_MESSAGE.SET_TOKEN('PERSON_ID',c_sca_rec.person_number);
1333: FND_MESSAGE.SET_TOKEN('COURSE_CD',c_sca_rec.course_cd);
1334: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1335:
1336: -- check if the program attempt has been intermitted for the whole FTE period then fte=0
1329:
1330: FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA');
1331: -- smaddali moved set token to after set message name for bug 2429893
1332: FND_MESSAGE.SET_TOKEN('PERSON_ID',c_sca_rec.person_number);
1333: FND_MESSAGE.SET_TOKEN('COURSE_CD',c_sca_rec.course_cd);
1334: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1335:
1336: -- check if the program attempt has been intermitted for the whole FTE period then fte=0
1337: IF igs_he_extract_fields_pkg.isDormant
1330: FND_MESSAGE.SET_NAME('IGS','IGS_HE_SPA');
1331: -- smaddali moved set token to after set message name for bug 2429893
1332: FND_MESSAGE.SET_TOKEN('PERSON_ID',c_sca_rec.person_number);
1333: FND_MESSAGE.SET_TOKEN('COURSE_CD',c_sca_rec.course_cd);
1334: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.Get) ;
1335:
1336: -- check if the program attempt has been intermitted for the whole FTE period then fte=0
1337: IF igs_he_extract_fields_pkg.isDormant
1338: (p_person_id => c_sca_rec.person_id,
1365: IF l_message IS NOT NULL OR l_fte_calc_type IS NULL THEN
1366: -- ie current year of program not found / hesa mapping for attendance type not found
1367: -- implies that fte calculation type could not be found
1368:
1369: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_CALC_TYPE') ;
1370: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1371: FND_MESSAGE.SET_NAME('IGS', l_message );
1372: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1373: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1366: -- ie current year of program not found / hesa mapping for attendance type not found
1367: -- implies that fte calculation type could not be found
1368:
1369: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_CALC_TYPE') ;
1370: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1371: FND_MESSAGE.SET_NAME('IGS', l_message );
1372: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1373: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1374: END IF ;
1367: -- implies that fte calculation type could not be found
1368:
1369: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_CALC_TYPE') ;
1370: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1371: FND_MESSAGE.SET_NAME('IGS', l_message );
1372: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1373: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1374: END IF ;
1375:
1368:
1369: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_CALC_TYPE') ;
1370: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1371: FND_MESSAGE.SET_NAME('IGS', l_message );
1372: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1373: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1374: END IF ;
1375:
1376: -- start of unit based calculation
1444: -- get the standard annual load for the program
1445: OPEN c_ann_load (c_sca_rec.course_cd , c_sca_rec.version_number) ;
1446: FETCH c_ann_load INTO l_std_annual_load ;
1447: IF l_std_annual_load IS NULL OR l_std_annual_load = 0 THEN
1448: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_ANN_LOAD');
1449: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1450: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1451:
1452: ELSE
1445: OPEN c_ann_load (c_sca_rec.course_cd , c_sca_rec.version_number) ;
1446: FETCH c_ann_load INTO l_std_annual_load ;
1447: IF l_std_annual_load IS NULL OR l_std_annual_load = 0 THEN
1448: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_ANN_LOAD');
1449: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1450: l_exit_flag := TRUE ; -- skip this current program attempt and go to the next program attempt
1451:
1452: ELSE
1453: l_calculated_FTE := l_calculated_FTE + ( ( l_total_credit_points * 100 ) / l_std_annual_load );
1494: IF l_message IS NOT NULL OR l_fte_intensity IS NULL THEN
1495: -- ie program has both full time and part-time offerings
1496: -- implies that fte intensity could not be found
1497:
1498: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_INTENSITY') ;
1499: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1500: FND_MESSAGE.SET_NAME('IGS', l_message );
1501: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1502: l_exit_flag := TRUE ;
1495: -- ie program has both full time and part-time offerings
1496: -- implies that fte intensity could not be found
1497:
1498: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_INTENSITY') ;
1499: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1500: FND_MESSAGE.SET_NAME('IGS', l_message );
1501: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1502: l_exit_flag := TRUE ;
1503: EXIT ; -- exit the year of program loop and go to the next program attempt
1496: -- implies that fte intensity could not be found
1497:
1498: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_INTENSITY') ;
1499: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1500: FND_MESSAGE.SET_NAME('IGS', l_message );
1501: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1502: l_exit_flag := TRUE ;
1503: EXIT ; -- exit the year of program loop and go to the next program attempt
1504:
1497:
1498: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_INTENSITY') ;
1499: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET );
1500: FND_MESSAGE.SET_NAME('IGS', l_message );
1501: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1502: l_exit_flag := TRUE ;
1503: EXIT ; -- exit the year of program loop and go to the next program attempt
1504:
1505: ELSE
1863: X_MODE => 'R'
1864: ) ;
1865: END IF; -- end of hesa unit set attempt record found
1866: CLOSE c_susa_upd ;
1867: FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_SUCC');
1868: -- smaddali moved set token to after set message name for bug 2429893
1869: FND_MESSAGE.SET_TOKEN('UNIT_SET',c_year_rec.unit_set_cd) ;
1870: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1871: ELSE
1865: END IF; -- end of hesa unit set attempt record found
1866: CLOSE c_susa_upd ;
1867: FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_SUCC');
1868: -- smaddali moved set token to after set message name for bug 2429893
1869: FND_MESSAGE.SET_TOKEN('UNIT_SET',c_year_rec.unit_set_cd) ;
1870: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1871: ELSE
1872: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_YOP');
1873: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1866: CLOSE c_susa_upd ;
1867: FND_MESSAGE.SET_NAME('IGS','IGS_HE_FTE_SUCC');
1868: -- smaddali moved set token to after set message name for bug 2429893
1869: FND_MESSAGE.SET_TOKEN('UNIT_SET',c_year_rec.unit_set_cd) ;
1870: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1871: ELSE
1872: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_YOP');
1873: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1874: END IF ; --end of current year of program found
1868: -- smaddali moved set token to after set message name for bug 2429893
1869: FND_MESSAGE.SET_TOKEN('UNIT_SET',c_year_rec.unit_set_cd) ;
1870: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1871: ELSE
1872: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_YOP');
1873: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1874: END IF ; --end of current year of program found
1875: CLOSE c_year ;
1876:
1869: FND_MESSAGE.SET_TOKEN('UNIT_SET',c_year_rec.unit_set_cd) ;
1870: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1871: ELSE
1872: FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_YOP');
1873: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET) ;
1874: END IF ; --end of current year of program found
1875: CLOSE c_year ;
1876:
1877: END IF; -- end of skip the current program attempt
1897: WHEN OTHERS THEN
1898: ROLLBACK;
1899: retcode :=2;
1900: Fnd_File.Put_Line(FND_FILE.LOG,SQLERRM);
1901: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1902: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.fte_calculation');
1903: Errbuf := FND_MESSAGE.GET;
1904: IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1905:
1898: ROLLBACK;
1899: retcode :=2;
1900: Fnd_File.Put_Line(FND_FILE.LOG,SQLERRM);
1901: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1902: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.fte_calculation');
1903: Errbuf := FND_MESSAGE.GET;
1904: IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1905:
1906: END fte_calculation ;
1899: retcode :=2;
1900: Fnd_File.Put_Line(FND_FILE.LOG,SQLERRM);
1901: FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1902: FND_MESSAGE.Set_Token('NAME','igs_he_fte_calc_pkg.fte_calculation');
1903: Errbuf := FND_MESSAGE.GET;
1904: IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1905:
1906: END fte_calculation ;
1907: