[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM igs_pe_persid_group_all
WHERE GROUP_ID = cp_group_id AND file_name IS NULL;
SELECT azg.ROWID row_id,
azg.*
FROM igs_az_groups azg
WHERE (azg.group_name = p_group_name
AND azg.delivery_method_code <> 'SELF')
OR (p_group_name IS NULL
AND azg.auto_refresh_flag = 'Y');
CURSOR cur_stdt_to_be_updated(cp_group_name VARCHAR2,cp_student_person_id NUMBER) IS
SELECT azs.ROWID row_id,
azs.*
FROM igs_az_students azs
where GROUP_NAME = cp_group_name
AND STUDENT_PERSON_ID = cp_student_person_id;
CURSOR cur_advr_to_be_updated(cp_group_name VARCHAR2,cp_advisor_person_id NUMBER) IS
SELECT azs.ROWID row_id,
azs.*
FROM igs_az_advisors azs
where GROUP_NAME = cp_group_name
AND ADVISOR_PERSON_ID = cp_advisor_person_id;
:= ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
|| ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
|| ' MINUS '
|| ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :2 ';
:= ' MINUS SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 ';
:= ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
|| ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
|| ' MINUS '
|| ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :2 ';
:= ' MINUS SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 ';
:= ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
|| ' AND NVL(ACCEPT_DELETE_FLAG, ''N'') = ''N'' '
|| ' MINUS '
|| ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :2 '
|| ' AND NVL(END_DATE, SYSDATE) >= SYSDATE AND trunc(START_DATE) <= trunc(SYSDATE) ';
:= ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
|| ' AND NVL(ACCEPT_DELETE_FLAG, ''N'') = ''N'' MINUS ';
:= ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 '
|| ' MINUS '
|| ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :2 '
|| ' AND NVL(END_DATE, SYSDATE) >= SYSDATE AND trunc(START_DATE) <= trunc(SYSDATE) ';
:= ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 MINUS ';
:= ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
|| ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
|| ' MINUS '
|| ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :2 '
|| ' AND ACCEPT_ADD_FLAG IS NOT NULL';
:= ' MINUS SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
|| ' AND ACCEPT_ADD_FLAG IS NOT NULL';
:= ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
|| ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
|| ' MINUS '
|| ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :2 '
|| ' AND ACCEPT_ADD_FLAG IS NOT NULL';
:= ' MINUS SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 '
|| ' AND ACCEPT_ADD_FLAG IS NOT NULL';
lvStdRowID VARCHAR2 (25); -- ROWID to be passed as parameter to IGS_AZ_STUDENTS_PKG.INSERT_ROW(An out parameter)
lnGrpStdID igs_az_students.group_student_id%TYPE; -- Group Student ID to be passed as parameter to IGS_AZ_STUDENTS_PKG.INSERT_ROW(An out parameter)
lvAdvRowID VARCHAR2 (25); -- ROWID to be passed as parameter to IGS_AZ_ADVISORS_PKG.INSERT_ROW(An out parameter)
lnGrpadvID igs_az_students.group_student_id%TYPE; -- Group Student ID to be passed as parameter to IGS_AZ_ADVISORS_PKG.INSERT_ROW(An out parameter)
p_student_rec cur_stdt_to_be_updated%ROWTYPE;
p_advisor_rec cur_advr_to_be_updated%ROWTYPE;
igs_az_students_pkg.insert_row (
x_rowid => lvStdRowID,
x_group_student_id => lnGrpStdID,
x_group_name => grp_rec.group_name,
x_student_person_id => l_person_id,
x_start_date => ldAddStdStartDate,
x_end_date => NULL,
x_advising_hold_type => NULL,
x_hold_start_date => NULL,
x_notified_date => NULL,
x_accept_add_flag => lvAutoStdAddInd,
x_accept_delete_flag => NULL,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
OPEN cur_stdt_to_be_updated (p_group_name, l_person_id);
FETCH cur_stdt_to_be_updated into p_student_rec;
igs_az_students_pkg.update_row (
x_rowid => p_student_rec.row_id,
x_group_student_id => p_student_rec.group_student_id,
x_group_name => p_student_rec.group_name,
x_student_person_id => p_student_rec.student_person_id,
x_start_date => ldAddStdStartDate,
x_end_date => p_student_rec.end_date,
x_advising_hold_type => p_student_rec.advising_hold_type,
x_hold_start_date => p_student_rec.hold_start_date,
x_notified_date => p_student_rec.notified_date,
x_accept_add_flag => lvAutoStdAddInd,
x_accept_delete_flag => p_student_rec.accept_delete_flag,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
CLOSE cur_stdt_to_be_updated;
igs_az_advisors_pkg.insert_row (
x_rowid => lvAdvRowID,
x_group_advisor_id => lnGrpadvID,
x_group_name => grp_rec.group_name,
x_advisor_person_id => l_person_id,
x_start_date => ldAddAdvStartDate,
x_end_date => NULL,
x_max_students_num => grp_rec.default_advisor_load_num, -- The maximum load initialized to the default load of the group
x_notified_date => NULL,
x_accept_add_flag => lvAutoAdvAddInd,
x_accept_delete_flag => NULL, --Todo uncomment following three parameters once the are added to TBH
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
OPEN cur_advr_to_be_updated (p_group_name, l_person_id);
FETCH cur_advr_to_be_updated into p_advisor_rec;
igs_az_advisors_pkg.update_row(
x_rowid => p_advisor_rec.row_id,
x_group_advisor_id => p_advisor_rec.group_advisor_id,
x_group_name => p_advisor_rec.group_name,
x_advisor_person_id => p_advisor_rec.advisor_person_id,
x_start_date => ldAddAdvStartDate,
x_end_date => p_advisor_rec.end_date,
x_max_students_num => p_advisor_rec.max_students_num,
x_notified_date => SYSDATE, -- This is the only change
x_accept_add_flag => lvAutoAdvAddInd,
x_accept_delete_flag => p_advisor_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
CLOSE cur_advr_to_be_updated;
igs_az_groups_pkg.update_row (
x_rowid => grp_rec.row_id,
x_group_name => grp_rec.group_name,
x_group_desc => grp_rec.group_desc,
x_advising_code => grp_rec.advising_code,
x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
x_resp_person_id => grp_rec.resp_person_id,
x_location_cd => grp_rec.location_cd,
x_delivery_method_code => grp_rec.delivery_method_code,
x_advisor_group_id => grp_rec.advisor_group_id,
x_student_group_id => grp_rec.student_group_id,
x_default_advisor_load_num => grp_rec.default_advisor_load_num,
x_mandatory_flag => grp_rec.mandatory_flag,
x_advising_sessions_num => grp_rec.advising_sessions_num,
x_advising_hold_type => grp_rec.advising_hold_type,
x_closed_flag => grp_rec.closed_flag,
x_comments_txt => grp_rec.comments_txt,
x_auto_refresh_flag => grp_rec.auto_refresh_flag,
x_last_auto_refresh_date => SYSDATE, -- only change.
x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
x_auto_match_flag => grp_rec.auto_match_flag,
x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
x_attribute_category => grp_rec.attribute_category,
x_attribute1 => grp_rec.attribute1,
x_attribute2 => grp_rec.attribute2,
x_attribute3 => grp_rec.attribute3,
x_attribute4 => grp_rec.attribute4,
x_attribute5 => grp_rec.attribute5,
x_attribute6 => grp_rec.attribute6,
x_attribute7 => grp_rec.attribute7,
x_attribute8 => grp_rec.attribute8,
x_attribute9 => grp_rec.attribute9,
x_attribute10 => grp_rec.attribute10,
x_attribute11 => grp_rec.attribute11,
x_attribute12 => grp_rec.attribute12,
x_attribute13 => grp_rec.attribute13,
x_attribute14 => grp_rec.attribute14,
x_attribute15 => grp_rec.attribute15,
x_attribute16 => grp_rec.attribute16,
x_attribute17 => grp_rec.attribute17,
x_attribute18 => grp_rec.attribute18,
x_attribute19 => grp_rec.attribute19,
x_attribute20 => grp_rec.attribute20, --To Do --see if the following three parameters are required.
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
SELECT auto_match_flag
FROM igs_az_groups
WHERE group_name = p_group_name;
SELECT stu.group_student_id,
stu.end_date
FROM igs_az_students stu
WHERE TRUNC (stu.start_date) <= TRUNC (SYSDATE)
AND NVL (stu.end_date, SYSDATE + 1) > SYSDATE
AND stu.group_name = p_group_name
AND NOT EXISTS
(SELECT 1
FROM igs_az_advising_rels rel
WHERE rel.group_name = p_group_name
AND rel.group_student_id = stu.group_student_id);
SELECT adv.group_advisor_id,
adv.end_date,
adv.max_students_num maximum_load,
NVL (rel.actual_load, 0) actual_load,
NVL (rel.actual_load, 0) / NVL (adv.max_students_num, 1) percent_load
FROM igs_az_advisors adv, (SELECT group_advisor_id,
COUNT(*) actual_load
FROM igs_az_advising_rels
WHERE group_name = p_group_name
GROUP BY group_advisor_id) rel
WHERE adv.start_date IS NOT NULL
AND TRUNC (adv.start_date) <= TRUNC (SYSDATE)
AND NVL (adv.end_date, SYSDATE + 1) > SYSDATE
AND adv.group_name = p_group_name
AND rel.group_advisor_id (+) = adv.group_advisor_id
ORDER BY percent_load;
SELECT COUNT (group_student_id)
FROM igs_az_students st
WHERE TRUNC (start_date) <= SYSDATE
AND NVL (end_date, SYSDATE + 1) > SYSDATE
AND group_name = p_group_name
AND NOT EXISTS
(SELECT 1
FROM igs_az_advising_rels rel
WHERE group_name = p_group_name
AND rel.group_student_id = st.group_student_id);
SELECT SUM (max_students_num)
FROM igs_az_advisors
WHERE TRUNC (start_date) <= TRUNC (SYSDATE)
AND NVL (end_date, SYSDATE + 1) > SYSDATE
AND group_name = p_group_name;
SELECT COUNT (*)
FROM igs_az_advising_rels rel
WHERE group_name = p_group_name
AND EXISTS
(SELECT 1
FROM igs_az_advisors adv
WHERE rel.group_advisor_id = adv.group_advisor_id
AND TRUNC (adv.start_date) <= TRUNC (SYSDATE)
AND NVL (adv.end_date, SYSDATE + 1) > SYSDATE
AND adv.group_name = p_group_name);
igs_az_advising_rels_pkg.insert_row (
x_rowid => lv_rowid,
x_group_advising_rel_id => l_group_advising_rel_id,
x_group_name => p_group_name,
x_group_advisor_id => rec_advisors_to_load.group_advisor_id,
x_group_student_id => rec_students_to_match.group_student_id,
x_start_date => ldRelStartDate,
x_end_date => ldRelEndDate,
x_return_status => lvcReturnStatus,
x_msg_data => lvcMsgData,
x_msg_count => lnMsgCount
);
SELECT advising_hold_type
FROM igs_az_groups
WHERE group_name = p_group_name;
SELECT s_encmb_effect_type
FROM igs_fi_enc_dflt_eft
WHERE encumbrance_type = cp_hold_type;
SELECT azs.ROWID row_id,
azs.*,
p.party_number,
p.party_name
FROM igs_az_students azs ,
hz_parties p
WHERE azs.group_name = cp_group_name
AND azs.advising_hold_type IS NULL
AND azs.hold_start_date IS NULL
AND azs.start_date IS NOT NULL
AND TRUNC (azs.start_date) <= TRUNC (SYSDATE)
AND NVL (azs.end_date, TRUNC (SYSDATE+1)) > TRUNC (SYSDATE)
AND p.party_id = azs.student_person_id;
SELECT igs_pe_persenc_effct_seq_num_s.NEXTVAL
FROM dual;
SELECT encumbrance_type,
start_dt
FROM igs_pe_pers_encumb
WHERE encumbrance_type = cp_encumbrance_type
AND person_id = cp_person_id
AND TRUNC (start_dt) <= TRUNC (SYSDATE)
AND NVL (expiry_dt, SYSDATE) >= SYSDATE;
SELECT start_date
FROM igs_az_students
WHERE student_person_id = cp_student_person_id
AND group_name = cp_group_name;
igs_pe_pers_encumb_pkg.insert_row (
x_rowid => lvHoldRowID,
x_person_id => std_rec.student_person_id,
x_encumbrance_type => lvcHoldType,
x_start_dt => ldHldStrtDt,
x_expiry_dt => NULL,
x_authorising_person_id => NULL, -- To do .. Look how this can be populated
x_comments => NULL, -- See if we can use some message here.
x_spo_course_cd => NULL,
x_spo_sequence_number => NULL,
x_cal_type => NULL,
x_sequence_number => NULL,
x_auth_resp_id => NULL,
x_external_reference => NULL
);
igs_pe_persenc_effct_pkg.insert_row (
x_rowid => lvHoldEfctRowID,
x_person_id => std_rec.student_person_id,
x_encumbrance_type => lvcHoldType,
x_pen_start_dt => ldHldStrtDt,
x_s_encmb_effect_type => HldEfct_rec.s_encmb_effect_type,
x_pee_start_dt => ldHldEfctStrtDt,
x_sequence_number => lnpeeseqnum,
x_expiry_dt => NULL,
x_course_cd => NULL,
x_restricted_enrolment_cp => NULL,
x_restricted_attendance_type => NULL
);
igs_az_students_pkg.update_row (
x_rowid => std_rec.row_id,
x_group_student_id => std_rec.group_student_id,
x_group_name => std_rec.group_name,
x_student_person_id => std_rec.student_person_id,
x_start_date => std_rec.start_date,
x_end_date => std_rec.end_date,
x_advising_hold_type => lvcHoldType,
x_hold_start_date => ldHldStrtDt,
x_notified_date => std_rec.notified_date,
x_accept_add_flag => std_rec.accept_add_flag,
x_accept_delete_flag => std_rec.accept_delete_flag ,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
SELECT azs.ROWID AS row_id,
azs.*,
p.party_number,
p.party_name
FROM igs_az_students azs, hz_parties p
WHERE azs.group_name = p_group_name
AND azs.accept_add_flag = 'Y'
AND azs.start_date IS NOT NULL
AND azs.end_date IS NULL
AND azs.notified_date IS NULL
AND p.party_id = azs.student_person_id;
SELECT azs.ROWID AS row_id,
azs.*,
p.party_number,
p.party_name
FROM igs_az_students azs, hz_parties p
WHERE azs.group_name = p_group_name
AND azs.accept_add_flag = 'Y'
AND azs.notified_date IS NULL
AND azs.start_date IS NOT NULL
AND azs.end_date IS NOT NULL
AND p.party_id = azs.student_person_id;
SELECT aza.ROWID AS row_id,
aza.*,
p.party_number,
p.party_name
FROM igs_az_advisors aza, igs_az_groups azg, hz_parties p
WHERE aza.group_name = p_group_name
AND aza.accept_add_flag = 'Y'
AND aza.start_date IS NOT NULL
AND aza.end_date IS NULL
AND aza.notified_date IS NULL
AND azg.group_name = aza.group_name
AND azg.delivery_method_code <>
'SELF' -- To do look for exact lookup code
AND p.party_id = aza.advisor_person_id;
SELECT aza.ROWID AS row_id,
aza.*,
p.party_number,
p.party_name
FROM igs_az_advisors aza, igs_az_groups azg, hz_parties p
WHERE aza.group_name = p_group_name
AND aza.accept_add_flag = 'Y'
AND aza.start_date IS NOT NULL
AND aza.notified_date IS NULL
AND aza.end_date IS NOT NULL
AND azg.group_name = aza.group_name
AND azg.delivery_method_code <>
'SELF' -- To do look for exact lookup code
AND p.party_id = aza.advisor_person_id;
igs_az_students_pkg.update_row(
x_rowid => add_std_rec.row_id,
x_group_student_id => add_std_rec.group_student_id,
x_group_name => add_std_rec.group_name,
x_student_person_id => add_std_rec.student_person_id,
x_start_date => add_std_rec.start_date,
x_end_date => add_std_rec.end_date,
x_advising_hold_type => add_std_rec.advising_hold_type,
x_hold_start_date => add_std_rec.hold_start_date,
x_notified_date => SYSDATE,
x_accept_add_flag => add_std_rec.accept_add_flag,
x_accept_delete_flag => add_std_rec.accept_delete_flag,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
END IF; -- Some student selected for group add notification.
igs_az_students_pkg.update_row(
x_rowid => del_std_rec.row_id,
x_group_student_id => del_std_rec.group_student_id,
x_group_name => del_std_rec.group_name,
x_student_person_id => del_std_rec.student_person_id,
x_start_date => del_std_rec.start_date,
x_end_date => del_std_rec.end_date,
x_advising_hold_type => del_std_rec.advising_hold_type,
x_hold_start_date => del_std_rec.hold_start_date,
x_notified_date => SYSDATE,
x_accept_add_flag => del_std_rec.accept_add_flag,
x_accept_delete_flag => del_std_rec.accept_delete_flag,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
END IF; -- Some student selected for group add notification.
igs_az_advisors_pkg.update_row(
x_rowid => add_adv_rec.row_id,
x_group_advisor_id => add_adv_rec.group_advisor_id,
x_group_name => add_adv_rec.group_name,
x_advisor_person_id => add_adv_rec.advisor_person_id,
x_start_date => add_adv_rec.start_date,
x_end_date => add_adv_rec.end_date,
x_max_students_num => add_adv_rec.max_students_num,
x_notified_date => SYSDATE, -- This is the only change
x_accept_add_flag => add_adv_rec.accept_add_flag,
x_accept_delete_flag => add_adv_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
END IF; -- Some advisor selected for group add notification.
igs_az_advisors_pkg.update_row(
x_rowid => del_adv_rec.row_id,
x_group_advisor_id => del_adv_rec.group_advisor_id,
x_group_name => del_adv_rec.group_name,
x_advisor_person_id => del_adv_rec.advisor_person_id,
x_start_date => del_adv_rec.start_date,
x_end_date => del_adv_rec.end_date,
x_max_students_num => del_adv_rec.max_students_num,
x_notified_date => SYSDATE, -- This is the only change
x_accept_add_flag => del_adv_rec.accept_add_flag,
x_accept_delete_flag => del_adv_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
END IF; -- Some student selected for group add notification.
SELECT igs_as_wf_beas006_s.NEXTVAL
FROM DUAL;
l_parameter_list_t.DELETE;
SELECT aza.ROWID row_id,
aza.*
FROM igs_az_advisors aza
WHERE aza.group_name = p_group_name
AND aza.advisor_person_id = p_advisor_person_id
AND NVL (aza.accept_add_flag, 'Y') = 'Y';
SELECT aza.ROWID row_id
FROM igs_az_advisors aza
WHERE aza.group_name = p_group_name
AND aza.advisor_person_id = p_advisor_person_id
AND aza.accept_add_flag = 'N';
SELECT azr.ROWID row_id,
azr.*
FROM igs_az_advising_rels azr, igs_az_advisors aza
WHERE azr.group_name = p_group_name
AND azr.group_name = aza.group_name
AND azr.group_advisor_id = aza.group_advisor_id
AND aza.advisor_person_id = p_advisor_person_id
AND azr.start_date IS NOT NULL
AND TRUNC (NVL (azr.end_date, SYSDATE + 1)) > TRUNC (SYSDATE);
SELECT azr.ROWID row_id
FROM igs_az_advising_rels azr, igs_az_advisors aza
WHERE azr.group_name = p_group_name
AND azr.group_name = aza.group_name
AND azr.group_advisor_id = aza.group_advisor_id
AND aza.advisor_person_id = p_advisor_person_id
AND azr.start_date IS NULL;
SELECT auto_advisor_remove_flag
FROM igs_az_groups azg
WHERE azg.group_name = p_group_name;
igs_az_advising_rels_pkg.update_row (
x_rowid => reln_rec.row_id,
x_group_advising_rel_id => reln_rec.group_advising_rel_id,
x_group_name => reln_rec.group_name,
x_group_advisor_id => reln_rec.group_advisor_id,
x_group_student_id => reln_rec.group_student_id,
x_start_date => reln_rec.start_date,
x_end_date => lddeladvdate,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
); -- To do see if msg count etc. is require , if yes add and Error Handling
igs_az_advising_rels_pkg.delete_row (
x_rowid => del_reln.row_id,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
igs_az_advisors_pkg.update_row (
x_rowid => adv_upd_rec.row_id,
x_group_advisor_id => adv_upd_rec.group_advisor_id,
x_group_name => adv_upd_rec.group_name,
x_advisor_person_id => adv_upd_rec.advisor_person_id,
x_start_date => adv_upd_rec.start_date,
x_end_date => lddeladvdate, -- This is only changed..
x_max_students_num => adv_upd_rec.max_students_num,
x_notified_date => adv_upd_rec.notified_date, -- This is the only change
x_accept_add_flag => adv_upd_rec.accept_add_flag,
x_accept_delete_flag => lvadvdelind, ---To do Follwing three parameters need to be added in the TBH and then uncomment
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
); -- To Do error handling..
igs_az_advisors_pkg.delete_row (
adv_del_rec.row_id,
lvReturnStatus,
lvMsgData,
lnMsgCount
);
SELECT azs.ROWID row_id,
azs.*
FROM igs_az_students azs
WHERE azs.group_name = p_group_name
AND azs.student_person_id = p_student_person_id
AND NVL (azs.accept_add_flag, 'Y') = 'Y';
SELECT azs.ROWID row_id
FROM igs_az_students azs
WHERE azs.group_name = p_group_name
AND azs.student_person_id = p_student_person_id
AND azs.accept_add_flag = 'N';
SELECT azr.ROWID row_id,
azr.*
FROM igs_az_advising_rels azr, igs_az_students azs
WHERE azr.group_name = p_group_name
AND azr.group_name = azs.group_name
AND azr.group_student_id = azs.group_student_id
AND azs.student_person_id = p_student_person_id
AND azr.start_date IS NOT NULL
AND TRUNC (NVL (azr.end_date, SYSDATE + 1)) > TRUNC (SYSDATE);
SELECT auto_stdnt_remove_flag
FROM igs_az_groups azg
WHERE azg.group_name = p_group_name;
SELECT azr.ROWID row_id
FROM igs_az_advising_rels azr, igs_az_students azs
WHERE azr.group_name = p_group_name
AND azr.group_name = azs.group_name
AND azr.group_student_id = azs.group_student_id
AND azs.student_person_id = p_student_person_id
AND azr.start_date IS NULL;
igs_az_advising_rels_pkg.update_row (
x_rowid => reln_rec.row_id,
x_group_advising_rel_id => reln_rec.group_advising_rel_id,
x_group_name => reln_rec.group_name,
x_group_advisor_id => reln_rec.group_advisor_id,
x_group_student_id => reln_rec.group_student_id,
x_start_date => reln_rec.start_date,
x_end_date => lddelstddate,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
); -- To do see if msg count etc. is require , if yes add and Error Handling also to do See how we can verify that the end date is earliest of either the student or the advisor end date if any.
igs_az_advising_rels_pkg.delete_row (
x_rowid => del_reln.row_id,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
);
IF p_calling_mod = 'C' THEN -- This is because from the ss pages, there will already be call to update row for this table.
igs_az_students_pkg.update_row (
x_rowid => std_upd_rec.row_id,
x_group_student_id => std_upd_rec.group_student_id,
x_group_name => std_upd_rec.group_name,
x_student_person_id => std_upd_rec.student_person_id,
x_start_date => std_upd_rec.start_date,
x_end_date => lddelstddate, -- this is the only change..
x_advising_hold_type => std_upd_rec.advising_hold_type,
x_hold_start_date => std_upd_rec.hold_start_date,
x_notified_date => std_upd_rec.notified_date,
x_accept_add_flag => std_upd_rec.accept_add_flag,
x_accept_delete_flag => lvstddelind,
x_return_status => lvReturnStatus,
x_msg_data => lvMsgData,
x_msg_count => lnMsgCount
); -- To Do error handling..
igs_az_students_pkg.delete_row (
std_del_rec.row_id,
lvReturnStatus,
lvMsgData,
lnMsgCount
);
SELECT azs.ROWID row_id,
azs.*
FROM igs_az_students azs
WHERE azs.group_name = p_group_name AND azs.student_person_id = p_person_id;
SELECT hld.ROWID row_id,
hld.*
FROM igs_pe_pers_encumb hld
WHERE hld.person_id = p_person_id
AND hld.encumbrance_type = cp_hold_type
AND hld.start_dt = cp_start_date; --AND hld.EXPIRY_DT IS NULL ;
SELECT efc.ROWID row_id,
efc.*
FROM igs_pe_persenc_effct efc
WHERE efc.person_id = p_person_id
AND efc.encumbrance_type = cp_hold_type
AND efc.pen_start_dt = cp_start_date; --to do see if there needs to be a check on expiry date also..
igs_pe_persenc_effct_pkg.update_row (
x_rowid => effect_rec.row_id,
x_person_id => effect_rec.person_id,
x_encumbrance_type => effect_rec.encumbrance_type,
x_pen_start_dt => effect_rec.pen_start_dt,
x_s_encmb_effect_type => effect_rec.s_encmb_effect_type,
x_pee_start_dt => effect_rec.pee_start_dt,
x_sequence_number => effect_rec.sequence_number,
x_expiry_dt => p_hld_end_dt, -- Only Change
x_course_cd => effect_rec.course_cd,
x_restricted_enrolment_cp => effect_rec.restricted_enrolment_cp,
x_restricted_attendance_type => effect_rec.restricted_attendance_type
);
igs_pe_pers_encumb_pkg.update_row (
x_rowid => hold_rec.row_id,
x_person_id => hold_rec.person_id,
x_encumbrance_type => hold_rec.encumbrance_type,
x_start_dt => hold_rec.start_dt,
x_expiry_dt => p_hld_end_dt,
x_authorising_person_id => hold_rec.authorising_person_id,
x_comments => hold_rec.comments,
x_spo_course_cd => hold_rec.spo_course_cd,
x_spo_sequence_number => hold_rec.spo_sequence_number,
x_cal_type => hold_rec.cal_type,
x_sequence_number => hold_rec.sequence_number,
x_auth_resp_id => hold_rec.auth_resp_id,
x_external_reference => hold_rec.external_reference
);
SELECT user_name
FROM fnd_user
WHERE person_party_id = cp_person_id;
SELECT COUNT (1)
FROM wf_local_user_roles
WHERE user_name = cp_user_name
AND role_name = cp_role_name
AND role_orig_system = 'WF_LOCAL_ROLES'
AND role_orig_system_id = 0;
SELECT azg.ROWID row_id,
azg.*
FROM igs_az_groups azg
WHERE azg.group_name = p_group_name;
SELECT advisor_person_id
FROM igs_az_advisors aza
WHERE aza.group_name = p_group_name;
SELECT student_person_id
FROM igs_az_students azs
WHERE azs.group_name = p_group_name;
igs_az_groups_pkg.update_row (
x_rowid => grp_rec.row_id,
x_group_name => grp_rec.group_name,
x_group_desc => grp_rec.group_desc,
x_advising_code => grp_rec.advising_code,
x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
x_resp_person_id => grp_rec.resp_person_id,
x_location_cd => grp_rec.location_cd,
x_delivery_method_code => grp_rec.delivery_method_code,
x_advisor_group_id => grp_rec.advisor_group_id,
x_student_group_id => grp_rec.student_group_id,
x_default_advisor_load_num => grp_rec.default_advisor_load_num,
x_mandatory_flag => grp_rec.mandatory_flag,
x_advising_sessions_num => grp_rec.advising_sessions_num,
x_advising_hold_type => grp_rec.advising_hold_type,
x_closed_flag => 'Y',
x_comments_txt => grp_rec.comments_txt,
x_auto_refresh_flag => grp_rec.auto_refresh_flag,
x_last_auto_refresh_date => grp_rec.last_auto_refresh_date,
x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
x_auto_match_flag => grp_rec.auto_match_flag,
x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
x_attribute_category => grp_rec.attribute_category,
x_attribute1 => grp_rec.attribute1,
x_attribute2 => grp_rec.attribute2,
x_attribute3 => grp_rec.attribute3,
x_attribute4 => grp_rec.attribute4,
x_attribute5 => grp_rec.attribute5,
x_attribute6 => grp_rec.attribute6,
x_attribute7 => grp_rec.attribute7,
x_attribute8 => grp_rec.attribute8,
x_attribute9 => grp_rec.attribute9,
x_attribute10 => grp_rec.attribute10,
x_attribute11 => grp_rec.attribute11,
x_attribute12 => grp_rec.attribute12,
x_attribute13 => grp_rec.attribute13,
x_attribute14 => grp_rec.attribute14,
x_attribute15 => grp_rec.attribute15,
x_attribute16 => grp_rec.attribute16,
x_attribute17 => grp_rec.attribute17,
x_attribute18 => grp_rec.attribute18,
x_attribute19 => grp_rec.attribute19,
x_attribute20 => grp_rec.attribute20,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count
);
SELECT azg.ROWID row_id,
azg.*
FROM igs_az_groups azg
WHERE azg.group_name = p_group_name;
igs_az_groups_pkg.update_row (
x_rowid => grp_rec.row_id,
x_group_name => grp_rec.group_name,
x_group_desc => grp_rec.group_desc,
x_advising_code => grp_rec.advising_code,
x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
x_resp_person_id => grp_rec.resp_person_id,
x_location_cd => grp_rec.location_cd,
x_delivery_method_code => grp_rec.delivery_method_code,
x_advisor_group_id => grp_rec.advisor_group_id,
x_student_group_id => grp_rec.student_group_id,
x_default_advisor_load_num => grp_rec.default_advisor_load_num,
x_mandatory_flag => grp_rec.mandatory_flag,
x_advising_sessions_num => grp_rec.advising_sessions_num,
x_advising_hold_type => grp_rec.advising_hold_type,
x_closed_flag => 'N',
x_comments_txt => grp_rec.comments_txt,
x_auto_refresh_flag => grp_rec.auto_refresh_flag,
x_last_auto_refresh_date => grp_rec.last_auto_refresh_date,
x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
x_auto_match_flag => grp_rec.auto_match_flag,
x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
x_attribute_category => grp_rec.attribute_category,
x_attribute1 => grp_rec.attribute1,
x_attribute2 => grp_rec.attribute2,
x_attribute3 => grp_rec.attribute3,
x_attribute4 => grp_rec.attribute4,
x_attribute5 => grp_rec.attribute5,
x_attribute6 => grp_rec.attribute6,
x_attribute7 => grp_rec.attribute7,
x_attribute8 => grp_rec.attribute8,
x_attribute9 => grp_rec.attribute9,
x_attribute10 => grp_rec.attribute10,
x_attribute11 => grp_rec.attribute11,
x_attribute12 => grp_rec.attribute12,
x_attribute13 => grp_rec.attribute13,
x_attribute14 => grp_rec.attribute14,
x_attribute15 => grp_rec.attribute15,
x_attribute16 => grp_rec.attribute16,
x_attribute17 => grp_rec.attribute17,
x_attribute18 => grp_rec.attribute18,
x_attribute19 => grp_rec.attribute19,
x_attribute20 => grp_rec.attribute20,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count
);