[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : Validate Registration details from interface tables and update
the corresponding OSS tables with the new Registration Date and Unit
Attempt Status.
Known limitations,
enhancements,
remarks :
Change History
Who When What
ckasu 31-DEC-2004 Modified code inorder to show note when stored Transfer exists for the source program and
that stored transfer needs to be updated to include newly enrolled units so that this unit
can be transfered to the destination when the future dated transfer is processed as a part
of bug#4095276
ckasu 29-SEP-2004 Modified signature of log_regid_person_dtls procedure by adding
p_load_cal_type,p_load_seq_number inorder to print Load cal details
and also modified code to do hold,person validations only when Load
cal in the context has unit attempts.
ckasu 14-SEP-2004 Added Holds,person step validations as a part of Bug# 3823810
stutta 11-Feb-2004 Passing new parameter p_enrolled_dt as SYSDATE in
call to validate_enroll_validate.
rvivekan 3-Aug-2003 Bulk unit upload TD #3049009. Added 'Enrolled' status to the get_prgrm_dtls curosr definition.
Added error message logging for igs_en_enroll_wlst.validate_uinit_steps and igs_ss_en_wrappers.validate_enroll_validate
svenkata 15-Jan-2003 Bug# 2740746 - The message name IGS_PS_INVALID_PRG_CD was invalid.Changed the message to IGS_PR_INVALID_PRG_CD.
svenkata 31-DEC-2002 Bug# 2724288 - added values for WHO columns in the UPDATE stmnts for the interface table.
sgurusam 17-Jun-2005 Modified to pass aditional parameter p_calling_obj = 'JOB' p_create_warning='N' in the calls to
igs_en_elgbl_person.eval_person_steps
Modified to pass aditional parameter p_calling_obj='JOB' in the calls to
Igs_En_Enroll_Wlst.Validate_unit_steps
******************************************************************/
--cursor to fetch records from interface table
--
CURSOR get_int_dtls (p_batch_id IN NUMBER) IS
SELECT ROWID , int.*
FROM igs_en_reg_upd_int int
WHERE int.batch_id = p_batch_id
FOR UPDATE NOWAIT ;
SELECT pe.person_id
FROM igs_pe_person_base_v pe
WHERE pe.person_number = p_person_number;
SELECT 'x'
FROM igs_ps_ver ps
WHERE ps.course_cd = p_course_cd ;
SELECT ca.cal_type , ca.sequence_number , ca.start_dt , ca.end_dt
FROM igs_ca_inst ca
WHERE ca.alternate_code = p_alternate_code;
SELECT COUNT(*)
FROM igs_ca_inst ca
WHERE ca.alternate_code = p_alternate_code;
SELECT sca.course_cd , sca.version_number , sca.cal_type,sca.course_attempt_status
FROM igs_en_stdnt_ps_att_all sca
WHERE sca.course_cd = NVL(p_course_cd,sca.course_cd) AND
sca.course_attempt_status IN ('INACTIVE','ENROLLED') AND
sca.cal_type = p_cal_type AND
sca.person_id = p_person_id ;
SELECT ci.cal_type , ci.start_dt , ci.end_dt , ci.sequence_number
FROM igs_ca_inst ci , igs_ca_type ct , igs_ca_stat cs , igs_ca_inst_rel cir
WHERE ct.cal_type = ci.cal_type
AND ct.s_cal_cat = 'LOAD'
AND cir.sup_cal_type = p_acad_cal_type
AND cir.sup_ci_sequence_number = p_acad_ci_sequence_number
AND cir.sub_cal_type = ci.cal_type
AND cir.suB_ci_sequence_number = ci.sequence_number
AND ci.cal_status = cs. cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci.start_dt >= p_start_dt
AND ci.end_dt <= p_end_dt;
SELECT sua.uoo_id , sua.unit_cd , sua.version_number , sua.cal_type , sua.ci_sequence_number
FROM igs_en_su_attempt_all sua , igs_ca_load_to_teach_v calt
where sua.unit_attempt_status = 'UNCONFIRM'
AND sua.course_cd = p_course_cd
AND sua.cal_type = calt.teach_cal_type
AND sua.person_id = p_person_id
AND sua.ci_sequence_number = calt.teach_ci_sequence_number
AND calt.load_cal_type = p_load_cal_type
AND calt.load_ci_sequence_number = p_load_ci_sequence_number
ORDER BY sua.sup_unit_cd DESC;
SELECT asa.ROWID , asa.*
FROM igs_as_sc_atmpt_enr asa , igs_ca_inst ci
WHERE ci.start_dt >= p_start_dt AND ci.end_dt <= p_end_dt
AND asa.cal_type = ci.cal_type
AND asa.ci_sequence_number = ci.sequence_number
AND asa.course_cd = p_course_cd
AND asa.person_id = p_person_id
FOR UPDATE NOWAIT ;
SELECT sua.ROWID , sua.*
FROM igs_en_su_attempt_all sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.uoo_id = p_uoo_id
FOR UPDATE NOWAIT ;
l_reg_update_int_rec igs_en_reg_upd_int%ROWTYPE;
l_last_update_date igs_en_reg_upd_int.last_update_date%TYPE;
l_last_updated_by igs_en_reg_upd_int.last_updated_by%TYPE;
l_last_update_login igs_en_reg_upd_int.last_update_login%TYPE;
l_program_update_date igs_en_reg_upd_int.program_update_date%TYPE;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
IF l_last_updated_by IS NULL THEN
l_last_updated_by := -1;
IF l_last_update_login IS NULL THEN
l_last_update_login := -1;
l_program_update_date := NULL;
l_program_update_date := SYSDATE;
FOR l_reg_update_int_rec IN get_int_dtls (p_batch_id)
LOOP
BEGIN
SAVEPOINT interface_rec;
OPEN get_person_number (l_reg_update_int_rec.person_number ) ;
IF l_reg_update_int_rec.course_cd IS NOT NULL THEN
OPEN get_course_cd(l_reg_update_int_rec.course_cd );
OPEN get_acad_cnt (l_reg_update_int_rec.alternate_code);
OPEN get_acad_cal_dtls (l_reg_update_int_rec.alternate_code);
-- Update the record with error messages in the interface table.
UPDATE igs_en_reg_upd_int SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
FOR l_prgm_dtls_rec IN get_prgm_dtls ( NVL(l_reg_update_int_rec.course_cd , NULL) , l_acad_cal_type , l_person_id)
LOOP
l_prgm_inactive_chk := 'Y';
log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,
l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
-- Update the Enrolled_date of all the Unit Attempts that have passed the Unit step validation
l_pass_uoo_ids := l_uoo_ids;
UPDATE IGS_EN_SU_ATTEMPT_ALL SET ENROLLED_DT = l_reg_update_int_rec.enr_form_received_dt WHERE ROWID = l_get_sua_upd_rec.ROWID;
UPDATE igs_en_reg_upd_int
SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
-- Update all Administrative Program Details for the Programs that passed the validations.
igs_as_sc_atmpt_enr_pkg .update_row (
X_ROWID => l_as_sc_atmpt_enr_rec.ROWID,
x_person_id => l_as_sc_atmpt_enr_rec.person_id,
x_course_cd => l_as_sc_atmpt_enr_rec.course_cd,
x_cal_type => l_as_sc_atmpt_enr_rec.cal_type,
x_ci_sequence_number => l_as_sc_atmpt_enr_rec.ci_sequence_number,
x_enrolment_cat => l_as_sc_atmpt_enr_rec.enrolment_cat,
x_enrolled_dt => l_reg_update_int_rec.enr_form_received_dt,
x_enr_form_due_dt => l_as_sc_atmpt_enr_rec.enr_form_due_dt ,
x_enr_pckg_prod_dt => l_as_sc_atmpt_enr_rec.enr_pckg_prod_dt ,
x_enr_form_received_dt => l_reg_update_int_rec.enr_form_received_dt ,
x_mode => 'R' );
-- Update the records with error message in the interface table
FND_MESSAGE.SET_NAME('IGS','IGS_EN_INACT_PGM');
UPDATE igs_en_reg_upd_int
SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
UPDATE igs_en_reg_upd_int
SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
UPDATE igs_en_reg_upd_int
SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
DELETE FROM igs_en_reg_upd_int
WHERE ROWID = l_reg_update_int_rec.ROWID;
UPDATE igs_en_reg_upd_int
SET errors = l_message_text ,
creation_date = l_creation_date ,
last_update_date = l_last_update_date ,
created_by = l_created_by ,
last_updated_by = l_last_updated_by ,
last_update_login = l_last_update_login ,
request_id = l_request_id ,
program_id = l_program_id ,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE ROWID = l_reg_update_int_rec.ROWID ;
SELECT batch_description
FROM igs_en_reg_btch_int
WHERE batch_id = p_batch_id;
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE preferred_ind = 'Y';
SELECT person_id,full_name
FROM igs_pe_person_base_v
WHERE person_number = p_person_number;
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE person_id_type = p_pers_id_type AND
pe_person_id = p_person_id;
SELECT registration_id,person_number,course_cd,alternate_code,errors
FROM igs_en_reg_upd_int
WHERE batch_id = p_batch_id AND
errors IS NOT NULL;
SELECT count(*)
FROM igs_en_reg_upd_int int
WHERE int.batch_id = p_batch_id ;