[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.subject_code
FROM igs_uc_qual_dets a
WHERE a.person_id = p_person_id
AND EXISTS (SELECT 'X'
FROM igs_he_code_values b
WHERE b.value = a.exam_level
AND b.code_type = 'OSS_QUAL_1ST_DEGREE'
AND NVL(b.closed_ind,'N') = 'N' )
ORDER BY a.year DESC;
SELECT govt_field_of_study
FROM IGS_PS_FLD_OF_STUDY PFS
WHERE field_of_study = p_subject;
p_value_from IN Column name of what to select from
E.g 'map1' or 'map2' etc.
p_return_value OUT NOCOPY Return Value
Change History :
Who When What
jchakrab 05-FEB-2005 Modified for 4006205 - SQL queries using literals have been
modified to use bind variables.
Added new internal procedure - get_map_values_from_SQL()
----------------------------------------------------------------------*/
PROCEDURE get_map_values
(p_he_code_map_val IN igs_he_code_map_val%ROWTYPE,
p_value_from IN VARCHAR2,
p_return_value OUT NOCOPY igs_he_code_map_val.map1%TYPE)
IS
CURSOR cur_map1 (p_assoc igs_he_code_map_val.association_code%TYPE ,
p_map2 igs_he_code_map_val.map2%TYPE ) IS
SELECT map1
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map2 = p_map2;
SELECT map2
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map1 = p_map1;
SELECT map3
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map2 = p_map2;
SELECT map1
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map2 = p_map2
AND map3 = p_map3;
SELECT map1
FROM igs_he_code_map_val
WHERE association_code = p_assoc
AND map2 = p_map2
AND map3 = p_map3
AND map4 = p_map4;
p_value_from IN Column name of what to select from
E.g 'map1' or 'map2' etc.
p_return_value OUT NOCOPY Return Value
Known limitations,enhancements,remarks:
CHANGE HISTORY:
WHO WHEN WHAT
******************************************************************************/
TYPE cur_mapval IS REF CURSOR;
l_sql_stmt := ' SELECT '||p_value_from ||
' FROM igs_he_code_map_val '||
' WHERE association_code = '''||p_he_code_map_val.association_code ||'''';
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.
Change History :
Who When What
jchakrab 05-FEB-2005 Modified for 4006205 - Removed Autonomous Transaction.
Replaced TBH call to insert_row() with direct DML.
----------------------------------------------------------------------*/
PROCEDURE log_error
(p_he_ext_run_exceptions IN OUT NOCOPY igs_he_ext_run_excp%ROWTYPE)
IS
l_rowid VARCHAR2(30) ;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date := SYSDATE;
l_last_updated_by := NVL(fnd_global.user_id, -1);
l_last_update_login := NVL(fnd_global.login_id, -1);
INSERT INTO igs_he_ext_run_excp (
ext_exception_id,
extract_run_id,
person_id,
person_number,
course_cd,
crv_version_number,
unit_cd,
uv_version_number,
line_number,
field_number,
exception_reason,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
igs_he_ext_run_excp_s.NEXTVAL,
p_he_ext_run_exceptions.Extract_Run_Id,
p_he_ext_run_exceptions.Person_Id,
p_he_ext_run_exceptions.Person_Number,
p_he_ext_run_exceptions.Course_Cd,
p_he_ext_run_exceptions.Crv_Version_Number,
p_he_ext_run_exceptions.Unit_Cd,
p_he_ext_run_exceptions.Uv_Version_Number,
p_he_ext_run_exceptions.Line_Number,
p_he_ext_run_exceptions.Field_Number,
p_he_ext_run_exceptions.Exception_Reason,
l_last_update_date,
l_last_updated_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
sjlaport 31 May 2005 Modified cursor c_fld_defn to select the mandatory_flag
field from the system return class definition and to
exclude fields not included in the user return class
jbaber 15 Mar 2006 Modified c_fld_defn to use report_null_flag instead of
mandatory_flag as per HE365 - Extract Rerun
----------------------------------------------------------------------*/
PROCEDURE get_field_defn
(p_extract_run_id IN igs_he_ext_run_dtls.extract_run_id%TYPE)
IS
CURSOR c_ext_dtl IS
SELECT a.submission_name,
a.user_return_subclass,
a.return_name ,
a.extract_phase,
a.student_ext_run_id,
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,
c.apply_to_atmpt_st_dt,
c.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 hefld.field_number,
hefld.constant_val,
hefld.default_val,
hefld.report_null_flag
FROM igs_he_usr_rt_cl_fld hefld,
igs_he_usr_rtn_clas hecls
WHERE hefld.user_return_subclass = p_usr_return_subclass
AND hefld.user_return_subclass = hecls.user_return_subclass
AND hefld.include_flag = 'Y';
SELECT govt_field_of_study
FROM igs_ps_fld_of_study
WHERE field_of_study = cp_field_of_study;
SELECT govt_field_of_study
FROM igs_ps_fld_of_study
WHERE field_of_study = cp_field_of_study;
Replaced call to TBH insert_row() with direct DML
sjlaport 31-May-05 Modified for 4304808 - Error message only created for mandatory
fields derived with null value. Removed check for included fields.
jbaber 15-Mar-2006 Update recalculated fields for HE365 - Extract Rerun
***************************************************************/
l_rowid VARCHAR2(30);
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
g_field_defn.hesa_value.delete ;
g_field_defn.oss_value.delete ;
l_last_update_date := SYSDATE;
l_last_updated_by := NVL(fnd_global.user_id,-1);
l_last_update_login := NVL(fnd_global.login_id,-1);
UPDATE igs_he_ex_rn_dat_fd
SET value = l_value
WHERE extract_run_id = p_extract_run_id
AND line_number = p_line_number
AND field_number = g_field_defn.field_number(l_index);
INSERT INTO igs_he_ex_rn_dat_fd (
rn_dat_fd_id,
extract_run_id,
line_number,
field_number,
value,
override_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
IGS_HE_EX_RN_DAT_FD_S.NEXTVAL,
p_extract_run_id,
p_line_number,
g_field_defn.field_number(l_index),
l_value,
NULL,
l_last_update_date,
l_last_updated_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
It will select all the required details and then call the individual
procedures to derive the field values.
Parameters :
p_extract_run_id The Extract Run Id
p_person_id Person_id for the student
p_course_cd Course Code that the student is attempting
p_crv_version_number Version Number of the course code
p_student_inst_number Student Instance Number
p_line_number Line Number of the current line being processed
----------------------------------------------------------------------*/
FUNCTION process_comb_stdnt_return
(p_extract_run_id igs_he_ext_run_dtls.extract_run_id%TYPE,
p_person_id igs_he_ex_rn_dat_ln.person_id%TYPE,
p_course_cd igs_he_ex_rn_dat_ln.course_cd%TYPE,
p_crv_version_number igs_he_ex_rn_dat_ln.crv_version_number%TYPE,
p_student_inst_number igs_he_ex_rn_dat_ln.student_inst_number%TYPE,
p_line_number igs_he_ex_rn_dat_ln.line_number%TYPE)
RETURN BOOLEAN
/***************************************************************
Created By :
Date Created By :
Known Limitations,Enhancements or Remarks:
Change History :
Who When What
pkpatel 27-MAR-2003 Bug No: 2261717
These 2 columns are obsolete from igs_pe_person_v and here they are not being used for any processing.
Hence removed to avoid confusion.
smaddali 3-dec-03 modified cursors c_yop 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 logic not to stop processing student when igs_pe_stats record is not found : bug#3361317
ayedubat 11-may-04 Modified the cursor, c_pe_stats to remove the effective dates comparision for Bug, 3614658
jbaber 20-sep-04 Modified c_spa, c_pe_stats, c_yop, c_crse as per HE350 - Statutory Changes for 2004/05 Reporting
jtmathew 23-dec-05 Modified c_spa, c_yop for HE309
jchin 20-jan-06 Modified c_pe_stats and c_pers cursor queries for bug 4251011, 3717086 and 4250923
***************************************************************/
IS
-- smaddali selecting version_number for HECR214 build
CURSOR c_spa IS
SELECT sca.version_number,
sca.cal_type,
sca.location_cd ,
sca.attendance_mode,
sca.attendance_type,
sca.coo_id ,
sca.student_confirmed_ind,
sca.commencement_dt ,
sca.course_attempt_status,
sca.progression_status ,
sca.discontinued_dt,
sca.discontinuation_reason_cd,
sca.funding_source ,
sca.exam_location_cd,
sca.course_rqrmnt_complete_ind,
sca.course_rqrmnts_complete_dt,
sca.override_time_limitation,
sca.advanced_standing_ind,
sca.fee_cat,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number,
hspa.fe_student_marker,
hspa.domicile_cd,
hspa.inst_last_attended,
hspa.year_left_last_inst ,
hspa.highest_qual_on_entry ,
hspa.date_qual_on_entry_calc ,
hspa.a_level_point_score,
hspa.highers_points_scores ,
hspa.occupation_code,
hspa.commencement_dt,
hspa.special_student,
hspa.student_qual_aim,
hspa.student_fe_qual_aim ,
hspa.teacher_train_prog_id ,
hspa.itt_phase,
hspa.bilingual_itt_marker ,
hspa.teaching_qual_gain_sector ,
hspa.teaching_qual_gain_subj1,
hspa.teaching_qual_gain_subj2,
hspa.teaching_qual_gain_subj3,
hspa.hesa_return_name,
hspa.hesa_return_id,
hspa.hesa_submission_name,
hspa.associate_ucas_number,
hspa.associate_scott_cand ,
hspa.associate_teach_ref_num,
hspa.associate_nhs_reg_num,
hspa.itt_prog_outcome,
hspa.nhs_funding_source ,
hspa.ufi_place,
hspa.postcode ,
hspa.social_class_ind ,
hspa.destination,
hspa.occcode,
hspa.total_ucas_tariff ,
hspa.nhs_employer,
hspa.return_type,
hspa.student_inst_number,
hspa.qual_aim_subj1 ,
hspa.qual_aim_subj2 ,
hspa.qual_aim_subj3 ,
hspa.qual_aim_proportion,
hspa.dependants_cd,
hspa.enh_fund_elig_cd,
hspa.implied_fund_rate,
hspa.gov_initiatives_cd,
hspa.units_completed,
hspa.units_for_qual,
hspa.disadv_uplift_elig_cd,
hspa.disadv_uplift_factor,
hspa.franch_out_arr_cd,
hspa.employer_role_cd,
hspa.franch_partner_cd,
pst.course_type
FROM igs_en_stdnt_ps_att_all sca,
igs_he_st_spa_all hspa,
igs_ps_ver_all psv,
igs_ps_type_all pst
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd
AND sca.version_number = p_crv_version_number
AND sca.person_id = hspa.person_id
AND sca.course_cd = hspa.course_cd
AND psv.course_cd = p_course_cd
AND psv.version_number = p_crv_version_number
AND psv.course_type = pst.course_type;
SELECT P.PARTY_NUMBER PERSON_NUMBER,
P.PARTY_NAME PERSON_NAME,
P.PERSON_LAST_NAME SURNAME,
P.PERSON_FIRST_NAME GIVEN_NAMES,
P.PERSON_MIDDLE_NAME MIDDLE_NAME,
P.PERSON_TITLE TITLE,
NVL (P.KNOWN_AS,
SUBSTR (P.PERSON_FIRST_NAME, 1, DECODE (INSTR (P.PERSON_FIRST_NAME, ' '), 0, LENGTH (P.PERSON_FIRST_NAME),
(INSTR (P.PERSON_FIRST_NAME, ' ') - 1))))
|| ' '
|| P.PERSON_LAST_NAME PREFERRED_NAME,
P.KNOWN_AS PREFERRED_GIVEN_NAME,
PP.GENDER SEX,
PP.DATE_OF_BIRTH BIRTH_DT,
PP.PERSON_NAME FULL_NAME
FROM HZ_PARTIES P,
HZ_PERSON_PROFILES PP
WHERE P.PARTY_ID = PP.PARTY_ID
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL (PP.EFFECTIVE_END_DATE, SYSDATE)
AND P.PARTY_ID = P_PERSON_ID;
SELECT HPP.DECLARED_ETHNICITY ETHNIC_ORIGIN_ID,
SD.RELIGION_CD RELIGION,
HPP.MARITAL_STATUS MARITAL_STATUS
FROM HZ_PERSON_PROFILES HPP,
IGS_PE_STAT_DETAILS SD
WHERE HPP.PARTY_ID = SD.PERSON_ID(+)
AND HPP.PARTY_ID = P_PERSON_ID
AND SYSDATE BETWEEN HPP.EFFECTIVE_START_DATE
AND NVL(HPP.EFFECTIVE_END_DATE, SYSDATE);
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.new_he_entrant_cd,
husa.term_time_accom ,
husa.disability_allow,
husa.additional_sup_band,
husa.sldd_discrete_prov,
husa.study_mode,
husa.study_location ,
husa.fte_perc_override,
husa.franchising_activity,
husa.completion_status,
husa.good_stand_marker,
husa.complete_pyr_study_cd,
husa.credit_value_yop1,
husa.credit_value_yop2,
husa.credit_value_yop3,
husa.credit_value_yop4,
husa.credit_level_achieved1,
husa.credit_level_achieved2,
husa.credit_level_achieved3,
husa.credit_level_achieved4,
husa.credit_pt_achieved1,
husa.credit_pt_achieved2,
husa.credit_pt_achieved3,
husa.credit_pt_achieved4,
husa.credit_level1,
husa.credit_level2,
husa.credit_level3,
husa.credit_level4,
husa.grad_sch_grade,
husa.mark,
husa.teaching_inst1,
husa.teaching_inst2,
husa.pro_not_taught,
husa.fundability_code,
husa.fee_eligibility,
husa.fee_band,
husa.non_payment_reason,
husa.student_fee,
husa.calculated_fte,
husa.fte_intensity,
husa.type_of_year,
husa.year_stu,
husa.enh_fund_elig_cd,
husa.additional_sup_cost,
husa.disadv_uplift_factor
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.person_id = p_person_id
AND susa.course_cd = p_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 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_end_date AND
(sca.discontinued_dt IS NULL OR sca.discontinued_dt >= p_start_date ) AND
(sca.course_rqrmnts_complete_dt IS NULL OR sca.course_rqrmnts_complete_dt >= p_start_date ) AND
susa.selection_dt <= p_end_date AND
(susa.end_dt IS NULL OR susa.end_dt >= p_start_date ) AND
(susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= p_start_date)
)
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_start_date AND
sca.course_rqrmnts_complete_dt <= p_end_date 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,
pop.program_length,
pop.program_length_measurement,
crv.contact_hours,
crv.govt_special_course_type,
hpr.teacher_train_prog_id,
hpr.itt_phase ,
hpr.bilingual_itt_marker ,
hpr.teaching_qual_sought_sector,
hpr.teaching_qual_sought_subj1,
hpr.teaching_qual_sought_subj2,
hpr.teaching_qual_sought_subj3,
hpr.location_of_study ,
hpr.other_inst_prov_teaching1,
hpr.other_inst_prov_teaching2,
hpr.prop_teaching_in_welsh ,
hpr.prop_not_taught,
hpr.credit_transfer_scheme ,
hpr.return_type,
hpr.default_award,
Nvl(hpr.program_calc,'N') ,
hpr.level_applicable_to_funding,
hpr.franchising_activity,
hpr.nhs_funding_source,
hpr.fe_program_marker,
hpr.fee_band ,
hpr.fundability,
hpr.implied_fund_rate,
hpr.gov_initiatives_cd,
hpr.units_for_qual,
hpr.disadv_uplift_elig_cd,
hpr.franch_out_arr_cd,
hpud.location_of_study,
hpud.mode_of_study,
hpud.ufi_place ,
hpud.franchising_activity,
hpud.type_of_year,
hpud.leng_current_year,
hpud.grading_schema_cd,
hpud.gs_version_number,
hpud.credit_value_yop1,
hpud.level_credit1 ,
hpud.credit_value_yop2,
hpud.level_credit2 ,
hpud.credit_value_yop3,
hpud.level_credit3 ,
hpud.credit_value_yop4,
hpud.level_credit4 ,
hpud.fte_intensity ,
hpud.other_instit_teach1,
hpud.other_instit_teach2,
hpud.prop_not_taught,
hpud.fundability_cd,
hpud.fee_band,
hpud.level_applicable_to_funding,
hpud.funding_source
FROM igs_ps_ver crv,
igs_he_st_prog hpr,
igs_he_poous hpud,
igs_ps_ofr_opt pop
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 = cp_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
AND pop.course_cd = p_course_cd
AND pop.version_number = cp_crv_version_number
AND pop.cal_type = p_cal_type
AND pop.attendance_mode = p_attendance_mode
AND pop.attendance_type = p_attendance_type
AND pop.location_cd = p_location_cd ;
SELECT had.occupation_cd,
had.domicile_cd,
had.social_class_cd ,
had.special_student_cd
FROM igs_he_ad_dtl had
WHERE had.person_id = p_person_id
AND had.admission_appl_number = p_admission_appl_number
AND had.nominated_course_cd = p_nominated_course_cd
AND had.sequence_number = p_sequence_number;
SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type, tr.fee_cat
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 , tr.fee_cat
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, tr.fee_cat
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;
SELECT party_number person_number
FROM hz_parties
WHERE party_id = cp_person_id ;
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.new_he_entrant_cd,
g_he_en_susa.term_time_accom ,
g_he_en_susa.disability_allow,
g_he_en_susa.additional_sup_band,
g_he_en_susa.sldd_discrete_prov,
g_he_en_susa.study_mode,
g_he_en_susa.study_location ,
g_he_en_susa.fte_perc_override,
g_he_en_susa.franchising_activity,
g_he_en_susa.completion_status,
g_he_en_susa.good_stand_marker,
g_he_en_susa.complete_pyr_study_cd,
g_he_en_susa.credit_value_yop1,
g_he_en_susa.credit_value_yop2,
g_he_en_susa.credit_value_yop3,
g_he_en_susa.credit_value_yop4,
g_he_en_susa.credit_level_achieved1,
g_he_en_susa.credit_level_achieved2,
g_he_en_susa.credit_level_achieved3,
g_he_en_susa.credit_level_achieved4,
g_he_en_susa.credit_pt_achieved1,
g_he_en_susa.credit_pt_achieved2,
g_he_en_susa.credit_pt_achieved3,
g_he_en_susa.credit_pt_achieved4,
g_he_en_susa.credit_level1,
g_he_en_susa.credit_level2,
g_he_en_susa.credit_level3,
g_he_en_susa.credit_level4,
g_he_en_susa.grad_sch_grade,
g_he_en_susa.mark,
g_he_en_susa.teaching_inst1,
g_he_en_susa.teaching_inst2,
g_he_en_susa.pro_not_taught,
g_he_en_susa.fundability_code,
g_he_en_susa.fee_eligibility,
g_he_en_susa.fee_band,
g_he_en_susa.non_payment_reason,
g_he_en_susa.student_fee,
g_he_en_susa.calculated_fte,
g_he_en_susa.fte_intensity,
g_he_en_susa.type_of_year,
g_he_en_susa.year_stu,
g_he_en_susa.enh_fund_elig_cd,
g_he_en_susa.additional_sup_cost,
g_he_en_susa.disadv_uplift_factor;
It will select all the required details and then call the individual
procedures to derive the field values.
Parameters :
p_extract_run_id The Extract Run Id
p_unit_cd Unit Code
p_uv_version_number Version Number of the Unit Code
p_line_number Line Number of the current line being processed
----------------------------------------------------------------------*/
FUNCTION process_module_return
(p_extract_run_id igs_he_ext_run_dtls.extract_run_id%TYPE,
p_unit_cd igs_he_ex_rn_dat_ln.unit_cd%TYPE,
p_uv_version_number igs_he_ex_rn_dat_ln.uv_version_number%TYPE,
p_line_number igs_he_ex_rn_dat_ln.line_number%TYPE)
RETURN BOOLEAN
IS
--smaddali modified the order of columns because they donot match that of the Fetch statement bug 2417454
CURSOR c_moddtl IS
SELECT a.prop_of_teaching_in_welsh ,
a.credit_transfer_scheme ,
a.module_length ,
a.proportion_of_fte,
a.location_cd ,
b.title,
b.enrolled_credit_points,
b.unit_level
FROM igs_he_st_unt_vs a,
igs_ps_unit_ver_v b
WHERE a.unit_cd = b.unit_cd
AND a.version_number = b.version_number
AND a.unit_cd = p_unit_cd
AND a.version_number = p_uv_version_number;
It will select all the required details and then call the individual
procedures to derive the field values.
Parameters :
p_extract_run_id The Extract Run Id
p_person_id Person Id
p_line_number Line Number of the current line being processed
----------------------------------------------------------------------*/
FUNCTION process_dlhe_return
(p_extract_run_id igs_he_ext_run_dtls.extract_run_id%TYPE,
p_person_id igs_he_ex_rn_dat_ln.person_id%TYPE,
p_line_number igs_he_ex_rn_dat_ln.line_number%TYPE)
RETURN BOOLEAN
IS
/***************************************************************
Created By : smaddali
Date Created By : 9-apr-03
Purpose :This procedure does the processing for a DLHE Return
Known Limitations,Enhancements or Remarks:
Change History :
Who When What
***************************************************************/
CURSOR c_dlhe_dtls(cp_submission_name igs_he_submsn_return.submission_name%TYPE ,
cp_return_name igs_he_submsn_return.return_name%TYPE ) IS
SELECT *
FROM igs_he_stdnt_dlhe
WHERE person_id = p_person_id
AND submission_name = cp_submission_name
AND return_name = cp_return_name ;
SELECT pe.party_number person_number
FROM hz_parties pe
WHERE pe.party_id = p_person_id;
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
----------------------------------------------------------------------*/
PROCEDURE process_temp_table
(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.
Known Limitations,Enhancements or Remarks:
Change History :
Who When What
smaddali 09-apr-03 modified procedure for HEFD203 build , bug 2717745
sjlaport 03-Jun-05 Cache translated error message IGS_HE_EXT_FLD_VAL_NULL bug 4304808
jbaber 15-Mar-06 Better support for recalculated records as per HE365 - Exract Rerun
***************************************************************/
CURSOR c_get_temp_rows IS
SELECT rowid,
ext_interim_id,
person_id,
course_cd,
crv_version_number ,
unit_cd,
uv_version_number,
student_inst_number,
line_number
FROM igs_he_ext_run_interim
WHERE extract_run_id = p_extract_run_id;
SELECT party_number person_number
FROM hz_parties
WHERE party_id = cp_person_id ;
SELECT MAX(line_number)
INTO l_line_number
FROM igs_he_ex_rn_dat_ln
WHERE extract_run_id = p_extract_run_id;
l_he_ex_rn_dat_ln.manually_inserted := 'N';
Igs_He_Ex_Rn_Dat_Ln_Pkg.Insert_Row
(X_rowid => l_rowid,
X_rn_dat_ln_id => l_he_ex_rn_dat_ln.rn_dat_ln_id,
X_person_id => l_he_ex_rn_dat_ln.person_id,
X_course_cd => l_he_ex_rn_dat_ln.course_cd,
X_crv_version_number => l_he_ex_rn_dat_ln.crv_version_number,
X_student_inst_number => l_he_ex_rn_dat_ln.student_inst_number,
X_unit_cd => l_he_ex_rn_dat_ln.unit_cd,
X_uv_version_number => l_he_ex_rn_dat_ln.uv_version_number,
X_extract_run_id => l_he_ex_rn_dat_ln.extract_run_id,
X_record_id => l_he_ex_rn_dat_ln.record_id,
X_line_number => l_he_ex_rn_dat_ln.line_number,
X_manually_inserted => l_he_ex_rn_dat_ln.manually_inserted,
X_exclude_from_file => l_he_ex_rn_dat_ln.exclude_from_file,
X_recalculate_flag => l_he_ex_rn_dat_ln.recalculate_flag);
UPDATE igs_he_ex_rn_dat_ln
SET last_update_date = sysdate,
recalculate_flag = 'Y'
WHERE extract_run_id = p_extract_run_id
AND line_number = l_he_ex_rn_dat_ln.line_number;
DELETE FROM igs_he_ext_run_interim WHERE ext_interim_id = l_rec_list(l_rec_cnt);
DELETE FROM igs_he_ext_run_interim WHERE extract_run_id = p_extract_run_id;