[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL)) AND
(p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null)) AND
(aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
Where
aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
Where aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL
AND aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL
AND aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
(
select
r.sup_cal_type,
r.sup_ci_sequence_number,
r.sub_cal_type,
r.sub_ci_sequence_number
from
igs_ca_inst_rel r
, igs_ca_type t1
, igs_ca_type t2
, igs_ca_inst acad
, igs_ca_inst adm
where
sup_cal_type = t1.cal_type
and t1.s_cal_cat = 'ACADEMIC'
and sub_cal_type = t2.cal_type
and t2.s_cal_cat = 'ADMISSION'
and sup_cal_type = acad.cal_type
and sup_ci_sequence_number = acad.sequence_number
and sub_cal_type = adm.cal_type
and sub_ci_sequence_number = adm.sequence_number
and acad.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_acad_cal
and ci.sequence_number = l_acad_cal_seq_num)
and adm.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_adm_cal
and ci.sequence_number = l_adm_cal_seq_num)
)
)
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL
AND aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
(
select
r.sup_cal_type,
r.sup_ci_sequence_number,
r.sub_cal_type,
r.sub_ci_sequence_number
from
igs_ca_inst_rel r
, igs_ca_type t1
, igs_ca_type t2
, igs_ca_inst acad
, igs_ca_inst adm
where
sup_cal_type = t1.cal_type
and t1.s_cal_cat = 'ACADEMIC'
and sub_cal_type = t2.cal_type
and t2.s_cal_cat = 'ADMISSION'
and sup_cal_type = acad.cal_type
and sup_ci_sequence_number = acad.sequence_number
and sub_cal_type = adm.cal_type
and sub_ci_sequence_number = adm.sequence_number
and acad.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_acad_cal
and ci.sequence_number = l_acad_cal_seq_num)
and adm.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_adm_cal
and ci.sequence_number = l_adm_cal_seq_num)
)
)
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
Where
aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
(
select
r.sup_cal_type,
r.sup_ci_sequence_number,
r.sub_cal_type,
r.sub_ci_sequence_number
from
igs_ca_inst_rel r
, igs_ca_type t1
, igs_ca_type t2
, igs_ca_inst acad
, igs_ca_inst adm
where
sup_cal_type = t1.cal_type
and t1.s_cal_cat = 'ACADEMIC'
and sub_cal_type = t2.cal_type
and t2.s_cal_cat = 'ADMISSION'
and sup_cal_type = acad.cal_type
and sup_ci_sequence_number = acad.sequence_number
and sub_cal_type = adm.cal_type
and sub_ci_sequence_number = adm.sequence_number
and acad.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_acad_cal
and ci.sequence_number = l_acad_cal_seq_num)
and adm.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_adm_cal
and ci.sequence_number = l_adm_cal_seq_num)
)
)
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
Where aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
UNION
Select aa.person_id
From IGS_SS_ADM_APPL_STG aa
Where
((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND (p_person_id_group IS NOT NULL AND
aa.person_id IN (SELECT person_id
FROM igs_pe_prsid_grp_mem pgm
WHERE pgm.group_id = p_person_id_group
And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
(
select
r.sup_cal_type,
r.sup_ci_sequence_number,
r.sub_cal_type,
r.sub_ci_sequence_number
from
igs_ca_inst_rel r
, igs_ca_type t1
, igs_ca_type t2
, igs_ca_inst acad
, igs_ca_inst adm
where
sup_cal_type = t1.cal_type
and t1.s_cal_cat = 'ACADEMIC'
and sub_cal_type = t2.cal_type
and t2.s_cal_cat = 'ADMISSION'
and sup_cal_type = acad.cal_type
and sup_ci_sequence_number = acad.sequence_number
and sub_cal_type = adm.cal_type
and sub_ci_sequence_number = adm.sequence_number
and acad.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_acad_cal
and ci.sequence_number = l_acad_cal_seq_num)
and adm.start_dt > ( select ci.start_dt
from igs_ca_inst ci
where ci.cal_type = l_adm_cal
and ci.sequence_number = l_adm_cal_seq_num)
)
)
AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
Group BY aa.person_id;
SELECT user_id, user_name, description
FROM FND_USER
WHERE person_party_id = cp_person_id ;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = p_person_id ;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = cp_person_id ;
SELECT IGS_AD_WF_INAPPL_S.NEXTVAL
FROM dual;
l_parameter_list_t.delete;
select a.person_id
from igs_ad_appl_all a,
igs_ad_ps_appl_inst_all i,
igs_ad_aplins_admreq r,
igs_tr_item_all t,
igs_tr_type_all ty
where a.person_id = i.person_id
and (p_person_id_group IS NOT NULL and
a.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
and a.admission_appl_number = i.admission_appl_number
and i.person_id = r.person_id
and i.nominated_course_cd = r.course_cd
and i.admission_appl_number = r.admission_appl_number
and i.sequence_number = r.sequence_number
and r.tracking_id = t.tracking_id
and t.tracking_status in (select tracking_status from igs_tr_status where s_tracking_status = 'ACTIVE')
AND ((a.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND ((a.ACAD_CAL_TYPE= l_acad_cal) AND (a.ACAD_CI_SEQUENCE_NUMBER= l_acad_cal_seq_num))
AND ((a.ADM_CAL_TYPE= l_adm_cal) AND (a.ADM_CI_SEQUENCE_NUMBER= l_adm_cal_seq_num))
AND (((a.ADMISSION_CAT = l_adm_cat) AND (a.S_ADMISSION_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((a.application_id = p_appl_id and p_appl_id IS NOT NULL) OR (p_appl_id is NULL))
AND ((a.application_type = p_appl_type and p_appl_type IS NOT NULL) OR (p_appl_type is NULL))
AND ((i.nominated_course_cd = p_prog_code and p_prog_code IS NOT NULL) OR (p_prog_code is NULL))
AND ((i.location_cd = p_location and p_location IS NOT NULL) OR (p_location is NULL))
AND ((i.attendance_mode = p_att_mode and p_att_mode IS NOT NULL) OR (p_att_mode is NULL))
AND ((i.attendance_type = p_att_type and p_att_type IS NOT NULL) OR (p_att_type is NULL))
and t.tracking_type = ty.tracking_type
and ty.s_tracking_type = 'ADM_PROCESSING'
Group by a.person_id;
select a.person_id
from igs_ad_appl_all a,
igs_ad_ps_appl_inst_all i,
igs_ad_aplins_admreq r,
igs_tr_item_all t,
igs_tr_type_all ty
where a.person_id = i.person_id
and (p_person_id_group IS NOT NULL and
a.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 SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
OR
(p_person_id_group is null))
and a.admission_appl_number = i.admission_appl_number
and i.person_id = r.person_id
and i.nominated_course_cd = r.course_cd
and i.admission_appl_number = r.admission_appl_number
and i.sequence_number = r.sequence_number
and r.tracking_id = t.tracking_id
and t.tracking_status in (select tracking_status from igs_tr_status where s_tracking_status = 'ACTIVE')
AND ((a.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
AND ((a.ACAD_CAL_TYPE = l_acad_cal) AND (a.ACAD_CI_SEQUENCE_NUMBER = l_acad_cal_seq_num))
AND ((a.ADM_CAL_TYPE = l_adm_cal) AND (a.ADM_CI_SEQUENCE_NUMBER = l_adm_cal_seq_num))
AND (((a.ADMISSION_CAT = l_adm_cat) AND (a.S_ADMISSION_PROCESS_TYPE = l_adm_proc_type)
AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
AND ((a.application_id = p_appl_id and p_appl_id IS NOT NULL) OR (p_appl_id is NULL))
AND ((a.application_type = p_appl_type and p_appl_type IS NOT NULL) OR (p_appl_type is NULL))
AND ((i.nominated_course_cd = p_prog_code and p_prog_code IS NOT NULL) OR (p_prog_code is NULL))
AND ((i.location_cd = p_location and p_location IS NOT NULL) OR (p_location is NULL))
AND ((i.attendance_mode = p_att_mode and p_att_mode IS NOT NULL) OR (p_att_mode is NULL))
AND ((i.attendance_type = p_att_type and p_att_type IS NOT NULL) OR (p_att_type is NULL))
and t.tracking_type = ty.tracking_type
and ty.s_tracking_type = 'POST_ADMISSION'
group by a.person_id;
SELECT alternate_code
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type AND
sequence_number = cp_seq_no;
SELECT user_name, description
FROM FND_USER
WHERE person_party_id = cp_person_id ;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = p_person_id ;
SELECT IGS_AD_WF_ADREQ_S.NEXTVAL
FROM dual;
l_parameter_list_t.delete;
SELECT IGS_AD_WF_POSTREQ_S.NEXTVAL
FROM dual;
l_parameter_list_t.delete;