[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Retrive <> record.
These details affect the Financial Aid Processing of the Student.
Know limitations, enhancements or remarks
Change History
Bug No: 2154941
Desc : Disb Build Jul 2002 (FACCR001 DLD)
Who When What --
cdcruz 17-NOV-2004 Removed procedure GET_PE_RESIDENCY_STAT
as its no longer used
npalanis 23-OCT-2002 BUG : 2608360
Reference to igs_pe_code_classes changed to igs_lookups
mesriniv 22-JAN_2002 Added the NVL clauses for check against the
Application Number,Course Code and Version Number
in the cursor for geting addmission appl details
(reverse chronological order - newest change first)
******************************************************************/
PROCEDURE get_pe_mil_service_type(p_person_id IN hz_parties.party_id%TYPE,
p_mil_service_type OUT NOCOPY igs_lookups_view.lookup_code%TYPE,
p_mil_service_desc OUT NOCOPY igs_lookups_view.meaning%TYPE,
p_multiple OUT NOCOPY VARCHAR2)
IS
/*************************************************************
Created By : adhawan
Date Created On : 2001/06/07
Purpose :To get the Military Assitance type that would be considered by the financial Aid
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_get_mil_service IS
SELECT cc.lookup_code service_type,
cc.meaning service_desc
FROM igs_pe_mil_services ms,
igs_lookup_values cc
WHERE cc.lookup_code = ms.military_type_cd
AND cc.lookup_type = 'PE_MIL_SEV_TYPE'
AND ms.person_id = p_person_id
ORDER BY ms.milit_service_id;
SELECT holdtyp.encumbrance_type encumbrance_type,
b.description dsp_description
FROM igs_pe_pers_encumb holdtyp,igs_fi_encmb_type b, igs_pe_persenc_effct holdefft
WHERE holdtyp.person_id = p_person_id
AND b.encumbrance_type = holdtyp.encumbrance_type
AND holdtyp.start_dt <= TRUNC(SYSDATE)
AND ( holdtyp.expiry_dt >= TRUNC(SYSDATE)
OR holdtyp.expiry_dt IS NULL)
AND holdtyp.person_id = holdefft.person_id
AND holdtyp.encumbrance_type = holdefft.encumbrance_type
AND holdefft.s_encmb_effect_type IN
('GRAD_BLK', 'RESULT_BLK','RSTR_AT_TY', 'RSTR_GE_CP','RSTR_LE_CP','RVK_SRVC', 'SUS_COURSE', 'SUS_SRVC', 'DENY_EACT');
SELECT a.visa_type,
b.meaning visa_description
FROM igs_pe_visa a , fnd_lookup_values b
WHERE a.person_id = p_person_id
AND b.view_application_id = 3
AND b.lookup_type = 'PER_US_VISA_TYPES'
AND a.visa_type = b.lookup_code;
SELECT * FROM igs_pe_eit_restatus_v
WHERE person_id = p_person_id
AND (
(TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND TRUNC(end_date))
OR (start_date IS NULL) OR (end_date IS NULL)
);
SELECT ci.cal_type cal_type,
ci.sequence_number sequence_number,
ci.alternate_code alternate_code
FROM igs_ca_inst ci
WHERE ci.cal_type = l_acad_cal_type
AND ci.sequence_number = l_acad_sequence;
SELECT ci.cal_type cal_type,
ci.sequence_number sequence_number,
ci.alternate_code alternate_code
FROM igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat st
WHERE ct.s_cal_cat = 'AWARD'
AND ct.cal_type = ci.cal_type
AND ci.cal_status = st.cal_status
AND st.s_cal_status = 'ACTIVE'
AND TRUNC(ci.start_dt) < TRUNC(SYSDATE)
AND (
ci.end_dt IS NULL
OR TRUNC(ci.end_dt) > TRUNC(SYSDATE)
)
ORDER BY ci.start_dt,ci.sequence_number DESC;
Purpose : To get the Selected Admission Application Record for a Person.
Note - there can be more than 1 admission application record in OSS for
an Academic Calender Instance.
Know limitations, enhancements or remarks
Change History:
--Bug No:2296776
Who When What
sjalasut 16 Nov 03 FA126. Corrected the cursor c_get_adm_appl as the req is to
return "Y" for multiple application instances and not multiple
applications. changed the underlying table in the cursor
from igs_ap_appl
skoppula 10-APR-2002 Corrected the where clause of the the Cursor get_Adm_appl_details
so that adm_appl_status and other statuses in adm_appl table
are correctly mapped to the corresponding user defined statuses
--Bug No:2154941 Desc : Disbursement Build Jul 2002
Who When What
mesriniv 22-JAN-2002 Added the NVL clauses for check against the Application Number,Course Code and Version Number
as per the FACCR001 DLD
(reverse chronological order - newest change first)
***************************************************************/
lv_cal_type igs_ca_inst.cal_type%TYPE DEFAULT NULL;
SELECT aav.row_id appl_inst_row_id,
acai.row_id appl_prog_inst_row_id,
aav.person_id,
aav.admission_appl_number,
acai.course_cd,
acai.crv_version_number,
DECODE(rpstat.s_adm_offer_resp_status,'ACCEPTED',1, 'PENDING',2,'DEFERRAL',3,
DECODE(oustat.s_adm_outcome_status,'OFFER', 5,'COND-OFFER',6,'PENDING',7,
'NO-QUOTA',8,'REJECTED', 8,'VOIDED', 8, 'WITHDRAWN',8,
DECODE(apstat.s_adm_appl_status,'COMPLETED',9,'RECEIVED',10,'WITHDRAWN',11,23))) appl_selection_order,
acai.adm_offer_resp_status,
acai.adm_outcome_status,
aav.adm_appl_status
FROM IGS_AD_APPL aav,
IGS_AD_PS_APPL_INST acai,
IGS_PS_VER crv,
IGS_AD_APPL_STAT apstat,
IGS_AD_OU_STAT oustat,
IGS_AD_OFR_RESP_STAT rpstat
WHERE aav.person_id = acai.person_id (+)
AND aav.admission_appl_number = acai.admission_appl_number(+)
AND aav.person_id = p_person_id
/*AND aav.acad_cal_type = c_cal_type
AND aav.acad_ci_sequence_number = c_sequence_number*/ -- Commented as per bug 2296776
AND aav.admission_appl_number =NVL(p_adm_appl_number,aav.admission_appl_number)
AND acai.course_cd =NVL(p_course_cd,acai.course_cd)
AND acai.crv_version_number =NVL(p_crv_version_number,acai.crv_version_number)
AND acai.course_cd = crv.course_cd(+)
AND acai.crv_version_number = crv.version_number(+)
AND (crv.federal_financial_aid = 'Y'
OR
crv.state_financial_aid = 'Y'
OR
crv.institutional_financial_aid = 'Y' )
AND acai.adm_offer_resp_status = rpstat.adm_offer_resp_status(+)
AND acai.adm_outcome_status = oustat.adm_outcome_status(+)
AND aav.adm_appl_status = apstat.adm_appl_status
ORDER BY 7, 4;
CURSOR c_get_adm_appl IS SELECT COUNT(*) cnt FROM igs_ad_ps_appl_inst WHERE
person_id = p_person_id;
Purpose :This is provided to get the rowid for igs_ad_appl table in the Selected Applicaition Record
which inturn is based on the Academic calendar corresponding to the Award Calendar being passed(last two parameters)
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
-- The OUT NOCOPY parameter variable to capture the values returned from the get_adm_appl_details procedure.
lv_ad_appl_row_id ROWID;
Purpose :This is provided to get the rowid for igs_ad_ps_appl_inst table in the Selected Applicaition Record
which inturn is based on the Academic calendar corresponding to the Award Calendar being passed(last two parameters)
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
-- The OUT NOCOPY parameter variable to capture the values returned from the get_adm_appl_details procedure.
lv_ad_appl_row_id ROWID;