[Home] [Help]
1333: END assp_get_suaai_gs;
1334:
1335: FUNCTION getStdntCareerPrograms(
1336: P_PERSON_ID IN IGS_EN_STDNT_PS_ATT.person_id%TYPE ,
1337: P_PROGRAM_TYPE IN IGS_PS_VER_ALL.course_type%TYPE ) RETURN VARCHAR2 IS
1338:
1339: CURSOR c_stud_careers IS
1340: SELECT a.Course_cd, b.Title
1341: FROM IGS_EN_STDNT_PS_ATT_ALL a, IGS_PS_VER_ALL b
1337: P_PROGRAM_TYPE IN IGS_PS_VER_ALL.course_type%TYPE ) RETURN VARCHAR2 IS
1338:
1339: CURSOR c_stud_careers IS
1340: SELECT a.Course_cd, b.Title
1341: FROM IGS_EN_STDNT_PS_ATT_ALL a, IGS_PS_VER_ALL b
1342: WHERE a.course_cd = b.course_cd AND
1343: a.version_number = b.version_number AND
1344: a.course_attempt_status <> 'UNCONFIRM' AND
1345: b.COURSE_TYPE = P_program_type AND
1345: b.COURSE_TYPE = P_program_type AND
1346: a.person_id = P_PERSON_ID /* AND
1347: trunc(a.CREATION_DATE) <= (
1348: SELECT min(trunc(innerpsatt.CREATION_DATE))
1349: FROM IGS_EN_STDNT_PS_ATT_ALL innerpsatt, IGS_PS_VER_ALL innerpsver
1350: WHERE innerpsatt.course_cd = innerpsver.course_cd AND
1351: innerpsatt.version_number = innerpsver.version_number AND
1352: innerpsatt.person_id = P_PERSON_ID AND
1353: innerpsatt.course_attempt_status <> 'UNCONFIRM' AND
1378: P_TERM_END_DATE IN DATE ) RETURN VARCHAR2 IS
1379:
1380: CURSOR c_stud_careers IS
1381: SELECT a.Course_cd, b.Title
1382: FROM IGS_EN_STDNT_PS_ATT_ALL a, IGS_PS_VER_ALL b
1383: WHERE a.course_cd = b.course_cd AND
1384: a.version_number = b.version_number AND
1385: a.course_attempt_status <> 'UNCONFIRM' AND
1386: a.COMMENCEMENT_DT <= p_term_end_date AND
1385: a.course_attempt_status <> 'UNCONFIRM' AND
1386: a.COMMENCEMENT_DT <= p_term_end_date AND
1387: nvl(a.DISCONTINUED_DT , SYSDATE + 100000 ) >= p_term_start_date AND
1388: b.COURSE_TYPE = (SELECT b.course_type
1389: FROM IGS_EN_STDNT_PS_ATT_ALL innerpsatt, IGS_PS_VER_ALL innerpsver
1390: WHERE innerpsatt.course_cd = innerpsver.course_cd AND
1391: innerpsatt.version_number = innerpsver.version_number AND
1392: innerpsatt.person_id = P_PERSON_ID AND
1393: innerpsatt.course_cd = P_COURSE_CD) AND
1411: END getStdntCareerProgsBetween;
1412:
1413: FUNCTION getStdntPrograms(
1414: P_PERSON_ID IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE ,
1415: P_PROGRAM_CD IGS_PS_VER_ALL.COURSE_CD%TYPE ) RETURN VARCHAR2 IS
1416:
1417: CURSOR c_stud_programs IS
1418: SELECT a.person_id ,a.Course_cd, c.Title
1419: FROM IGS_PS_STDNT_TRN a, IGS_EN_STDNT_PS_ATT_ALL b , IGS_PS_VER c
1437:
1438:
1439: Function getStdntProgsBetween(
1440: P_PERSON_ID igs_en_stdnt_ps_att.person_id%type ,
1441: P_program_cd igs_ps_ver_all.course_type%type ,
1442: p_term_start_date DATE ,
1443: p_term_end_date DATE ) return VARCHAR2 is
1444:
1445: CURSOR c_stud_programs IS
1443: p_term_end_date DATE ) return VARCHAR2 is
1444:
1445: CURSOR c_stud_programs IS
1446: SELECT a.course_cd, b.title
1447: FROM igs_ps_stdnt_trn a, igs_ps_ver_all b , IGS_EN_STDNT_PS_ATT_ALL c
1448: WHERE a.TRANSFER_COURSE_CD = c.course_cd
1449: AND a.person_id = c.person_id
1450: AND c.course_cd = b.course_cd
1451: AND c.version_number = b.version_number
1489: IS
1490: SELECT ci.cal_type, ci.sequence_number, ci.start_dt, ci.end_dt,
1491: spa.person_id, spa.course_cd
1492: FROM igs_en_stdnt_ps_att_all spa, igs_ca_inst ci,
1493: igs_ps_ver_all pv
1494: WHERE spa.course_attempt_status <> 'UNCONFIRM'
1495: AND pv.course_cd = spa.course_cd
1496: AND ci.start_dt <= SYSDATE
1497: AND spa.person_id = p_person_id