[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure is called to insert errors into the exception run
table. The Exception Run Report is run after the Generate Extract
process completes which reads the data from this table and prints the
report
The processing should not stop if any error is encountered unless it
is fatal.
Parameters :
p_he_ext_run_exceptions IN Record which contains the values that
need to be inserted into the exception
table.
The field Exception_Reason should
contain the message text not the
message code.
----------------------------------------------------------------------*/
PROCEDURE log_error
(p_he_ext_run_exceptions IN OUT NOCOPY igs_he_ext_run_excp%ROWTYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
Igs_He_Ext_Run_Excp_Pkg.Insert_Row
(X_Rowid => l_rowid,
X_Ext_Exception_Id => p_he_ext_run_exceptions.ext_exception_id,
X_Extract_Run_Id => p_he_ext_run_exceptions.Extract_Run_Id,
X_Person_Id => p_he_ext_run_exceptions.Person_Id,
X_Person_Number => p_he_ext_run_exceptions.Person_Number,
X_Course_Cd => p_he_ext_run_exceptions.Course_Cd,
X_Crv_Version_Number => p_he_ext_run_exceptions.Crv_Version_Number,
X_Unit_Cd => p_he_ext_run_exceptions.Unit_Cd,
X_Uv_Version_Number => p_he_ext_run_exceptions.Uv_Version_Number,
X_Line_Number => p_he_ext_run_exceptions.Line_Number,
X_Field_Number => p_he_ext_run_exceptions.Field_Number,
X_Exception_Reason => p_he_ext_run_exceptions.Exception_Reason);
SELECT course_type
FROM igs_ps_ver_all
WHERE course_cd = g_en_stdnt_ps_att.course_cd
AND version_number = g_en_stdnt_ps_att.version_number;
SELECT 'X'
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND only = cp_person_id
AND param_type = 'RECALC-PERSON';
SELECT 'X'
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND only = cp_person_id
AND param_type = 'RECALC-PROGRAM';
SELECT param_type,
exclude,
only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND (exclude IS NOT NULL
OR only IS NOT NULL)
AND param_type IN ('PSN_IDENT_GROUP', 'PSN_ID')
ORDER BY param_type;
SELECT a.group_cd
FROM igs_pe_persid_group a ,
igs_pe_prsid_grp_mem_all b
WHERE b.person_id = p_person_id
AND a.group_cd = p_group_cd
AND ( b.Start_Date IS NULL OR b.Start_Date <= cp_enrl_end_dt)
AND ( b.End_Date IS NULL OR b.End_Date >= cp_enrl_start_dt )
AND a.group_id = b.group_id AND a.closed_ind = 'N' ;
SELECT group_id, group_type
FROM igs_pe_persid_group_v
WHERE group_cd = p_group_cd;
SELECT param_type,
DECODE(exclude, 'YES', 'Y',
'NO', 'N',
exclude) exclude,
DECODE(only, 'YES', 'Y',
'NO', 'N',
only) only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND (exclude IS NOT NULL
OR only IS NOT NULL)
AND param_type IN ('PROGRAM' ,'DORMANT','VISIT_EXCHANGE','POST_CODE' )
ORDER BY param_type;
SELECT special_student_cd
FROM igs_he_ad_dtl_all
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND sequence_number = p_sequence_number;
SELECT exclude, only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND param_type = 'PROGRAM_CATEGORY' ;
SELECT exclude, only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND param_type = 'PROGRAM_GROUP' ;
SELECT 'X'
FROM igs_ps_categorise_all
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND course_cat IN ( SELECT exclude FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND exclude IS NOT NULL
AND param_type ='PROGRAM_CATEGORY') ;
SELECT 'X'
FROM igs_ps_categorise_all
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND course_cat IN ( SELECT only FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND only IS NOT NULL
AND param_type ='PROGRAM_CATEGORY') ;
SELECT 'X'
FROM igs_ps_grp_mbr
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND course_group_cd IN ( SELECT exclude FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND exclude IS NOT NULL
AND param_type ='PROGRAM_GROUP') ;
SELECT 'X'
FROM igs_ps_grp_mbr
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND course_group_cd IN ( SELECT only FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND only IS NOT NULL
AND param_type ='PROGRAM_GROUP') ;
SELECT param_type,
DECODE(exclude, 'YES', 'Y',
'NO', 'N',
exclude) exclude,
DECODE(only, 'YES', 'Y',
'NO', 'N',
only) only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND (exclude IS NOT NULL
OR only IS NOT NULL)
AND param_type IN ( 'ORG_UNIT' ,'PROGRAM_YEAR','OUTSIDE_UK','FE')
ORDER BY param_type;
This procedure deletes rows from tables before starting the
process
Because it uses table handlers, there might be a server
performance issue as the number of rows being processed
would be huge
Parameters :
p_extract_run_id IN The Extract Run Id
p_line_number IN Line Number
p_table_name IN Table Name
Values : INTERIM - Igs_He_Ext_Run_Interim
LINE - Igs_He_Ex_Rn_Dat_Ln
FIELD - Igs_He_Ex_Rn_Dat_Fd
ERROR - Igs_He_Ext_Run_Excp
WHO: AYEDUBAT
WHAT: Removed the 'PROCEDURE delete_rows' as all the call to this procedure are placed
with direct DMLS for bug,3179585
----------------------------------------------------------------------*/
/*----------------------------------------------------------------------
This procedure fetches the modules that need to be processed
and inserts them into the temporary procesing table
Parameters :
p_extract_run_id IN The Extract Run Id
--smaddali seperated the extract parameters validation into 3 different groups :person,
-- program attempt and program,program year parameters for bug 2350730
-- hence the procedure validate_params has been split into 3 procedures
-- namely validate_params , validate_params1,validate_params2
-- and calls to these procedures have been added in this procedure at appropriate places
--Done as a part of HEFD101(2636897)
--Bayadav Included in the WHERE clause the graduated student also but who have not awarded ans the conferral_td is set
--Outer join is to consider the graduation.conferraldt condition only in case the student have graduation rec .
--The other students(not graduated) should also be selected
--smvk 03-Jun-2003 Bug # 2858436.Modified the cursor c_quaim to select open program awards only.
16-DEC-02 Bayadav Included the conditions in the WHERE clause to consider the students who have been awarded in HESA period but must have comepleted the course earlier as a part of bug 2702117
20-JAN-2003 Bayadav Included the validations to check if the alternate person id does not contains non-numeric characters for the person in context as a part of 2744808
03-MAR-2003 bayadav Included check in c_encp cursor to cehck for the Units
sarakshi 26-Jun-2003 Enh#2930935,modified cursor c_encp to include unit section level
enrolled_credit_points if exists else unit level credit points
dsridhar 04-Jul-03 Bug No:3079731. Changed the order of setting the tokens for the message IGS_HE_INVALID_PER_ID.
smaddali 20-Oct-03 Modified procedure for bug#3172980 , skip students whose api person id> 8 digits
ayedubat 14-Nov-03 Modified the procedure to improve the performance for Bug, 3179585
smaddali 05-Dec-03 Modified cursors c_get_yop, c_get_spa to add condition complete_ind=Y , for HECR210 build, bug#2874542
smaddali 10-Dec-03 Modified logic to get Term record details for HECR214 - Term based fees enhancement, bug#3291656
smaddali 14-Jan-04 Modified cursor c_qulaim for bug#3360646
ayedubat 09-Mar-04 Modified logic to check the condition, l_std_inst.person_id <> l_prev_person_id only
when logging the error message in the log file for Bug, 3491096
jbaber 04-Nov-04 Modified c_get_spa for HE354 - Program Transfer
Replace c_inact_st with c_enr_su for bug 3810280
slaport 31-Jan-05 Modified cursor c_alternate_id for HE358 to ignore logically deleted records.
jbaber 15-Apr-05 Modified c_get_spa cursor to include records where future_date_trans_flag = N or S as per bug #4179106
jtmathew 27-Jan-06 Modified c_get_spa cursor to include award conferral date parameters
jbaber 15-Mar-06 Added p_recalculate parameter for HE365 - Extract Rerun
----------------------------------------------------------------------*/
PROCEDURE get_students (p_extract_run_id IN NUMBER, p_recalculate IN BOOLEAN) IS
--smaddali modified where clause for comparing the enrolment dates for bug 2415632
--dsridhar modified the table form igs_pe_person to igs_pe_person_base_v for the bug 2911738
--Removed the cursor, c_get_stins for Bug, 3179585
--smaddali modified where clause for comparing the enrolment dates for bug 2415632
--smaddali added field hspa.fe_student_marker for bug 2452834
--Removed the cursor to remove person_id and person_number parameters for Bug, 3179585
--jbaber added check for exclude flag and removed calendar types for HE305
CURSOR c_get_spa (
p_submission_name igs_he_submsn_header.submission_name%TYPE,
p_return_name igs_he_submsn_return.return_name%TYPE,
p_user_return_subclass igs_he_submsn_return.user_return_subclass%TYPE,
p_enrl_start_dt DATE,
p_enrl_end_dt DATE,
p_awd_conf_start_dt DATE,
p_awd_conf_end_dt DATE) IS
SELECT DISTINCT sca.person_id,
pe.party_number person_number,
sca.course_cd,
sca.version_number,
sca.location_cd ,
sca.attendance_mode,
sca.attendance_type,
sca.cal_type sca_cal_type,
sca.commencement_dt ,
sca.discontinued_dt,
sca.course_rqrmnt_complete_ind,
sca.course_rqrmnts_complete_dt,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number,
sca.course_attempt_status,
sca.funding_source,
hspa.student_inst_number,
hspa.student_qual_aim,
hspa.return_type,
hspa.postcode,
hspa.special_student,
hspa.fe_student_marker ,
enawd.complete_ind,
enawd.conferral_date
FROM igs_en_stdnt_ps_att_all sca,
igs_he_st_spa_all hspa,
igs_he_st_prog_all hprog,
igs_en_spa_awd_aim enawd,
hz_parties pe
WHERE sca.person_id = hspa.person_id
AND sca.course_cd = hspa.course_cd
AND sca.course_cd = hprog.course_cd (+)
AND sca.version_number = hprog.version_number (+)
AND NVL(hprog.exclude_flag, 'N') = 'N'
AND NVL(hspa.exclude_flag, 'N') = 'N'
AND NVL(sca.future_dated_trans_flag,'N') IN ('N','S')
AND sca.student_confirmed_ind = 'Y'
AND hspa.person_id = enawd.person_id(+)
AND hspa.course_cd = enawd.course_cd(+)
AND sca.person_id = pe.party_id
AND ( ( sca.commencement_dt <= p_enrl_end_dt
AND ( sca.discontinued_dt IS NULL OR sca.discontinued_dt >= p_enrl_start_dt )
AND (sca.course_rqrmnts_complete_dt IS NULL OR
sca.course_rqrmnts_complete_dt >= p_enrl_start_dt
)
)
OR
(
enawd.complete_ind = 'Y' AND
(enawd.conferral_date BETWEEN p_awd_conf_start_dt AND p_awd_conf_end_dt))
)
ORDER BY sca.person_id, hspa.student_inst_number, discontinued_dt DESC,
course_rqrmnts_complete_dt DESC, sca.commencement_dt DESC ;
SELECT award_cd
FROM igs_ps_award
WHERE course_cd = p_course_cd
AND version_number = p_version_number
AND closed_ind = 'N' ;
SELECT DISTINCT susa.unit_set_cd,
susa.us_version_number,
susa.sequence_number,
susa.selection_dt,
susa.end_dt,
susa.rqrmnts_complete_ind,
susa.rqrmnts_complete_dt,
husa.study_location ,
husa.fte_perc_override,
husa.credit_value_yop1
FROM igs_as_su_setatmpt susa,
igs_he_en_susa husa,
igs_en_unit_set us,
igs_en_unit_set_cat susc,
igs_en_spa_awd_aim enawd,
igs_en_stdnt_ps_att_all sca
WHERE susa.person_id = sca.person_id
AND susa.course_cd = sca.course_cd
AND sca.person_id = enawd.person_id(+)
AND sca.course_cd = enawd.course_cd(+)
AND susa.unit_set_cd = husa.unit_set_cd
AND susa.us_version_number = husa.us_version_number
AND susa.person_id = husa.person_id
AND susa.course_cd = husa.course_cd
AND susa.sequence_number = husa.sequence_number
AND susa.unit_set_cd = us.unit_set_cd
AND susa.us_version_number = us.version_number
AND us.unit_set_cat = susc.unit_set_cat
AND susa.person_id = p_person_id
AND susa.course_cd = p_course_cd
AND susc.s_unit_set_cat = 'PRENRL_YR'
-- the program attempt is overlapping with the submission period and the yop is also overlapping with the submission period
AND ( ( sca.commencement_dt <= p_enrl_end_dt AND
(sca.discontinued_dt IS NULL OR sca.discontinued_dt >= p_enrl_start_dt ) AND
(sca.course_rqrmnts_complete_dt IS NULL OR sca.course_rqrmnts_complete_dt >= p_enrl_start_dt ) AND
susa.selection_dt <= p_enrl_end_dt AND
(susa.end_dt IS NULL OR susa.end_dt >= p_enrl_start_dt ) AND
(susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= p_enrl_start_dt)
)
OR
-- the yop has completed before the start of the submission period
-- AND the program attempt has completed before the end of the submission period
-- AND an award has been conferred between the NVL(award conferral dates, submission period)
( susa.rqrmnts_complete_dt < p_enrl_start_dt AND
sca.course_rqrmnts_complete_dt <= p_enrl_end_dt AND
enawd.complete_ind = 'Y' AND
enawd.conferral_date BETWEEN p_awd_conf_start_dt AND p_awd_conf_end_dt
)
)
ORDER BY susa.rqrmnts_complete_dt DESC, susa.end_dt DESC, susa.selection_dt DESC;
SELECT crv.title,
crv.std_annual_load,
crv.contact_hours,
crv.govt_special_course_type,
crv.responsible_org_unit_cd,
hpr.location_of_study ,
hpr.return_type,
hpr.default_award,
Nvl(hpr.program_calc,'N') ,
hpr.fe_program_marker,
hpud.location_of_study,
hpud.credit_value_yop1,
hpud.fte_intensity ,
hpud.funding_source
FROM igs_ps_ver crv,
igs_he_st_prog hpr,
igs_he_poous hpud
WHERE crv.course_cd = hpr.course_cd
AND crv.version_number = hpr.version_number
AND crv.course_cd = p_course_cd
AND crv.version_number = p_crv_version_number
AND hpud.course_cd = crv.course_cd
AND hpud.crv_version_number = crv.version_number
AND hpud.cal_type = p_cal_type
AND hpud.attendance_mode = p_attendance_mode
AND hpud.attendance_type = p_attendance_type
AND hpud.location_cd = p_location_cd
AND hpud.unit_set_cd = p_unit_set_cd
AND hpud.us_version_number = p_us_version_number;
SELECT 'X'
FROM igs_en_su_attempt_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','DISCONTIN','DUPLICATE')
AND TRUNC(enrolled_dt) <= p_enrolment_end_date;
SELECT api_person_id,person_id_type, LENGTH(api_person_id) api_length
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type IN ('HUSID', 'UCASID', 'GTTRID', 'NMASID', 'SWASID')
AND Start_Dt <= cp_enrl_end_dt
AND ( End_Dt IS NULL OR End_Dt >= cp_enrl_start_dt )
AND (End_Dt IS NULL OR Start_Dt <> End_Dt)
ORDER BY person_id_type, Start_Dt DESC ;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
FROM igs_en_spa_terms tr , igs_ca_inst_all ca
WHERE tr.term_cal_type = ca.cal_type AND
tr.term_sequence_number = ca.sequence_number AND
tr.person_id = cp_person_id AND
tr.program_cd = cp_course_cd AND
cp_lev_dt BETWEEN ca.start_dt AND ca.end_dt
ORDER BY ca.start_dt DESC;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
FROM igs_en_spa_terms tr , igs_ca_inst_all ca
WHERE tr.term_cal_type = ca.cal_type AND
tr.term_sequence_number = ca.sequence_number AND
tr.person_id = cp_person_id AND
tr.program_cd = cp_course_cd AND
cp_lev_dt > ca.start_dt AND
ca.start_dt BETWEEN cp_enrl_start_dt AND cp_enrl_end_dt
ORDER BY ca.start_dt DESC;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
FROM igs_en_spa_terms tr , igs_ca_inst_all ca
WHERE tr.term_cal_type = ca.cal_type AND
tr.term_sequence_number = ca.sequence_number AND
tr.person_id = cp_person_id AND
tr.program_cd = cp_course_cd AND
ca.start_dt BETWEEN cp_enrl_start_dt AND cp_enrl_end_dt
ORDER BY ca.start_dt DESC;
g_as_su_setatmpt.selection_dt,
g_as_su_setatmpt.end_dt,
g_as_su_setatmpt.rqrmnts_complete_ind,
g_as_su_setatmpt.rqrmnts_complete_dt,
g_he_en_susa.study_location ,
g_he_en_susa.fte_perc_override,
g_he_en_susa.credit_value_yop1;
igs_he_ext_run_interim_pkg.insert_row
(X_rowid => l_rowid,
X_ext_interim_id => l_ext_interim_id,
X_extract_run_id => p_extract_run_id,
X_person_id => g_en_stdnt_ps_att.person_id,
X_course_cd => g_en_stdnt_ps_att.course_cd,
X_crv_version_number => l_std_inst.version_number,
X_unit_cd => NULL,
X_uv_version_number => NULL,
X_student_inst_number => g_he_st_spa.student_inst_number,
X_line_number => NULL);
and inserts them into the temporary procesing table
Parameters :
p_extract_run_id IN The Extract Run Id
----------------------------------------------------------------------*/
PROCEDURE get_modules (p_extract_run_id IN NUMBER)
IS
--smaddali modified this cursor to add NVL 'N' to program_calc, also added ckeck that module_id is not null for bug 2425932
CURSOR c_get_mod (p_stdnt_extract_run_id NUMBER)
IS
SELECT DISTINCT Nvl(a.override_value, a.value) module_id
FROM igs_he_ex_rn_dat_fd a,
igs_he_ex_rn_dat_ln b,
igs_he_st_prog c
WHERE a.extract_run_id = b.extract_run_id
AND b.extract_run_id = p_stdnt_extract_run_id
AND b.course_cd = c.course_cd
AND b.crv_version_number = c.version_number
AND NVL(c.program_calc,'N') = 'N'
AND a.field_number BETWEEN 85 AND 100
AND NVL(a.override_value,a.value) IS NOT NULL ;
SELECT NVL(exclude_flag, 'N') exclude_flag
FROM igs_he_st_unt_vs_all
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
igs_he_ext_run_interim_pkg.insert_row
(X_rowid => l_rowid,
X_ext_interim_id => l_he_ext_run_interim.ext_interim_id,
X_extract_run_id => l_he_ext_run_interim.extract_run_id,
X_person_id => NULL,
X_course_cd => NULL,
X_crv_version_number => NULL,
X_unit_cd => l_he_ext_run_interim.unit_cd,
X_uv_version_number => l_he_ext_run_interim.uv_version_number,
X_student_inst_number => NULL,
X_line_number => l_he_ext_run_interim.line_number);
SELECT param_type,
exclude,
only
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND (exclude IS NOT NULL
OR only IS NOT NULL)
AND param_type IN ('DLHE','POPDLHE');
and inserts them into the temporary procesing table
Known Limitations,Enhancements or Remarks:
Change History :
Who When What
jchakrab 20-Feb-2006 Modified for 4256498 - removed ORDER BY from c_get_dlhe query
jbaber 15-Mar-2006 Added p_recalculate_flag for HE365 - Extract Rerun
***************************************************************/
-- Get all the dlhe records belonging to all the qualifying periods in this submission return
CURSOR c_get_dlhe ( p_submission_name igs_he_submsn_header.submission_name%TYPE,
p_return_name igs_he_submsn_return.return_name%TYPE,
p_user_return_subclass igs_he_submsn_return.user_return_subclass%TYPE )
IS
SELECT dlhe.person_id , dlhe.dlhe_record_status, dlhe.popdlhe_flag
FROM igs_he_stdnt_dlhe dlhe,
igs_he_sub_rtn_qual qual
WHERE qual.submission_name = dlhe.submission_name
AND qual.return_name = dlhe.return_name
AND qual.user_return_subclass = dlhe.user_return_subclass
AND qual.qual_period_code = dlhe.qual_period_code
AND qual.submission_name = p_submission_name
AND qual.return_name = p_return_name
AND qual.user_return_subclass = p_user_return_subclass
AND qual.closed_ind = 'N';
igs_he_ext_run_interim_pkg.insert_row
(X_rowid => l_rowid,
X_ext_interim_id => l_ext_interim_id ,
X_extract_run_id => p_extract_run_id ,
X_person_id => g_he_stdnt_dlhe.person_id,
X_course_cd => NULL,
X_crv_version_number => NULL,
X_unit_cd => NULL,
X_uv_version_number => NULL,
X_student_inst_number => NULL,
X_line_number => NULL);
jbaber 15-Mar-05 Do NOT delete recalculated fields for HE365
***************************************************************/
CURSOR c_recalc_criteria IS
SELECT 'X'
FROM igs_he_ext_run_prms
WHERE extract_run_id = p_extract_run_id
AND param_type IN ('RECALC-PERSON', 'RECALC-PROGRAM');
SELECT record_id,
line_number,
person_id,
course_cd,
manually_inserted,
exclude_from_file,
student_inst_number,
crv_version_number,
unit_cd,
uv_version_number,
recalculate_flag
FROM igs_he_ex_rn_dat_ln
WHERE extract_run_id = p_extract_run_id
AND manually_inserted = 'N'
AND (recalculate_flag = 'Y'
OR person_id IN (SELECT only from igs_he_ext_run_prms WHERE param_type = 'RECALC-PERSON' AND extract_run_id = p_extract_run_id)
OR course_cd IN (SELECT only from igs_he_ext_run_prms WHERE param_type = 'RECALC-PROGRAM'AND extract_run_id = p_extract_run_id));
DELETE FROM igs_he_ext_run_interim WHERE extract_run_id = p_extract_run_id;
igs_he_ext_run_interim_pkg.insert_row
(X_rowid => l_rowid,
X_ext_interim_id => l_he_ext_run_interim.ext_interim_id,
X_extract_run_id => l_he_ext_run_interim.extract_run_id,
X_person_id => l_he_ext_run_interim.person_id,
X_course_cd => l_he_ext_run_interim.course_cd,
X_crv_version_number => l_he_ext_run_interim.crv_version_number,
X_unit_cd => l_he_ext_run_interim.unit_cd,
X_uv_version_number => l_he_ext_run_interim.uv_version_number,
X_student_inst_number => l_he_ext_run_interim.student_inst_number,
X_line_number => l_he_ext_run_interim.line_number);
DELETE FROM igs_he_ext_run_interim
WHERE ext_interim_id IN
(SELECT MAX(ext_interim_id)
FROM igs_he_ext_run_interim a
GROUP BY extract_run_id, person_id, course_cd, crv_version_number, unit_cd, uv_version_number, student_inst_number
HAVING COUNT(ext_interim_id) > 1)
AND line_number IS NULL;
This procedure processes the records that have been inserted into
the temporary run table.
For each student / module, it will derive each of the fields and insert
the rows into the extarct run data tables.
Parameters :
p_extract_run_id IN The Extract Run Id
p_module_called_from IN Module this process was called from
Values : 'IGSHE007' and 'IGSHE008'
p_new_run_flag IN Indicates whether this is a fresh run
Values : 'Y', 'N'
retcode OUT NOCOPY Return status of the concurrent program
Values : 0 - Success
1 - Warning
2 - Error
errbuf OUT NOCOPY Error Buffer
----------------------------------------------------------------------*/
PROCEDURE extract_main
(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY NUMBER,
p_extract_run_id IN igs_he_ext_run_dtls.extract_run_id%TYPE,
p_module_called_from IN VARCHAR2,
p_new_run_flag IN VARCHAR2)
IS
/***************************************************************
Created By : Bidisha S
Date Created By : 28-Jan-02
Purpose : This procedure processes the records that have been inserted into
the temporary run table. For each student / module, it will derive each of the fields and insert
the rows into the extarct run data tables.
Known Limitations,Enhancements or Remarks:
Change History :
Who When What
smaddali 9-apr-03 modified for adding dlhe processing , HEFD203 build , bug#2717745
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
***************************************************************/
CURSOR c_ext_dtl IS
SELECT a.rowid,
a.submission_name,
a.user_return_subclass,
a.return_name ,
a.extract_phase,
a.student_ext_run_id,
a.conc_request_id,
a.conc_request_status,
a.extract_run_date,
a.file_name ,
a.file_location ,
a.date_file_sent ,
a.extract_override,
a.validation_kit_result,
a.hesa_validation_result ,
b.lrr_start_date,
b.lrr_end_date,
b.record_id,
c.enrolment_start_date,
c.enrolment_end_date,
c.offset_days ,
c.validation_country ,
Nvl(c.apply_to_atmpt_st_dt,'N') apply_to_atmpt_st_dt,
Nvl(c.apply_to_inst_st_dt,'N') apply_to_inst_st_dt
FROM igs_he_ext_run_dtls a,
igs_he_submsn_return b,
igs_he_submsn_header c
WHERE a.extract_run_id = p_extract_run_id
AND a.submission_name = b.submission_name
AND a.return_name = b.return_name
AND a.User_Return_Subclass = b.user_return_subclass
AND a.submission_name = c.submission_name;
SELECT 1
FROM igs_he_ext_run_interim
WHERE extract_run_id = p_extract_run_id;
DELETE FROM igs_he_ext_run_excp excp
WHERE excp.extract_run_id = p_extract_run_id
AND excp.line_number IN
(SELECT line_number
FROM igs_he_ext_run_interim
WHERE extract_run_id = excp.extract_run_id);
fnd_message.set_name('IGS','IGS_HE_DELETE_REC');
fnd_message.set_token('PROCEDURE', 'START_DELETE_ROWS');
DELETE FROM igs_he_ext_run_interim WHERE extract_run_id = p_extract_run_id;
DELETE FROM igs_he_ex_rn_dat_fd WHERE extract_run_id = p_extract_run_id;
DELETE FROM igs_he_ex_rn_dat_ln WHERE extract_run_id = p_extract_run_id;
DELETE FROM igs_he_ext_run_excp WHERE extract_run_id = p_extract_run_id;
fnd_message.set_token('PROCEDURE', 'END_DELETE_ROWS');
igs_he_ext_run_dtls_pkg.update_row
(X_rowid => l_ext_run_dtl_rowid,
X_extract_run_id => p_extract_run_id,
X_submission_name => g_he_ext_run_dtls.submission_name,
X_user_return_subclass => g_he_ext_run_dtls.user_return_subclass,
X_return_name => g_he_ext_run_dtls.return_name,
X_extract_phase => g_he_ext_run_dtls.extract_phase ,
X_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
X_conc_request_status => 'COMPLETE',
X_extract_run_date => TRUNC(SYSDATE),
X_file_name => g_he_ext_run_dtls.file_name ,
X_file_location => g_he_ext_run_dtls.file_location,
X_date_file_sent => g_he_ext_run_dtls.date_file_sent,
X_extract_override => g_he_ext_run_dtls.extract_override,
X_validation_kit_result => g_he_ext_run_dtls.validation_kit_result,
X_hesa_validation_result => g_he_ext_run_dtls.hesa_validation_result,
X_student_ext_run_id => g_he_ext_run_dtls.student_ext_run_id );
igs_he_ext_run_dtls_pkg.update_row
(X_rowid => l_ext_run_dtl_rowid,
X_extract_run_id => p_extract_run_id,
X_submission_name => g_he_ext_run_dtls.submission_name,
X_user_return_subclass => g_he_ext_run_dtls.user_return_subclass,
X_return_name => g_he_ext_run_dtls.return_name,
X_extract_phase => g_he_ext_run_dtls.extract_phase ,
X_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
X_conc_request_status => 'ERROR',
X_extract_run_date => TRUNC(SYSDATE),
X_file_name => g_he_ext_run_dtls.file_name ,
X_file_location => g_he_ext_run_dtls.file_location,
X_date_file_sent => g_he_ext_run_dtls.date_file_sent,
X_extract_override => g_he_ext_run_dtls.extract_override,
X_validation_kit_result => g_he_ext_run_dtls.validation_kit_result,
X_hesa_validation_result => g_he_ext_run_dtls.hesa_validation_result,
X_student_ext_run_id => g_he_ext_run_dtls.student_ext_run_id );