[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| which will update the outcome decision for an application |
| calculated by the user in interface table |
| HISTORY |
| Who When What |
| rrengara 2001/08/14 Creation of this code |
| cdcruz 18-feb-2002 Bug 2217104 Admit to future term Enhancement,updated tbh call for
| new columns being added to IGS_AD_PS_APPL_INST
|
| kamohan 09-SEP-2002 Bug 2536463 Modified the package to accomodate the detailed |
| error codes for the outcome status validation failure |
| nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build |
| kamohan 16-SEP-2002 Bug # 2550009 // Modified the prc_adm_outcome_status procedure
| for the UCAS transaction builder call |
|ayedubat 04-DEC-03 Modified the call to the procedure,ucas_user_hook to add |
| two new IN parameters, p_condition_category and p_condition_name |
| and one OUT Patrameter,p_uc_tran_id for bug, 3009203 |
*=======================================================================*/
PROCEDURE update_int_table (
p_status IN igs_ad_admde_int.status%TYPE,
p_error_msg IN fnd_new_messages.message_text%TYPE, -- Replaced error_code with error_msg Bug 3297241
p_interface_mkdes_id IN igs_ad_admde_int.interface_mkdes_id%TYPE,
p_outcome_status IN igs_ad_admde_int.adm_outcome_status%TYPE
)
IS
------------------------------------------------------------------
--Created by : rrengara, Oracle India (in)
--Date created: 14-AUG-2001
--
--Purpose: to update the interface table
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
BEGIN
UPDATE igs_ad_admde_int
SET status = p_status,
error_text = p_error_msg
WHERE interface_mkdes_id = p_interface_mkdes_id;
END update_int_table;
SELECT decision_make_id, decision_date, decision_reason_id,
pending_reason_id, offer_dt, offer_response_dt
FROM igs_ad_batc_def_det
WHERE
batch_id = p_batch_id AND
acad_cal_type = p_acad_cal_type AND
acad_ci_sequence_number = p_acad_ci_sequence_number AND
adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
s_admission_process_type = p_s_admission_process_type;
SELECT person_id, decision_make_id, decision_date, decision_reason_id,
pending_reason_id, offer_dt, offer_response_dt, admission_appl_number
FROM igs_ad_admde_int
WHERE interface_mkdes_id = p_interface_mkdes_id;
SELECT appl_dt
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
SELECT 'x'
FROM igs_ad_code_classes
WHERE code_id = cp_code_id
AND class = cp_class
AND closed_ind = 'N';
SELECT 'x'
FROM igs_pe_typ_instances typeinst
WHERE typeinst.person_id = cp_decision_maker_id
AND typeinst.system_type IN ('STAFF','FACULTY')
AND (SYSDATE between typeinst.start_date AND NVL(typeinst.end_date,SYSDATE));
SELECT 'X'
FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
WHERE base.person_id = cp_person_id
AND base.person_id = pd.party_id (+)
AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
SELECT a.ROWID, a.*
FROM IGS_AD_APPL a
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number;
SELECT a.ROWID, a.*, b.req_for_reconsideration_ind
FROM igs_ad_ps_appl_inst a, IGS_AD_PS_APPL b
WHERE a.person_id = p_person_id
AND a.admission_appl_number = p_admission_appl_number
AND a.nominated_course_cd = p_nominated_course_cd
AND a.sequence_number = p_sequence_number
AND a.person_id = b.person_id
AND a.admission_appl_number = b.admission_appl_number
AND a.nominated_course_cd = b.nominated_course_cd;
SELECT
'Y'
FROM
IGS_AD_PRCS_CAT_STEP
WHERE
admission_cat = cp_s_admission_process_type
AND s_admission_process_type = cp_s_admission_process_type
AND s_admission_step_type = 'PRE-ENROL'
AND step_group_type <> 'TRACK' ;
SELECT a.*
FROM igs_ad_ps_appl a
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_cd;
SELECT a.adm_offer_resp_status
FROM igs_ad_ps_appl_inst a
WHERE person_id = p_person_id
AND admission_appl_number = p_admission_appl_number
AND nominated_course_cd = p_nominated_course_cd
AND sequence_number = p_sequence_number;
SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP
WHERE admission_cat = cp_admission_cat
AND s_admission_process_type = cp_s_admission_process_type
AND s_admission_step_type = 'RECONSIDER';
update_int_table ('3', l_err_msg, p_interface_mkdes_id, p_adm_outcome_status);
update_int_table ('3', l_error_msg, p_interface_mkdes_id, p_adm_outcome_status);
update_int_table ('3', l_msg_data, p_interface_mkdes_id, p_adm_outcome_status);
UPDATE igs_ad_admde_int_all
SET status = '1'
WHERE interface_mkdes_id = p_interface_mkdes_id;
UPDATE igs_ad_admde_int SET status = '3', error_text = l_sqlerrm WHERE interface_mkdes_id = p_interface_mkdes_id;
SELECT *
FROM igs_ad_batc_def_det_all abdd
WHERE batch_id = p_batch_id AND
( (abdd.ACAD_CAL_TYPE IS NULL AND
abdd.ACAD_CI_SEQUENCE_NUMBER IS NULL AND
abdd.ADM_CAL_TYPE IS NULL AND
abdd.ADM_CI_SEQUENCE_NUMBER IS NULL AND
abdd.ADMISSION_CAT IS NULL AND
abdd.S_ADMISSION_PROCESS_TYPE IS NULL)
OR
(abdd.ACAD_CAL_TYPE IS NOT NULL AND
abdd.ACAD_CI_SEQUENCE_NUMBER IS NOT NULL AND
abdd.ADM_CAL_TYPE IS NOT NULL AND
abdd.ADM_CI_SEQUENCE_NUMBER IS NOT NULL AND
abdd.ADMISSION_CAT IS NOT NULL AND
abdd.S_ADMISSION_PROCESS_TYPE IS NOT NULL) ) ;
SELECT mdi.*
FROM igs_ad_admde_int_all mdi, igs_ad_batc_def_det_all abdd
WHERE mdi.batch_id = p_batch_id
AND mdi.batch_id = abdd.batch_id
AND EXISTS ( SELECT 1
FROM igs_ad_ps_appl_inst aplinst, igs_ad_appl appl
WHERE aplinst.person_id = appl.person_id
AND aplinst.admission_appl_number = appl.admission_appl_number
AND aplinst.person_id = mdi.person_id
AND aplinst.admission_appl_number = mdi.admission_appl_number
AND aplinst.nominated_course_cd = mdi.nominated_course_cd
AND aplinst.sequence_number = mdi.sequence_number
AND ( (abdd.ACAD_CAL_TYPE IS NULL
AND abdd.ACAD_CI_SEQUENCE_NUMBER IS NULL
AND abdd.ADM_CAL_TYPE IS NULL
AND abdd.ADM_CI_SEQUENCE_NUMBER IS NULL
AND abdd.ADMISSION_CAT IS NULL
AND abdd.S_ADMISSION_PROCESS_TYPE IS NULL)
OR appl.ACAD_CAL_TYPE = abdd.ACAD_CAL_TYPE
AND appl.ACAD_CI_SEQUENCE_NUMBER = abdd.ACAD_CI_SEQUENCE_NUMBER
AND NVL(aplinst.ADM_CAL_TYPE, appl.ADM_CAL_TYPE) = abdd.ADM_CAL_TYPE
AND NVL(aplinst.ADM_CI_SEQUENCE_NUMBER, appl.ADM_CI_SEQUENCE_NUMBER ) = abdd.ADM_CI_SEQUENCE_NUMBER
AND appl.ADMISSION_CAT = abdd.ADMISSION_CAT
AND appl.S_ADMISSION_PROCESS_TYPE = abdd.S_ADMISSION_PROCESS_TYPE /*2*/) /*1*/)
AND status = '2';
SELECT adm_outcome_status
FROM igs_ad_ps_appl_inst
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number
AND nominated_course_cd = cp_nominated_course_cd
AND sequence_number = cp_sequence_number;
DELETE FROM igs_ad_admde_int_all
WHERE batch_id = p_batch_id
AND status = '1';
SELECT *
FROM igs_ad_admde_int_all
WHERE batch_id = p_batch_id
AND status = '2';
DELETE FROM igs_ad_admde_int_all
WHERE interface_mkdes_id = c_admde_int_rec.interface_mkdes_id;
p_message_name := 'IGS_AD_DELETE_N_RECORDS';