[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) n_count
FROM igs_pe_person_base_v
WHERE person_id = l_per_id;
SELECT match_set_id
FROM igs_pe_match_sets
WHERE source_type_id = p_source_type_id;
pmarada 26-dec-02 Bug 2726132, i)Removed the igs_he_st_spa_all_pkg.update_row.
ii)creating record in igs_uc_attend_hist if the record not exist else updating.
ayedubat 31-DEC-2002 Fixed all the issues as mentioned in the bug, 2727487
rbezawad 19-Sep-2003 Modified the process w.r.t. UCFD210 Build, Bug 2893542 to populate the Previous education details into
OSS Academic History and obsolete the functionality related to IGS_UC_ATTEND_HIST.
rgangara 30-Jan-2004 Modified cur_all_applicants cursor to check for Sent_to_hesa flag from IGS_UC_APP_STATS instead of
IGS_UC_APPLICANTS. The Sent_to_hesa is for all practical purposes obsolete. Also removed update of
Applicant's.Sent_to_hesa as it is no more required as part of bug 3405245
arvsrini 27-Jul-2004 Added code to shift the exporting ethnic code logic from IGSUC44B.pls to the current process.
Included logic to export ethnic details in case of the same person having multiple information coming from
different systems Bug#3796641
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
***************************************************************/
/* smaddali added this cursor to get the person_number to display in the log file , bug 2497516 */
CURSOR c_person ( cp_person_id igs_pe_person.person_id%TYPE ) IS
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id ;
/* cursor to select all applicants and details whose details have not been exported to HESA */
CURSOR cur_all_applicants(cp_person_id igs_pe_person.person_id%TYPE ) IS
SELECT app.app_no,
app.app_id,
app.oss_person_id,
TO_CHAR(app.domicile_apr) domicile_apr,
app.system_code,
app.country_birth,
stat.starh_pocc,
stat.starh_socio_economic,
stat.starh_pocc_edu_chg_dt
FROM igs_uc_applicants app, igs_uc_app_stats stat
WHERE app.app_no = stat.app_no
AND oss_person_id = NVL(cp_person_id, oss_person_id)
AND stat.sent_to_hesa = 'N'
ORDER BY app.system_code, app.app_no ;
SELECT system_code
FROM igs_uc_defaults
WHERE prev_inst_left_date IS NULL
AND system_code IN ( SELECT DISTINCT app.system_code
FROM igs_uc_applicants app, igs_uc_app_stats stat
WHERE app.app_no = stat.app_no
AND oss_person_id = NVL(cp_person_id, oss_person_id)
AND stat.sent_to_hesa = 'N' );
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type = 'UCAS PER'
AND NVL(closed_ind,'N') = 'N';
SELECT 'X'
FROM igs_ad_source_cat_v
WHERE source_type_id = cp_source_type_id
AND category_name = cp_category
AND include_ind = 'Y';
SELECT map2
FROM igs_he_code_map_val
WHERE association_code = cp_association_code
AND map1 = cp_map1 ;
SELECT had.ROWID ,had.*
FROM igs_he_ad_dtl_all had
WHERE person_id = l_per_id;
SELECT course_cd, version_number
FROM igs_he_st_spa_all hestspa
WHERE person_id = l_per_id;
SELECT app.ROWID , app.*
FROM igs_uc_applicants app
WHERE app.app_id = p_app_id ;
SELECT apst.ROWID , apst.*
FROM igs_uc_app_stats apst
WHERE apst.app_id = p_app_id ;
SELECT admission_appl_number,
nominated_course_cd,
sequence_number
FROM igs_ad_ps_appl_inst_all
WHERE person_id = p_per_id;
SELECT COUNT(*)
FROM igs_he_ad_dtl_all
WHERE person_id = p_per_id;
SELECT igs_ad_interface_batch_id_s.NEXTVAL
FROM dual;
SELECT person_number, last_name surname, first_name given_names, gender sex, birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT a.person_number, a.interface_id
FROM igs_ad_interface a
WHERE a.batch_id = cp_batch_id
AND ( a.status IN ('2','3') OR a.record_status='3' ) ;
SELECT app.system_code,app.app_no
FROM igs_uc_applicants app, igs_uc_app_stats stat
WHERE app.app_no = stat.app_no
AND oss_person_id = cp_person_id
ORDER BY app.system_code DESC;
SELECT a.*
FROM igs_ad_stat_int_all a
WHERE a.interface_id = cp_interface_id
AND a.status = '3';
/* Fetch the fields in the tables for this particular person_id and call the TBH to insert/update the table */
-- check if there is a record for the passed person in igs_he_ad_dtl_all ,
-- if not then create a new record from the corresponding OSS admission record for the person
-- smaddali start replacing the select statement with a cursor
l_count :=0 ;
igs_he_ad_dtl_all_pkg.insert_row(
x_rowid => l_rowid,
x_org_id => NULL,
x_hesa_ad_dtl_id => l_count,
x_person_id => all_appl_rec.oss_person_id,
x_admission_appl_number => lv_ad_appl_inst.admission_appl_number,
x_nominated_course_cd => lv_ad_appl_inst.nominated_course_cd,
x_sequence_number => lv_ad_appl_inst.sequence_number,
x_occupation_cd => l_occ_code,
x_domicile_cd => l_dom_cd,
x_social_class_cd => l_soc_code,
x_special_student_cd => NULL,
x_mode => 'R' );
igs_he_ad_dtl_all_pkg.update_row (
x_mode => 'R',
x_rowid => had_rec.ROWID,
x_org_id => had_rec.org_id,
x_hesa_ad_dtl_id => had_rec.hesa_ad_dtl_id,
x_person_id => had_rec.person_id,
x_admission_appl_number => had_rec.admission_appl_number,
x_nominated_course_cd => had_rec.nominated_course_cd,
x_sequence_number => had_rec.sequence_number,
x_occupation_cd => NVL(l_occ_code,had_rec.occupation_cd ),
x_domicile_cd => NVL(l_dom_cd,had_rec.domicile_cd ),
x_social_class_cd => NVL(l_soc_code,had_rec.social_class_cd ),
x_special_student_cd => had_rec.special_student_cd );
INSERT INTO igs_ad_imp_batch_det ( batch_id,
batch_desc,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id)
VALUES ( l_imp_batch_id,
fnd_message.get_string('IGS','IGS_UC_IMP_ACAD_HIST_BATCH_ID'),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
);
INSERT INTO igs_ad_interface(person_number,
interface_id,
batch_id,
source_type_id,
person_id,
surname,
given_names,
sex,
birth_dt,
status,
record_status,
match_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id)
VALUES(l_person_info_rec.person_number,
igs_ad_interface_s.NEXTVAL,
l_imp_batch_id,
l_src_type_id_rec.source_type_id,
all_appl_rec.oss_person_id,
l_person_info_rec.surname,
l_person_info_rec.given_names,
l_person_info_rec.sex,
l_person_info_rec.birth_dt,
'1', --status
'2', --record_status,
'15', --Match_Ind
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id) )
RETURNING interface_id INTO l_interface_id;
INSERT INTO igs_ad_stat_int_all (
interface_stat_id
,interface_id
,ethnic_origin
,religion_cd
,status
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES(
IGS_AD_STAT_INT_S.NEXTVAL,
l_interface_id,
l_oss_ethnic_origin,
l_oss_religion_cd,
'2',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id )
RETURNING interface_stat_id INTO l_interface_stat_id;
igs_uc_app_stats_pkg.update_row(
X_ROWID => j.ROWID ,
X_APP_STAT_ID => j.app_stat_id ,
X_APP_ID => j.app_id ,
X_APP_NO => j.app_no ,
X_STARH_ETHNIC => j.starh_ethnic ,
X_STARH_SOCIAL_CLASS => j.starh_social_class ,
X_STARH_POCC_EDU_CHG_DT => j.starh_POCC_edu_chg_dt ,
X_STARH_POCC => j.starh_POCC ,
X_STARH_POCC_TEXT => j.starh_POCC_text ,
X_STARH_LAST_EDU_INST => j.starh_last_edu_inst ,
X_STARH_EDU_LEAVE_DATE => j.starh_edu_leave_date ,
X_STARH_LEA => j.starh_LEA ,
X_STARX_ETHNIC => j.starx_ethnic ,
X_STARX_POCC_EDU_CHG => j.starx_POCC_edu_chg ,
X_STARX_POCC => j.starx_POCC ,
X_STARX_POCC_TEXT => j.starx_POCC_text ,
X_SENT_TO_HESA => 'Y' ,
X_MODE => 'R' ,
-- 2-apr-2002 smaddali added these 3 new columns for UCCR002 bug#2278817
X_STARH_SOCIO_ECONOMIC => j.starh_socio_economic ,
X_STARX_SOCIO_ECONOMIC => j.starx_socio_economic ,
X_STARX_OCC_BACKGROUND => j.starx_occ_background,
-- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
x_ivstarh_dependants => j.ivstarh_dependants,
x_ivstarh_married => j.ivstarh_married,
x_ivstarx_religion => j.ivstarx_religion,
x_ivstarx_dependants => j.ivstarx_dependants,
x_ivstarx_married => j.ivstarx_married );