[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE igs_da_rqst
SET request_status = 'COMPLETE_ERROR'
WHERE batch_id = p_batch_id;
UPDATE igs_da_req_stdnts
SET report_text = v_report_text,
ERROR_CODE = l_error_code
WHERE batch_id = p_batch_id
AND person_id = p_person_id
AND program_code = p_program_code;
SELECT hp.ROWID row_id
FROM hz_parties p, igs_pe_hz_parties hp
WHERE hp.party_id = p.party_id
AND hp.inst_org_ind = 'I'
AND p.party_number = person_history_rec.prev_institution_code;
SELECT hp.ROWID row_id
FROM hz_parties p,
igs_pe_hz_parties hp,
igs_or_org_inst_type_all oit
WHERE hp.party_id = p.party_id
AND hp.inst_org_ind = 'I'
AND p.party_number = person_history_rec.prev_institution_code
AND hp.oi_institution_type = oit.institution_type(+)
AND oit.system_inst_type IN ('POST-SECONDARY', 'SECONDARY');
write_message ('***** IGS_AD_ACAD_HISTORY_PKG.INSERT_ROW *****'
);
igs_ad_acad_history_pkg.insert_row (x_rowid => l_rowid,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_attribute13 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_education_id => l_education_id,
x_person_id => person_history_rec.person_id,
x_current_inst => 'N',
x_degree_attempted => NULL,
x_program_code => NULL,
x_degree_earned => NULL,
x_comments => NULL,
x_start_date => TO_DATE (NULL
),
x_end_date => TO_DATE (NULL
),
x_planned_completion_date => TO_DATE (NULL
),
x_recalc_total_cp_attempted => NULL,
x_recalc_total_cp_earned => NULL,
x_recalc_total_unit_gp => NULL,
x_recalc_tot_gpa_units_attemp => NULL,
x_recalc_inst_gpa => NULL,
x_recalc_grading_scale_id => NULL,
x_selfrep_total_cp_attempted => NULL,
x_selfrep_total_cp_earned => NULL,
x_selfrep_total_unit_gp => NULL,
x_selfrep_tot_gpa_uts_attemp => NULL,
x_selfrep_inst_gpa => NULL,
x_selfrep_grading_scale_id => NULL,
x_selfrep_weighted_gpa => NULL,
x_selfrep_rank_in_class => NULL,
x_selfrep_weighed_rank => NULL,
x_type_of_school => NULL,
x_institution_code => person_history_rec.prev_institution_code,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_selfrep_class_size => NULL,
x_transcript_required => 'Y',
x_status => 'A',
x_object_version_number => l_object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R'
);
SELECT ah.*
FROM igs_ad_acad_history_v ah
WHERE ( person_id = acad_hist_rec.person_id
AND institution_code = acad_hist_rec.prev_institution_code
);
SELECT h1.education_id
FROM hz_education h1, hz_parties h2
WHERE h1.party_id = acad_hist_rec.person_id
AND h2.party_number = acad_hist_rec.prev_institution_code
AND h2.party_id = h1.school_party_id
ORDER BY h1.creation_date DESC;
PROCEDURE delete_adv_stnd_records (p_person_id IN hz_parties.party_id%TYPE)
AS
CURSOR c_edu_id
IS
SELECT education_id
FROM hz_education
WHERE party_id = p_person_id;
SELECT trans_oss.ROWID, trans_oss.*
FROM igs_ad_transcript trans_oss
WHERE transcript_source IN (
SELECT code_id
FROM igs_ad_code_classes
WHERE CLASS = 'TRANSCRIPT_SOURCE'
AND closed_ind = 'N'
AND system_status = 'THIRD_PARTY_TRANSFER_EVAL')
AND education_id = cp_education_id
FOR UPDATE NOWAIT;
SELECT term_oss.ROWID, term_oss.*
FROM igs_ad_term_details term_oss
WHERE transcript_id = p_transcript_id
FOR UPDATE NOWAIT;
SELECT unit_oss.ROWID, unit_oss.*
FROM igs_ad_term_unitdtls unit_oss
WHERE term_details_id = p_term_details_id
FOR UPDATE NOWAIT;
SELECT unt.ROWID, unt.*
FROM igs_av_stnd_unit_all unt
WHERE unit_details_id = p_unit_details_id
FOR UPDATE NOWAIT;
SELECT adv.ROWID, adv.*
FROM igs_av_adv_standing_all adv
WHERE p_person_id = adv.person_id
FOR UPDATE NOWAIT;
SELECT unt.ROWID, unt.*
FROM igs_av_stnd_unit_all unt
WHERE p_person_id = unt.person_id
FOR UPDATE NOWAIT;
SELECT unt.ROWID, unt.*
FROM IGS_AV_STD_UNT_BASIS_ALL unt
WHERE unt.AV_STND_UNIT_ID = cp_AV_STND_UNIT_ID
FOR UPDATE NOWAIT;
ecx_debug.push ('IGS_DA_TRNS_IMP.DELETE_ADV_STND_RECORDS');
IGS_AV_STD_UNT_BASIS_PKG.delete_row (l__adv_unt_basis.ROWID);
igs_av_stnd_unit_pkg.delete_row (l_adv_stnd_unt.ROWID);
igs_ad_term_unitdtls_pkg.delete_row (l_term_unit.ROWID);
igs_ad_term_details_pkg.delete_row (l_trans_term.ROWID);
igs_ad_transcript_pkg.delete_row (l_trans.ROWID);
IGS_AV_STD_UNT_BASIS_PKG.delete_row (l_adv_unt_basis.ROWID);
igs_av_stnd_unit_pkg.delete_row (l_adv_stnd_unt.ROWID);
igs_av_adv_standing_pkg.delete_row (l_adv_stnd.ROWID);
ecx_debug.pop ('IGS_DA_TRNS_IMP.DELETE_ADV_STND_RECORDS');
END delete_adv_stnd_records;
SELECT code_id
FROM igs_ad_code_classes
WHERE CLASS = 'TRANSCRIPT_SOURCE'
AND closed_ind = 'N'
AND system_status = 'THIRD_PARTY_TRANSFER_EVAL'
ORDER BY NVL (system_default, 'A') DESC;
write_message ('***** IGS_AD_TRANSCRIPT_PKG.INSERT_ROW *****');
igs_ad_transcript_pkg.insert_row (x_rowid => l_rowid,
x_quintile_rank => NULL,
x_percentile_rank => NULL,
x_transcript_id => l_transcript_id,
x_education_id => p_trans_record.education_id,
x_transcript_status => 'FINAL',
x_transcript_source => l_transcript_source,
x_date_of_receipt => TRUNC (SYSDATE
),
x_entered_gpa => NULL,
x_entered_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
),
x_conv_gpa => NULL,
x_conv_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
),
x_term_type => p_trans_record.term_type,
x_rank_in_class => NULL,
x_class_size => NULL,
x_approximate_rank => NULL,
x_weighted_rank => NULL,
x_decile_rank => NULL,
x_quartile_rank => NULL,
x_transcript_type => 'OFFICIAL',
x_mode => 'R',
x_date_of_issue => TRUNC (SYSDATE
),
x_override => NVL (override_ind,
'N'
),
x_override_id => NULL,
x_override_date => NULL
);
write_log ('igs_ad_transcript_pkg.insert_row',
'igs.plsql.igs_da_trns_imp.create_new_transcript_details'
);
PROCEDURE update_transcript_details (
p_trans_record IN OUT NOCOPY trans_cur_rec
)
AS
l_transcript_id igs_ad_txcpt_int.transcript_id%TYPE;
SELECT trans_oss.ROWID row_id, trans_oss.*
FROM igs_ad_transcript trans_oss
WHERE ( transcript_id = p_trans_record.transcript_id
AND p_trans_record.transcript_id IS NOT NULL
)
OR ( p_trans_record.transcript_id IS NULL
AND education_id = p_trans_record.education_id
);
SELECT code_id
FROM igs_ad_code_classes
WHERE CLASS = 'TRANSCRIPT_SOURCE'
AND closed_ind = 'N'
AND system_status = 'THIRD_PARTY_TRANSFER_EVAL';
ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
SAVEPOINT before_update_transcript;
igs_ad_transcript_pkg.update_row (x_rowid => dup_cur_rec.row_id,
x_quintile_rank => dup_cur_rec.quintile_rank,
x_percentile_rank => dup_cur_rec.percentile_rank,
x_transcript_id => dup_cur_rec.transcript_id,
x_education_id => dup_cur_rec.education_id,
x_transcript_status => dup_cur_rec.transcript_status,
x_transcript_source => l_source,
x_date_of_receipt => TRUNC (SYSDATE
),
x_entered_gpa => p_trans_record.unit_grade_points,
x_entered_gs_id => fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE'
),
x_conv_gpa => dup_cur_rec.conv_gpa,
x_conv_gs_id => dup_cur_rec.conv_gs_id,
x_term_type => p_trans_record.term_type,
x_rank_in_class => dup_cur_rec.rank_in_class,
x_class_size => dup_cur_rec.class_size,
x_approximate_rank => dup_cur_rec.approximate_rank,
x_weighted_rank => dup_cur_rec.weighted_rank,
x_decile_rank => dup_cur_rec.decile_rank,
x_quartile_rank => dup_cur_rec.quartile_rank,
x_transcript_type => dup_cur_rec.transcript_type,
x_date_of_issue => dup_cur_rec.date_of_issue,
x_override => NULL,
x_override_id => NULL,
x_override_date => NULL
);
write_log ('Update trans details',
'igs.plsql.igs_da_trns_imp.update_transcript_details'
);
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TRANSCRIPT_DETAILS');
'igs.plsql.igs_da_trns_imp.update_transcript_details'
);
'igs.plsql.igs_da_trns_imp.update_transcript_details'
);
'igs.plsql.igs_da_trns_imp.update_transcript_details'
);
END update_transcript_details;
PROCEDURE update_term_unit_details (
p_term_unitdtls_record IN OUT NOCOPY trans_cur_rec
)
AS
l_rowid VARCHAR2 (25);
SELECT unit_oss.ROWID, unit_oss.*
FROM igs_ad_term_unitdtls unit_oss
WHERE term_details_id = p_term_unitdtls_record.term_details_id
AND unit = p_term_unitdtls_record.unit;
ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
SAVEPOINT before_update_unit;
igs_ad_term_unitdtls_pkg.update_row (x_rowid => dup_cur_rec.ROWID,
x_unit_details_id => dup_cur_rec.unit_details_id,
x_term_details_id => dup_cur_rec.term_details_id,
x_unit => p_term_unitdtls_record.unit,
x_unit_difficulty => dup_cur_rec.unit_difficulty,
x_unit_name => p_term_unitdtls_record.unit_name,
x_cp_attempted => p_term_unitdtls_record.cp_attempted,
x_cp_earned => p_term_unitdtls_record.cp_earned,
x_grade => p_term_unitdtls_record.grade,
x_unit_grade_points => p_term_unitdtls_record.unit_grade_points
);
write_log ( 'igs_ad_term_unitdtls_pkg.update_row unit_details_id='
|| p_term_unitdtls_record.unit_details_id,
'igs.plsql.igs_da_trns_imp.update_term_unit_details'
);
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_UNIT_DETAILS');
'igs.plsql.igs_da_trns_imp.update_term_unit_details'
);
'igs.plsql.igs_da_trns_imp.update_term_unit_details'
);
'igs.plsql.igs_da_trns_imp.update_term_unit_details'
);
END update_term_unit_details;
SELECT code_id
FROM igs_ad_code_classes
WHERE CLASS = 'UNIT_DIFFICULTY' AND NAME = 'STANDARD';
write_log ('Before igs_ad_term_unitdtls_pkg.insert_row',
'igs.plsql.igs_da_trns_imp.create_term_unit_details'
);
write_message ('***** IGS_AD_TERM_UNITDTLS_PKG.INSERT_ROW *****');
igs_ad_term_unitdtls_pkg.insert_row (l_rowid,
l_unit_details_id,
p_term_unitdtls_record.term_details_id,
p_term_unitdtls_record.unit,
l_unit_difficulty,
p_term_unitdtls_record.unit_name,
p_term_unitdtls_record.cp_attempted,
p_term_unitdtls_record.cp_earned,
p_term_unitdtls_record.grade,
p_term_unitdtls_record.unit_grade_points
);
write_log ( 'After igs_ad_term_unitdtls_pkg.insert_row l_unit_details_id='
|| l_unit_details_id,
'igs.plsql.igs_da_trns_imp.create_term_unit_details'
);
SELECT unit_oss.ROWID, unit_oss.*
FROM igs_ad_term_unitdtls unit_oss
WHERE term_details_id = p_trans_cur_rec.term_details_id
AND unit = p_trans_cur_rec.unit
AND unit_name = p_trans_cur_rec.unit_name;
write_log ('Entering update_term_unit_details',
'igs.plsql.igs_da_trns_imp.process_term_unit_details'
);
update_term_unit_details (p_trans_cur_rec);
write_message ('***** IGS_AD_TERM_DETAILS_PKG.INSERT_ROW *****');
igs_ad_term_details_pkg.insert_row (l_rowid,
l_term_details_id,
p_term_dtls_record.transcript_id,
p_term_dtls_record.term,
TRUNC (p_term_dtls_record.start_date
),
TRUNC (p_term_dtls_record.end_date
),
NULL,
NULL,
NULL,
TO_NUMBER (NULL),
--p_term_dtls_record.total_gpa_units,
TO_NUMBER (NULL)
--p_term_dtls_record.gpa
);
PROCEDURE update_term_details (
p_term_dtls_record IN OUT NOCOPY trans_cur_rec
)
AS
l_msg_at_index NUMBER := 0;
:= 'igs.plsql.igs_da_trns_imp.update_term_details';
SELECT term_oss.ROWID, term_oss.*
FROM igs_ad_term_details term_oss
WHERE transcript_id = p_term_dtls_record.transcript_id
AND term = p_term_dtls_record.term
AND TRUNC (start_date) = TRUNC (p_term_dtls_record.start_date)
AND TRUNC (end_date) = TRUNC (p_term_dtls_record.end_date);
ecx_debug.push ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
SAVEPOINT before_update_term;
igs_ad_term_details_pkg.update_row (x_rowid => dup_cur_rec.ROWID,
x_term_details_id => dup_cur_rec.term_details_id,
x_transcript_id => dup_cur_rec.transcript_id,
x_term => p_term_dtls_record.term,
x_start_date => TRUNC (p_term_dtls_record.start_date
),
x_end_date => TRUNC (p_term_dtls_record.end_date
),
x_total_cp_attempted => dup_cur_rec.total_cp_attempted,
--dup_cur_rec.total_cp_attempted,
x_total_cp_earned => dup_cur_rec.total_cp_earned,
--p_term_dtls_record.total_cp_earned,
x_total_unit_gp => dup_cur_rec.total_unit_gp,
--p_term_dtls_record.total_unit_gp,
x_total_gpa_units => dup_cur_rec.total_gpa_units,
--p_term_dtls_record.total_gpa_units
x_gpa => dup_cur_rec.gpa
);
write_log ('igs_ad_term_details_pkg.update_row',
'igs.plsql.igs_da_trns_imp.update_term_details'
);
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
ecx_debug.pop ('IGS_DA_TRNS_IMP.UPDATE_TERM_DETAILS');
'igs.plsql.igs_da_trns_imp.update_term_details'
);
'igs.plsql.igs_ad_imp_024.update_term_details.exception '
|| l_msg_data;
'igs.plsql.igs_da_trns_imp.update_term_details'
);
write_log ('igs_da_trns_imp.update_term_details',
'igs.plsql.igs_da_trns_imp.update_term_details'
);
END update_term_details;
SELECT term_oss.ROWID, term_oss.*
FROM igs_ad_term_details term_oss
WHERE transcript_id = p_trans_cur_rec.transcript_id
AND term = p_trans_cur_rec.term
AND TRUNC (start_date) = TRUNC (p_trans_cur_rec.start_date)
AND TRUNC (end_date) = TRUNC (p_trans_cur_rec.end_date);
update_term_details (p_trans_cur_rec);
'igs.plsql.igs_da_trns_imp.update_term_details'
);
SELECT trans_oss.ROWID, trans_oss.*
FROM igs_ad_transcript trans_oss
WHERE trans_oss.term_type = l_trans_cur_rec.term_type
AND ( ( transcript_id = l_trans_cur_rec.transcript_id
AND l_trans_cur_rec.transcript_id IS NOT NULL
)
OR ( l_trans_cur_rec.transcript_id IS NULL
AND education_id = l_trans_cur_rec.education_id
-- AND TRUNC (date_of_issue) = TRUNC (SYSDATE)
)
)
ORDER BY last_update_date DESC;
SELECT hz.ROWID, hz.*
FROM hz_education hz
WHERE hz.party_id = cp_person_id
AND hz.school_party_id = p_school_party_id
ORDER BY hz.last_update_date DESC;
SELECT transcript_id
FROM igs_ad_transcript
WHERE education_id = l_trans_cur_rec.education_id
AND transcript_status = 'FINAL'
AND transcript_source IN (
SELECT code_id
FROM igs_ad_code_classes
WHERE CLASS = 'TRANSCRIPT_SOURCE'
AND closed_ind = 'N'
AND system_status = 'THIRD_PARTY_TRANSFER_EVAL')
AND entered_gs_id = fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE')
AND conv_gs_id = fnd_profile.VALUE ('IGS_AD_INST_GRAD_SCALE')
AND term_type = l_trans_cur_rec.term_type
AND transcript_type = 'OFFICIAL'
ORDER BY last_update_date DESC;
SELECT hz.ROWID, hz.*
FROM hz_education hz
WHERE education_id = l_trans_cur_rec.education_id;
hz_education_pkg.update_row (x_rowid => l_edu_id_rec.ROWID,
x_education_id => l_edu_id_rec.education_id,
x_course_major => l_edu_id_rec.course_major,
x_party_id => l_edu_id_rec.party_id,
x_school_party_id => l_edu_id_rec.school_party_id,
x_degree_received => l_edu_id_rec.degree_received,
x_last_date_attended => l_edu_id_rec.last_date_attended,
x_school_attended_name => l_edu_id_rec.school_attended_name,
x_type_of_school => l_edu_id_rec.type_of_school,
x_start_date_attended => l_edu_id_rec.start_date_attended,
x_status => 'A',
x_object_version_number => l_edu_id_rec.object_version_number,
x_created_by_module => l_edu_id_rec.created_by_module,
x_application_id => l_edu_id_rec.application_id
);
write_log ( 'Calling update_transcript_details for transcript_id='
|| dup_cur_rec.transcript_id,
'igs.plsql.igs_da_trns_imp.create_acad_hist_rec'
);
update_transcript_details (l_trans_cur_rec);
hz_education_pkg.update_row (x_rowid => l_edu_rec.ROWID,
x_education_id => l_edu_rec.education_id,
x_course_major => l_edu_rec.course_major,
x_party_id => l_edu_rec.party_id,
x_school_party_id => l_edu_rec.school_party_id,
x_degree_received => l_edu_rec.degree_received,
x_last_date_attended => l_edu_rec.last_date_attended,
x_school_attended_name => l_edu_rec.school_attended_name,
x_type_of_school => l_edu_rec.type_of_school,
x_start_date_attended => l_edu_rec.start_date_attended,
x_status => 'I',
x_object_version_number => l_edu_rec.object_version_number,
x_created_by_module => l_edu_rec.created_by_module,
x_application_id => l_edu_rec.application_id
);
SELECT feature_value
FROM igs_da_req_ftrs
WHERE batch_id = p_batch_id AND feature_code = 'AUT';
SELECT NVL (achievable_credit_points,
enrolled_credit_points
) credit_points
FROM igs_ps_unit_ver
WHERE unit_cd = cp_unit_cd AND version_number = cp_version_number;
SELECT deceased_ind
FROM igs_pe_hz_parties
WHERE party_id = cp_party_id;
SELECT hp.party_number tca_party_number,
ihp.oss_org_unit_cd exemption_institution_cd, hp.party_name,
ihp.oi_institution_status, 'INSTITUTION CODE' SOURCE, hp.created_by,
hp.creation_date, hp.last_updated_by, hp.last_update_date,
hp.last_update_login
FROM hz_parties hp, igs_pe_hz_parties ihp
WHERE hp.party_id = ihp.party_id
AND ihp.inst_org_ind = 'I'
AND ihp.oi_institution_status = 'ACTIVE'
AND ihp.oss_org_unit_cd = cp_exemption_institution_cd;
SELECT ins.local_institution_ind
FROM igs_or_institution ins
WHERE ins.institution_cd = cp_ins_cd;
SELECT DECODE (cp_local_ind,
'N', NVL (cv.external_adv_stnd_limit, -1),
NVL (cv.internal_adv_stnd_limit, -1)
) adv_stnd_limit
FROM igs_ps_ver cv
WHERE cv.course_cd = cp_course_cd
AND cv.version_number = cp_version_number;
SELECT 'x'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND course_attempt_status IN
('ENROLLED',
'INACTIVE',
'INTERMIT',
'UNCONFIRM',
'DISCONTIN',
'COMPLETED'
);
| inserting a record into the table IGS_AV_STND_UNIT_ALL and|
| keeps adding error message to stack as an when it encounters.|
| |
| MODIFICATION HISTORY |
| jhanda 11-08-2005 Created |
+===========================================================================*/
x_return_status BOOLEAN := TRUE;
SELECT 'x'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND course_attempt_status IN
('ENROLLED',
'INACTIVE',
'INTERMIT',
'UNCONFIRM',
'DISCONTIN',
'COMPLETED'
);
SELECT ROWID
FROM igs_av_adv_standing_all
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND version_number = p_course_version
AND exemption_institution_cd = p_prev_institution_code;
update IGS_AV_ADV_STANDING_ALL with above obtained values for
total_exmptn_approved, total_exmptn_granted and total_exmptn_perc_grntd
*/
OPEN c_adv_stnd;
igs_av_adv_standing_pkg.update_row (x_rowid => l_adv_stnd.ROWID,
x_person_id => p_person_id,
x_course_cd => p_program_cd,
x_version_number => p_course_version,
x_total_exmptn_approved => l_total_exmptn_approved,
x_total_exmptn_granted => l_total_exmptn_granted,
x_total_exmptn_perc_grntd => l_total_exmptn_perc_grntd,
x_exemption_institution_cd => p_prev_institution_code,
x_mode => 'R'
);
l_program_update_date igs_av_stnd_unit_all.program_update_date%TYPE;
SELECT version_number
FROM igs_ps_unit_ver_all OUTER
WHERE unit_cd = l_unit_cd
AND unit_status = 'ACTIVE'
AND version_number =
(SELECT MAX (version_number)
FROM igs_ps_unit_ver_all inn
WHERE OUTER.unit_cd = inn.unit_cd
AND inn.unit_status = 'ACTIVE');
SELECT adv_stnd_basis_inst
FROM igs_av_stnd_conf;
SELECT dr.requestor_id
FROM igs_da_rqst dr, fnd_user fdu
WHERE dr.batch_id = p_batch_id
AND dr.requestor_id = fdu.person_party_id;
SELECT unt.ROWID, unt.*
FROM igs_av_stnd_unit_all unt
WHERE person_id = cp_person_id
AND exemption_institution_cd = cp_exemption_institution_cd
AND unit_details_id = cp_unit_details_id
AND unit_cd = cp_unit_cd
AND as_course_cd = cp_as_course_cd
AND as_version_number = cp_as_version_number
AND version_number = cp_version_number
AND s_adv_stnd_type = cp_s_adv_stnd_type
FOR UPDATE NOWAIT;
SELECT hz_parties.party_id
FROM hz_parties, fnd_user
WHERE fnd_user.customer_id = hz_parties.party_id
AND fnd_user.user_id = fnd_profile.VALUE ('IGS_DA_WF_ADMIN');
write_message ('***** INSERT INTO IGS_AV_ADV_STANDING_ALL *****'
);
igs_av_adv_standing_pkg.insert_row (x_rowid => v_rowid,
x_person_id => l_person_id,
x_course_cd => UPPER (p_program_cd
),
x_version_number => l_as_version_number,
x_total_exmptn_approved => 0,
x_total_exmptn_granted => 0,
x_total_exmptn_perc_grntd => 0,
x_exemption_institution_cd => l_prev_institution_code,
x_org_id => igs_ge_gen_003.get_org_id ()
);
l_program_update_date := NULL;
l_program_update_date := SYSDATE;
write_message ('***** INSERT INTO IGS_AV_STND_UNIT_ALL *****'
);
SELECT schm.grading_schema_code,schm.grd_schm_version_number
FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
WHERE schm.unit_version_number = l_version_number AND schm.unit_code = l_unit_cd AND grd.grade=p_avstnd_grade
AND schm.grading_schema_code = grd.grading_schema_cd
AND schm.grd_schm_version_number=grd.version_number;
igs_av_stnd_unit_pkg.insert_row (x_rowid => l_dmmy_rowid,
x_person_id => l_person_id,
x_as_course_cd => UPPER (p_program_cd
),
x_as_version_number => l_as_version_number,
x_s_adv_stnd_type => l_s_adv_stnd_type,
x_unit_cd => UPPER (l_unit_cd
),
x_version_number => l_version_number,
x_s_adv_stnd_granting_status => 'APPROVED',
x_credit_percentage => NULL,
x_s_adv_stnd_recognition_type => 'CREDIT',
x_approved_dt => SYSDATE,
x_authorising_person_id => l_auth_pers_id,
x_crs_group_ind => 'N',
x_exemption_institution_cd => UPPER (l_prev_institution_code
),
x_granted_dt => TO_DATE (NULL
),
x_expiry_dt => TO_DATE (NULL
),
x_cancelled_dt => TO_DATE (NULL
),
x_revoked_dt => TO_DATE (NULL
),
x_comments => 'Advanced Standing from external source',
x_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
x_cal_type => l_cal_type,
x_ci_sequence_number => l_seq_number,
x_institution_cd => UPPER (l_prev_institution_code --l_institution_cd
),
x_unit_details_id => p_unit_details_id,
x_grade => p_avstnd_grade,
x_achievable_credit_points => l_achievable_credit_points,
x_mode => 'R',
x_org_id => igs_ge_gen_003.get_org_id (),
x_adv_stnd_trans => 'N',
x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number
);
SELECT schm.grading_schema_code,schm.grd_schm_version_number
FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
WHERE schm.unit_version_number = l_version_number AND schm.unit_code = l_unit_cd AND grd.grade=p_avstnd_grade
AND schm.grading_schema_code = grd.grading_schema_cd
AND schm.grd_schm_version_number=grd.version_number;
igs_av_stnd_unit_pkg.update_row (x_rowid => l_dmmy_rowid,
x_person_id => l_person_id,
x_as_course_cd => UPPER (p_program_cd
),
x_as_version_number => l_as_version_number,
x_s_adv_stnd_type => l_s_adv_stnd_type,
x_unit_cd => UPPER (l_unit_cd
),
x_version_number => l_version_number,
x_s_adv_stnd_granting_status => l_grant_status,
x_credit_percentage => TO_NUMBER (NULL
),
x_s_adv_stnd_recognition_type => 'CREDIT',
x_approved_dt => SYSDATE,
x_authorising_person_id => l_auth_pers_id,
x_crs_group_ind => 'N',
x_exemption_institution_cd => UPPER (l_prev_institution_code
),
x_granted_dt => l_granted_dt,
x_expiry_dt => TO_DATE (NULL
),
x_cancelled_dt => TO_DATE (NULL
),
x_revoked_dt => TO_DATE (NULL
),
x_comments => 'Advanced Standing from external source',
x_av_stnd_unit_id => l_av_stnd_unit_lvl_id,
x_cal_type => l_cal_type,
x_ci_sequence_number => l_seq_number,
x_institution_cd => UPPER (l_prev_institution_code --l_institution_cd
),
x_unit_details_id => p_unit_details_id,
x_tst_rslt_dtls_id => NULL,
x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number,
x_grade => p_avstnd_grade,
x_achievable_credit_points => l_achievable_credit_points,
x_mode => 'R',
x_deg_aud_detail_id => NULL
);
write_message ( ' Inserted into IGS_AV_STND_UNIT_ALL val AV_STND_UNIT_ID ='
|| l_av_stnd_unit_lvl_id
);
SELECT schm.grading_schema_code,schm.grd_schm_version_number
FROM igs_ps_unit_grd_schm schm , IGS_AS_GRD_SCH_GRADE grd
WHERE schm.unit_version_number = l_adv_stnd_unt.version_number AND schm.unit_code = l_adv_stnd_unt.unit_cd AND grd.grade=p_avstnd_grade
AND schm.grading_schema_code = grd.grading_schema_cd
AND schm.grd_schm_version_number=grd.version_number;
igs_av_stnd_unit_pkg.update_row (x_rowid => l_adv_stnd_unt.ROWID,
x_person_id => l_adv_stnd_unt.person_id,
x_as_course_cd => l_adv_stnd_unt.as_course_cd,
x_as_version_number => l_adv_stnd_unt.as_version_number,
x_s_adv_stnd_type => l_adv_stnd_unt.s_adv_stnd_type,
x_unit_cd => l_adv_stnd_unt.unit_cd,
x_version_number => l_adv_stnd_unt.version_number,
x_s_adv_stnd_granting_status => l_grant_status,
x_credit_percentage => l_adv_stnd_unt.credit_percentage,
x_s_adv_stnd_recognition_type => l_adv_stnd_unt.s_adv_stnd_recognition_type,
x_approved_dt => l_adv_stnd_unt.approved_dt,
x_authorising_person_id => l_auth_pers_id,
x_crs_group_ind => l_adv_stnd_unt.crs_group_ind,
x_exemption_institution_cd => l_adv_stnd_unt.exemption_institution_cd,
x_granted_dt => l_granted_dt,
x_expiry_dt => l_adv_stnd_unt.expiry_dt,
x_cancelled_dt => l_adv_stnd_unt.cancelled_dt,
x_revoked_dt => l_adv_stnd_unt.revoked_dt,
x_comments => 'Advanced Standing from external source',
x_av_stnd_unit_id => l_adv_stnd_unt.av_stnd_unit_id,
x_cal_type => l_cal_type,
x_ci_sequence_number => l_seq_number,
x_institution_cd => l_adv_stnd_unt.institution_cd,
x_unit_details_id => l_adv_stnd_unt.unit_details_id,
x_tst_rslt_dtls_id => l_adv_stnd_unt.tst_rslt_dtls_id,
x_grading_schema_cd => rec_unitcd_ver.grading_schema_code,
x_grd_sch_version_number => rec_unitcd_ver.grd_schm_version_number,
x_grade => p_avstnd_grade,
x_achievable_credit_points => l_achievable_credit_points,
x_deg_aud_detail_id => l_adv_stnd_unt.deg_aud_detail_id
);
write_message ('DONE IGS_AV_STND_UNIT_PKG.UPDATE_ROW ');
SELECT request_type_id
FROM igs_da_cnfg_req_typ
WHERE request_name = 'Transfer Evaluation External Source'
AND request_type = 'TE'
AND closed_ind = 'N';
SELECT igs_da_batch_id_s.NEXTVAL
FROM DUAL;
SELECT requestor_id
FROM igs_da_rqst
WHERE batch_id = p_batch_id;
SELECT 'x'
FROM igs_da_req_stdnts
WHERE batch_id = p_batch_id
AND person_id = p_person_id
AND ERROR_CODE = 'INP';
SELECT hz_parties.party_id
FROM hz_parties, fnd_user
WHERE fnd_user.customer_id = hz_parties.party_id
AND fnd_user.user_id = fnd_profile.VALUE ('IGS_DA_WF_ADMIN');
delete_adv_stnd_records (l_person_id);
write_message ('***** INSERT INTO IGS_DA_RQST *****');
igs_da_rqst_pkg.insert_row (x_rowid => v_dummy_rowid,
x_batch_id => l_batch_id,
x_request_type_id => l_request_type_id,
x_request_mode => 'MULTI',
x_program_comparison_type => 'DP',
x_request_status => 'COMPLETED',
x_person_id_group_id => NULL,
x_person_id => NULL,
x_requestor_id => l_requestor_person_id,
x_student_release_ind => 'N',
x_special_program => NULL,
x_special_program_catalog => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_mode => 'R',
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count
);
write_message ('***** INSERT INTO IGS_DA_REQ_STDNTS *****');
igs_da_req_stdnts_pkg.insert_row (x_rowid => v_dummy_rowid,
x_batch_id => l_batch_id,
x_igs_da_req_stdnts_id => l_igs_da_req_stdnts_id,
x_person_id => l_person_id,
x_program_code => p_program_code,
x_wif_program_code => NULL,
x_special_program_code => NULL,
x_major_unit_set_cd => NULL,
x_program_major_code => NULL,
x_report_text => NULL,
x_wif_id => NULL,
x_mode => 'R',
x_error_code => 'INP'
);
UPDATE igs_da_req_stdnts
SET ERROR_CODE = 'INP'
WHERE batch_id = p_batch_id AND person_id = l_person_id;
UPDATE igs_da_req_stdnts
SET ERROR_CODE = NULL
WHERE batch_id = p_batch_id AND ERROR_CODE = 'INP';
UPDATE igs_da_req_stdnts
SET report_text =
' Transfer Evaluation
'
|| 'Completed Successfully'
|| ' '
|| ' '
WHERE batch_id = p_batch_id AND report_text IS NULL;
igs_da_xml_pkg.update_request_status (p_batch_id);