[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION admp_val_aa_delete(
p_adm_appl_status IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN -- admp_val_aa_delete
-- Validate delete of an IGS_AD_APPL record.
DECLARE
v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_delete');
END admp_val_aa_delete;
FUNCTION admp_val_aa_insert(
p_person_id IN NUMBER ,
p_adm_cal_type IN VARCHAR2 ,
p_adm_ci_sequence_number IN NUMBER ,
p_s_admission_process_type IN VARCHAR2 ,
p_encmb_chk_ind IN VARCHAR2 DEFAULT 'N',
p_appl_dt IN DATE ,
p_title_required_ind IN VARCHAR2 DEFAULT 'N',
p_birth_dt_required_ind IN VARCHAR2 DEFAULT 'N',
p_message_name OUT NOCOPY VARCHAR2 ,
p_return_type OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN
-- admp_val_aa_insert
-- Validate insert of an IGS_AD_APPL record.
-- * If the admission process type is short admission, the short admission
-- date alias for the admission period must be set.
-- * If the admission process type is short admission, the short admission
-- date must be greater than the current date.
-- * If encumbrance checking is applicable, warn if the person has an
-- encumbrance of revoke service or suspend service effective as at the
-- application date.
-- * If encumbrance checking is applicable, warn if the person has an
-- encumbrance of revoke service or suspend service effective as at the
-- encumbrance checking date.
-- * If the person?s title is required, warn if the persons?s title is not set.
-- * If the person?s birth date is required, warn if the persons?s birth date
-- is not set.
DECLARE
cst_error CONSTANT VARCHAR2(1) := 'E';
SELECT pb.title,
pb.birth_date,
ph.deceased_ind
FROM IGS_PE_PERSON_BASE_V pb, IGS_PE_HZ_PARTIES ph
WHERE pb.person_id = ph.party_id
AND pb.person_id = p_person_id;
FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_insert');
END admp_val_aa_insert;
FUNCTION admp_val_aa_update(
p_adm_appl_status IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN -- admp_val_aa_update
-- Validate update of an IGS_AD_APPL record.
DECLARE
v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_update');
END admp_val_aa_update;
SELECT s_cal_cat
FROM IGS_CA_TYPE
WHERE cal_type = cp_acad_cal_type;
SELECT cs.s_cal_status
FROM IGS_CA_STAT cs,
IGS_CA_INST ci
WHERE ci.cal_type = cp_acad_cal_type AND
ci.sequence_number = cp_acad_ci_sequence_number AND
ci.cal_status = cs.cal_status;
SELECT cat.s_cal_cat
FROM IGS_CA_TYPE cat
WHERE cat.cal_type = cp_cal_type;
SELECT cs.s_cal_status
FROM IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE ci.cal_status = cs.cal_status AND
ci.cal_type = cp_cal_type AND
ci.sequence_number = cp_sequence_number;
SELECT 'x'
FROM IGS_CA_INST_REL cir
WHERE cir.sup_cal_type =cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_ci_sequence_number AND
cir.sub_cal_type = cp_adm_cal_type AND
cir.sub_ci_sequence_number = cp_adm_ci_sequence_number;
SELECT 'x'
FROM IGS_AD_PRD_AD_PRC_CA apapc
WHERE apapc.adm_cal_type = cp_adm_cal_type AND
apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
apapc.admission_cat = cp_admission_cat AND
apapc.s_admission_process_type = cp_s_admission_process_type AND
apapc.closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
SELECT ac.closed_ind
FROM IGS_AD_CAT ac
WHERE ac.admission_cat = p_admission_cat;
SELECT ccm.admission_cat,
COUNT(*)
FROM IGS_CO_CAT_MAP ccm
WHERE ccm.admission_cat = p_admission_cat AND
ccm.dflt_cat_ind = 'Y'
GROUP BY ccm.admission_cat;
SELECT ecm.admission_cat,
COUNT(*)
FROM IGS_EN_CAT_MAPPING ecm
WHERE ecm.admission_cat = p_admission_cat AND
ecm.dflt_cat_ind = 'Y'
GROUP BY ecm.admission_cat;
SELECT fcm.admission_cat,
COUNT(*)
FROM IGS_FI_FEE_CAT_MAP fcm
WHERE fcm.admission_cat = p_admission_cat AND
fcm.dflt_cat_ind = 'Y'
GROUP BY fcm.admission_cat;
SELECT aos.s_adm_outcome_status,
aors.s_adm_offer_resp_status
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
aos.adm_outcome_status = acai.adm_outcome_status AND
aors.adm_offer_resp_status = acai.adm_offer_resp_status;
SELECT closed_ind
FROM IGS_AD_APPL_STAT
WHERE adm_appl_status = p_adm_appl_status;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
aos.s_adm_outcome_status IN (
cst_offer,
cst_cond_offer,
cst_withdrawn,
cst_voided) AND
acai.adm_outcome_status = aos.adm_outcome_status;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
aos.s_adm_outcome_status = cst_offer AND
acai.adm_outcome_status = aos.adm_outcome_status;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
aos.s_adm_outcome_status = cst_cond_offer AND
acai.adm_outcome_status = aos.adm_outcome_status;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos,
IGS_AD_CNDNL_OFRSTAT acos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
aos.s_adm_outcome_status = cst_cond_offer AND
acos.s_adm_cndtnl_offer_status = cst_satisfied AND
acai.adm_cndtnl_offer_status = acos.adm_cndtnl_offer_status AND
acai.adm_outcome_status = aos.adm_outcome_status;
SELECT api.pe_person_id
FROM IGS_PE_ALT_PERS_ID api,
IGS_PE_PERSON_ID_TYP pit
WHERE api.pe_person_id = p_person_id AND
(api.start_dt IS NULL OR
(api.start_dt <= p_appl_dt AND
NVL(api.end_dt, IGS_GE_DATE.IGSCHAR('9999/01/01')) >= p_appl_dt) AND
(api.end_dt IS NULL OR api.start_dt <> api.end_dt)) AND
pit.person_id_type = api.person_id_type AND
pit.s_person_id_type = 'TAC';