[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
apai.person_id,
apai.admission_appl_number,
apai.nominated_course_cd,
apai.sequence_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads,
igs_ad_appl aa,
igs_ad_appl_stat aps
WHERE
-- known person
apai.person_id = nvl(p_person_id,apai.person_id) AND
-- known person id group
(p_person_id_group IS NOT NULL AND
apai.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = NVL(p_person_id_group,pgm.group_id) AND
NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
or
(p_person_id_group is null)) AND
-- known academic/admission calendar period
aa.acad_cal_type = nvl(l_acad_cal_type,aa.acad_cal_type) AND
aa.acad_ci_sequence_number = nvl(l_acad_sequence_number,aa.acad_ci_sequence_number) AND
aa.adm_cal_type = nvl(l_adm_cal_type,aa.adm_cal_type) AND
aa.adm_ci_sequence_number = nvl(l_adm_sequence_number,aa.adm_ci_sequence_number) AND
-- known admission process category
aa.admission_cat = nvl(l_admission_cat,aa.admission_cat) AND
aa.s_admission_process_type = nvl(l_s_adm_process_typ,aa.s_admission_process_type) AND
-- known application instance
apai.nominated_course_cd = nvl(p_program_code,apai.nominated_course_cd) AND
apai.admission_appl_number = nvl(p_admission_appl_number,apai.admission_appl_number) AND
apai.sequence_number = nvl(p_sequence_number,apai.sequence_number) AND
-- regular joins
aos.s_adm_outcome_status = 'PENDING' AND
apai.adm_outcome_status = aos.adm_outcome_status AND
ads.s_adm_doc_status = 'PENDING' AND
apai.adm_doc_status = ads.adm_doc_status AND
aa.person_id=apai.person_id AND
aa.admission_appl_number = apai.admission_appl_number AND
NVL(apai.appl_inst_status,aps.adm_appl_status) = aps.adm_appl_status AND
aps.s_adm_appl_status <> 'WITHDRAWN' AND -- igsm arvsrini instance withdrawn
aps.closed_ind = 'N';
SELECT DISTINCT
apai.person_id,
apai.admission_appl_number,
apai.nominated_course_cd,
apai.sequence_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_ou_stat aos,
igs_ad_doc_stat ads,
igs_ad_appl aa,
igs_ad_appl_stat aps
WHERE
-- known person
apai.person_id = nvl(p_person_id,apai.person_id) AND
-- known person id group
(p_person_id_group IS NOT NULL AND
apai.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = NVL(p_person_id_group,pgm.group_id) AND
NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
or
(p_person_id_group is null)) AND
-- known academic/admission calendar period
aa.acad_cal_type = nvl(l_acad_cal_type,aa.acad_cal_type) AND
aa.acad_ci_sequence_number = nvl(l_acad_sequence_number,aa.acad_ci_sequence_number) AND
aa.adm_cal_type = nvl(l_adm_cal_type,aa.adm_cal_type) AND
aa.adm_ci_sequence_number = nvl(l_adm_sequence_number,aa.adm_ci_sequence_number) AND
-- known admission process category
aa.admission_cat = nvl(l_admission_cat,aa.admission_cat) AND
aa.s_admission_process_type = nvl(l_s_adm_process_typ,aa.s_admission_process_type) AND
-- known application instance
apai.nominated_course_cd = nvl(p_program_code,apai.nominated_course_cd) AND
apai.admission_appl_number = nvl(p_admission_appl_number,apai.admission_appl_number) AND
apai.sequence_number = nvl(p_sequence_number,apai.sequence_number) AND
-- regular joins
aos.s_adm_outcome_status IN ( 'OFFER', 'PENDING', 'COND-OFFER') AND
apai.adm_outcome_status = aos.adm_outcome_status AND
ads.s_adm_doc_status IN ( 'INCOMPLETE', 'NOT-APPLIC', 'PENDING', 'SATISFIED') AND
apai.adm_doc_status = ads.adm_doc_status AND
aa.person_id=apai.person_id AND
aa.admission_appl_number = apai.admission_appl_number AND
NVL(apai.appl_inst_status,aps.adm_appl_status) = aps.adm_appl_status AND
aps.s_adm_appl_status <> 'WITHDRAWN' AND -- igsm arvsrini instance withdrawn
aps.closed_ind = 'N'
AND igs_ad_gen_002.check_adm_appl_inst_stat(apai.person_id,apai.admission_appl_number,apai.nominated_course_cd,
apai.sequence_number,'Y')='Y';
SELECT tracking_status
FROM igs_tr_status
WHERE s_tracking_status = 'ACTIVE'
AND NVL(default_ind,'N') = 'Y'
AND closed_ind = 'N';
SELECT meaning
FROM igs_lookup_values
WHERE lookup_type = 'TRACKING_TYPE'
AND lookup_code = p_requirements_type;
SELECT party_number person_number
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT
NVL(TRUNC(IGS_CA_GEN_001.calp_set_alias_value(
cdi.absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai(
cdi.ci_sequence_number, cdi.CAL_TYPE, cdi.DT_ALIAS, cdi.sequence_number)
)), TRUNC(sysdate-1)) alias_val
FROM
igs_ad_ps_appl_inst apai,
igs_ad_cal_conf acc,
igs_ca_da_inst cdi
WHERE
-- Application Instance
apai.person_id = p_person_id AND
apai.admission_appl_number = p_admission_appl_number AND
apai.nominated_course_cd = p_program_code AND
apai.sequence_number = p_sequence_number AND
-- Calendar Instance
cdi.ci_sequence_number = apai.adm_ci_sequence_number AND
cdi.cal_type = apai.adm_cal_type AND
-- Date Alias
((cdi.dt_alias = acc.adm_prc_trk_dt_alias AND
p_requirements_type = 'ADM_PROCESSING') OR
(cdi.dt_alias = acc.post_adm_trk_dt_alias AND
p_requirements_type = 'POST_ADMISSION'));
SELECT
ar.rowid, ar.tracking_id, itt.tracking_type
FROM
igs_ad_aplins_admreq ar,
igs_tr_item itt,
igs_tr_type tt
WHERE
ar.person_id = p_person_id AND
ar.admission_appl_number = p_admission_appl_number AND
ar.course_cd = p_program_code AND
ar.sequence_number = p_sequence_number AND
itt.tracking_id = ar.tracking_id AND
itt.tracking_type = tt.tracking_type AND
tt.s_tracking_type = p_requirements_type AND
NOT EXISTS
(SELECT 'x'
FROM igs_tr_step its
WHERE its.tracking_id = ar.tracking_id AND
(its.step_completion_ind = 'Y' OR
its.by_pass_ind = 'Y') -- bug 2776548
)
FOR UPDATE OF itt.tracking_id NOWAIT;
SELECT
aa.admission_cat,
aa.s_admission_process_type,
apai.crv_version_number
FROM
igs_ad_ps_appl_inst apai,
igs_ad_appl aa
WHERE
apai.person_id = aa.person_id AND
apai.admission_appl_number = aa.admission_appl_number AND
apai.person_id = p_person_id AND
apai.admission_appl_number = p_admission_appl_number AND
apai.nominated_course_cd = p_program_code AND
apai.sequence_number = p_sequence_number;
SELECT ty.tracking_type
FROM igs_tr_type ty
WHERE ty.s_tracking_type = p_requirements_type
AND ty.closed_ind = 'N'
AND EXISTS ( SELECT 'X'
FROM igs_ad_appl aa,
igs_ad_ps_appl_inst apai,
igs_ad_prcs_cat_step apcs
WHERE apai.person_id = p_person_id
AND apai.admission_appl_number = p_admission_appl_number
AND apai.sequence_number = p_sequence_number
AND apai.nominated_course_cd = p_program_code
AND aa.person_id = apai.person_id
AND aa.admission_appl_number = apai.admission_appl_number
AND aa.admission_cat = apcs.admission_cat
AND aa.s_admission_process_type = apcs.s_admission_process_type
AND apcs.s_admission_step_type = ty.tracking_type
AND apcs.step_group_type = 'TRACK' );
SELECT ar.tracking_id
FROM igs_ad_aplins_admreq ar
WHERE ar.person_id = p_person_id
AND ar.admission_appl_number = p_admission_appl_number
AND ar.course_cd = p_program_code
AND ar.sequence_number = p_sequence_number
AND EXISTS ( SELECT 'X'
FROM igs_tr_item itt,
igs_tr_type tt
WHERE itt.tracking_id = ar.tracking_id
AND itt.tracking_type = tt.tracking_type
AND tt.s_tracking_type = p_requirements_type
AND tt.tracking_type = cp_tracking_type);
igs_ad_aplins_admreq_pkg.insert_row(
x_rowid => l_rowid,
x_aplins_admreq_id => l_aplins_admreq_id,
x_person_id => p_person_id,
x_admission_appl_number => p_admission_appl_number,
x_course_cd => p_program_code,
x_sequence_number => p_sequence_number,
x_tracking_id => l_tracking_id,
x_mode => 'R'
);
igs_ad_aplins_admreq_pkg.delete_row ( check_existence_rec.rowid);
log_detail(FND_MESSAGE.GET_STRING( 'IGS', 'IGS_AD_TRAC_NOT_REQ_DELETED'));
SELECT row_id
FROM igs_pe_person_addr
WHERE person_id = p_person_id;
SELECT row_id
FROM igs_pe_mil_services
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_pe_pers_disablty
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM IGS_PE_EIT
WHERE INFORMATION_TYPE = 'PE_STAT_RES_STATUS'
AND person_id = p_person_id
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
SELECT row_id
FROM igs_pe_citizenship_v
WHERE party_id = p_person_id;
SELECT row_id
FROM igs_pe_res_dtls
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_pe_teach_periods
WHERE person_id = p_person_id;
SELECT iatrdv.rowid row_id
FROM igs_ad_tst_rslt_dtls iatrdv
WHERE EXISTS (SELECT 'X'
FROM igs_ad_test_results iatr
WHERE iatr.person_id = p_person_id
AND iatrdv.test_results_id = iatr.test_results_id );
SELECT row_id
FROM igs_ad_acad_history_v
WHERE person_id = p_person_id;
SELECT rowid
FROM HZ_PERSON_INTEREST PI
WHERE party_ID = p_person_id;
SELECT rowid row_id
FROM igs_ad_test_results
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_app_intent
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_acad_interest
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_spl_interests
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_spl_talents
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_unit_sets
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_ad_other_inst
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_pe_athletic_prg
WHERE person_id = p_person_id;
SELECT rowid row_id
FROM igs_pe_acad_honors
WHERE person_id = p_person_id;
SELECT rul_sequence_number
FROM igs_ad_apctr_ru
WHERE admission_cat = p_admission_cat
AND s_admission_process_type = p_admission_process_type
AND s_rule_call_cd = p_adm_rule_type;
SELECT p_rul_seq_num, item.NAMED_RULE, 'Y'
FROM IGS_RU_ITEM item
WHERE item.RUL_SEQUENCE_NUMBER = p_rul_seq_num
AND item.NAMED_RULE in(30171, 30172, 30173, 30174, 30175,30176,30177,30178, 30174, -- Adreess
30181,30182,30183,30184, -- Military Services
30185, -- Person Disability
30186, -- Citizenship status (Bug Person Stats )
30187, -- Citizenship
30188, 30189, -- Pe Results
30190, -- Pe Teaching Periods
30246,30247,30248,30249,30250,30251,30252,30253,30254, -- AD Test Results Details
30192,30193,30194,30195,30196,30197,30198,30199,30200,30201,30202,30203,30204,30205,30206,30207,53211 -- Acad Hist
30208,30209,30210,30211,30212,30213,30230,30231,30232,30233 -- Extra Curicular activities
30236,30237,30238,30239,30240,30241,30242,30243,30244,30245,30248,30249,30250,30251,30252,30253,30254, -- AD Test Results
30275 , -- App Intent
30276, -- Ad Acad Interest
30277, -- Spl Interest
30278, -- Spl Talent
30279,30280, -- UnitSets
30281, -- Other Institutions
30282,30283, -- Athletic PRG
30286,30287 -- Pe Acad Honors
); */
SELECT RULE_NUMBER FROM IGS_RU_ITEM item
WHERE item.RUL_SEQUENCE_NUMBER = p_rul_seq_num
AND rule_number is NOT NULL
ORDER BY item desc;
' SELECT 1 FROM IGS_RU_ITEM item ' ||
' WHERE item.RUL_SEQUENCE_NUMBER = :1 ' ||
' AND item.NAMED_RULE in (' || p_rul_item_seq_num_set || ') '
USING p_rul_seq_num ;