[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_new_update_who IN VARCHAR2 ,
p_old_update_who IN VARCHAR2 ,
p_new_update_on IN DATE ,
p_old_update_on IN DATE ,
p_new_applicant_acptnce_cndtn IN VARCHAR2 ,
p_old_applicant_acptnce_cndtn IN VARCHAR2 ,
p_new_cndtnl_offer_cndtn IN VARCHAR2 ,
p_old_cndtnl_offer_cndtn IN VARCHAR2 ,
p_new_appl_inst_status IN VARCHAR2 DEFAULT NULL, --arvsrini igsm
p_old_appl_inst_status IN VARCHAR2 DEFAULT NULL,
P_NEW_DECISION_DATE DATE DEFAULT NULL, -- begin APADEGAL adtd001 igs.m
P_OLD_DECISION_DATE DATE DEFAULT NULL,
P_NEW_DECISION_MAKE_ID NUMBER DEFAULT NULL,
P_OLD_DECISION_MAKE_ID NUMBER DEFAULT NULL,
P_NEW_DECISION_REASON_ID NUMBER DEFAULT NULL,
P_OLD_DECISION_REASON_ID NUMBER DEFAULT NULL,
P_NEW_PENDING_REASON_ID NUMBER DEFAULT NULL,
P_OLD_PENDING_REASON_ID NUMBER DEFAULT NULL,
P_NEW_WAITLIST_STATUS VARCHAR2 DEFAULT NULL,
P_OLD_WAITLIST_STATUS VARCHAR2 DEFAULT NULL,
P_NEW_WAITLIST_RANK VARCHAR2 DEFAULT NULL,
P_OLD_WAITLIST_RANK VARCHAR2 DEFAULT NULL,
P_NEW_FUTURE_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_OLD_FUTURE_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_NEW_FUTURE_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_OLD_FUTURE_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_NEW_FUTURE_ADM_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_OLD_FUTURE_ADM_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_NEW_FUTURE_ADM_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_OLD_FUTURE_ADM_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_NEW_DEF_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_OLD_DEF_ACAD_CAL_TYPE VARCHAR2 DEFAULT NULL,
P_NEW_DEF_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_OLD_DEF_ACAD_CI_SEQ_NUM NUMBER DEFAULT NULL,
P_NEW_DECLINE_OFR_REASON VARCHAR2 DEFAULT NULL,
P_OLD_DECLINE_OFR_REASON VARCHAR2 DEFAULT NULL -- end APADEGAL adtd001 igs.m
)
IS
gv_other_detail VARCHAR2(255);
lv_old_update_on DATE := p_old_update_on;
SELECT 'x'
FROM IGS_AD_PS_APLINSTHST
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
AND hist_start_dt = p_old_update_on;
SELECT req_for_reconsideration_ind
FROM igs_ad_ps_appl
WHERE person_id = cp_person_id and
admission_appl_number = cp_admission_appl_number and
nominated_course_cd = cp_nominated_course_cd;
SELECT MAX(hist_start_dt) max_hist_start_dt, MAX(hist_end_dt) max_hist_end_dt
FROM IGS_AD_PS_APLINSTHST
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;
v_acaih_rec.hist_start_dt := p_old_update_on;
v_acaih_rec.hist_end_dt := p_new_update_on;
v_acaih_rec.hist_who := p_old_update_who;
IF (l_latest_hist_dt.max_hist_start_dt = p_old_update_on) THEN
-- add one second from the hist_start_dt value
-- to avoid a primary key constraint from occurring
-- when saving the record. Modified as part of Bug:2315674
v_acaih_rec.hist_start_dt := v_acaih_rec.hist_start_dt +1 / (60*24*60);
ELSIF (l_latest_hist_dt.max_hist_start_dt > p_old_update_on) THEN
v_acaih_rec.hist_start_dt := l_latest_hist_dt.max_hist_start_dt +1 / (60*24*60);
IF (l_latest_hist_dt.max_hist_end_dt >= p_new_update_on) THEN
v_acaih_rec.hist_end_dt := l_latest_hist_dt.max_hist_end_dt + 1 / (60*24*60);
IGS_AD_PS_APLINSTHST_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => v_acaih_rec.person_id,
X_Admission_Appl_Number => v_acaih_rec.admission_appl_number,
X_Nominated_Course_Cd => v_acaih_rec.nominated_course_cd,
X_Sequence_Number => v_acaih_rec.sequence_number,
X_Hist_Start_Dt => v_acaih_rec.hist_start_dt,
X_Hist_End_Dt => v_acaih_rec.hist_end_dt,
X_Hist_Who => v_acaih_rec.hist_who,
X_Hist_Offer_Round_Number => Null,
X_Adm_Cal_Type => v_acaih_rec.adm_cal_type,
X_Adm_Ci_Sequence_Number => v_acaih_rec.adm_ci_sequence_number,
X_Course_Cd => v_acaih_rec.course_cd,
X_Crv_Version_Number => v_acaih_rec.crv_version_number,
X_Location_Cd => v_acaih_rec.location_cd,
X_Attendance_Mode => v_acaih_rec.attendance_mode,
X_Attendance_Type => v_acaih_rec.attendance_type,
X_Unit_Set_Cd => v_acaih_rec.unit_set_cd,
X_Us_Version_Number => v_acaih_rec.us_version_number,
X_Preference_Number => v_acaih_rec.preference_number,
X_Adm_Doc_Status => v_acaih_rec.adm_doc_status,
X_Adm_Entry_Qual_Status => v_acaih_rec.adm_entry_qual_status,
X_Late_Adm_Fee_Status => v_acaih_rec.late_adm_fee_status,
X_Adm_Outcome_Status => v_acaih_rec.adm_outcome_status,
X_ADM_OTCM_STATUS_AUTH_PER_ID => v_acaih_rec.adm_otcm_status_auth_person_id,
X_Adm_Outcome_Status_Auth_Dt => v_acaih_rec.adm_outcome_status_auth_dt,
X_Adm_Outcome_Status_Reason => v_acaih_rec.adm_outcome_status_reason,
X_Offer_Dt => v_acaih_rec.offer_dt,
X_Offer_Response_Dt => v_acaih_rec.offer_response_dt,
X_Prpsd_Commencement_Dt => v_acaih_rec.prpsd_commencement_dt,
X_Adm_Cndtnl_Offer_Status => v_acaih_rec.adm_cndtnl_offer_status,
X_Cndtnl_Offer_Satisfied_Dt => v_acaih_rec.cndtnl_offer_satisfied_dt,
X_CNDTNL_OFR_MUST_BE_STSFD_IND => v_acaih_rec.cndtnl_offer_must_be_stsfd_ind,
X_Adm_Offer_Resp_Status => v_acaih_rec.adm_offer_resp_status,
X_Actual_Response_Dt => v_acaih_rec.actual_response_dt,
X_Adm_Offer_Dfrmnt_Status => v_acaih_rec.adm_offer_dfrmnt_status,
X_Deferred_Adm_Cal_Type => v_acaih_rec.deferred_adm_cal_type,
X_Deferred_Adm_Ci_Sequence_Num => v_acaih_rec.deferred_adm_ci_sequence_num,
X_Deferred_Tracking_Id => v_acaih_rec.deferred_tracking_id,
X_Ass_Rank => v_acaih_rec.ass_rank,
X_Secondary_Ass_Rank => v_acaih_rec.secondary_ass_rank,
X_INTRNTNL_ACCEPT_ADVICE_NUM => v_acaih_rec.intrntnl_acceptance_advice_num,
X_Ass_Tracking_Id => v_acaih_rec.ass_tracking_id,
X_Fee_Cat => v_acaih_rec.fee_cat,
X_Hecs_Payment_Option => v_acaih_rec.hecs_payment_option,
X_Expected_Completion_Yr => v_acaih_rec.expected_completion_yr,
X_Expected_Completion_Perd => v_acaih_rec.expected_completion_perd,
X_Correspondence_Cat => v_acaih_rec.correspondence_cat,
X_Enrolment_Cat => v_acaih_rec.enrolment_cat,
X_Funding_Source => v_acaih_rec.funding_source,
X_Applicant_Acptnce_Cndtn => v_acaih_rec.applicant_acptnce_cndtn,
X_Cndtnl_Offer_Cndtn => v_acaih_rec.cndtnl_offer_cndtn,
X_Org_Id => l_org_id,
X_Appl_inst_status => v_acaih_rec.appl_inst_status, --arvsrini igsm
X_DECISION_DATE => v_acaih_rec.DECISION_DATE, -- begin APADEGAL adtd001 igs.m
X_DECISION_MAKE_ID => v_acaih_rec.DECISION_MAKE_ID,
X_DECISION_REASON_ID => v_acaih_rec.DECISION_REASON_ID,
X_PENDING_REASON_ID => v_acaih_rec.PENDING_REASON_ID,
X_WAITLIST_STATUS => v_acaih_rec.WAITLIST_STATUS,
X_WAITLIST_RANK => v_acaih_rec.WAITLIST_RANK,
X_FUTURE_ACAD_CAL_TYPE => v_acaih_rec.FUTURE_ACAD_CAL_TYPE,
X_FUTURE_ACAD_CI_SEQUENCE_NUM => v_acaih_rec.FUTURE_ACAD_CI_SEQUENCE_NUM,
X_FUTURE_ADM_CAL_TYPE => v_acaih_rec.FUTURE_ADM_CAL_TYPE,
X_FUTURE_ADM_CI_SEQUENCE_NUM => v_acaih_rec.FUTURE_ADM_CI_SEQUENCE_NUM,
X_DEF_ACAD_CAL_TYPE => v_acaih_rec.DEF_ACAD_CAL_TYPE,
X_DEF_ACAD_CI_SEQUENCE_NUM => v_acaih_rec.DEF_ACAD_CI_SEQUENCE_NUM,
X_RECONSIDER_FLAG => v_acaih_rec.RECONSIDER_FLAG,
X_DECLINE_OFR_REASON => v_acaih_rec.DECLINE_OFR_REASON -- end APADEGAL adtd001 igs.m
);
p_new_update_who IN VARCHAR2 ,
p_old_update_who IN VARCHAR2 ,
p_new_update_on IN DATE ,
p_old_update_on IN DATE )
IS
gv_other_detail VARCHAR2(255);
v_acah_rec.hist_start_dt := p_old_update_on;
v_acah_rec.hist_end_dt := p_new_update_on;
v_acah_rec.hist_who := p_old_update_who;
IGS_AD_PS_APPL_Hist_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Hist_Who => v_acah_rec.hist_who,
X_Transfer_Course_Cd => v_acah_rec.transfer_course_cd,
X_Basis_For_Admission_Type => v_acah_rec.basis_for_admission_type,
X_Admission_Cd => v_acah_rec.admission_cd,
X_Course_Rank_Set => v_acah_rec.course_rank_set,
X_Course_Rank_Schedule => v_acah_rec.course_rank_schedule,
X_Req_For_Reconsideration_Ind => v_acah_rec.req_for_reconsideration_ind,
X_Req_For_Adv_Standing_Ind => v_acah_rec.req_for_adv_standing_ind,
X_Person_Id => v_acah_rec.person_id,
X_Admission_Appl_Number => v_acah_rec.admission_appl_number,
X_Nominated_Course_Cd => v_acah_rec.nominated_course_cd,
X_Hist_Start_Dt => v_acah_rec.hist_start_dt,
X_Hist_End_Dt => v_acah_rec.hist_end_dt,
X_Org_Id => l_org_id
);
SELECT igs_co_itm_rf_num_s.NEXTVAL
FROM dual;
SELECT igs_co_ou_co_ref_seq_num_s.NEXTVAL
FROM dual;
SELECT apcl.letter_reference_number
FROM igs_ad_prcs_cat_ltr apcl,
igs_co_s_ltr slet
WHERE apcl.admission_cat = cp_admission_cat
AND apcl.s_admission_process_type = cp_s_admission_process_type
AND apcl.correspondence_type = cp_correspondence_type
AND apcl.correspondence_type = slet.correspondence_type
AND apcl.letter_reference_number = slet.letter_reference_number
AND slet.closed_ind = 'N';
SELECT sl.letter_reference_number
FROM igs_co_s_ltr sl
WHERE sl.correspondence_type = cp_correspondence_type
AND sl.closed_ind = 'N'
ORDER BY sl.letter_reference_number;
SELECT cit.reference_number
FROM igs_co_itm cit
WHERE cit.correspondence_type = cp_correspondence_type
AND cit.create_dt = cp_create_dt;
SELECT 'x'
FROM igs_ad_appl_ltr aal
WHERE aal.person_id = cp_person_id
AND aal.admission_appl_number = cp_admission_appl_number
AND aal.correspondence_type = cp_correspondence_type
AND aal.sequence_number = cp_aal_sequence_number
FOR UPDATE OF aal.letter_reference_number, aal.spl_sequence_number NOWAIT;
igs_co_itm_pkg.insert_row (
x_rowid => lv_rowid,
x_org_id => fnd_profile.value ('ORG_ID'),
x_correspondence_type => p_correspondence_type,
x_reference_number => v_igs_co_itm_rf_num_s,
x_create_dt => v_create_dt,
x_originator_person_id => NULL,
x_request_num => NULL,
x_s_job_name => NULL,
x_request_job_id => NULL,
x_output_num => NULL,
x_request_job_run_id => NULL,
x_cal_type => NULL,
x_ci_sequence_number => NULL,
x_course_cd => NULL,
x_cv_version_number => NULL,
x_unit_cd => NULL,
x_uv_version_number => NULL,
x_comments => UPPER (v_comments),
x_job_request_id => NULL,
x_mode => 'R'
);
igs_co_ou_co_pkg.insert_row (
x_rowid => lv_rowid,
x_person_id => p_person_id,
x_correspondence_type => p_correspondence_type,
x_reference_number => gv_reference_number,
x_issue_dt => v_issue_dt,
x_dt_sent => NULL,
x_unknown_return_dt => NULL,
x_addr_type => NULL,
x_tracking_id => NULL,
x_comments => NULL,
x_letter_reference_number => v_letter_reference_number,
x_org_id => l_org_id,
x_spl_sequence_number => v_spl_sequence_number,
x_mode => 'R'
);
igs_co_ou_co_ref_pkg.insert_row (
x_rowid => lv_rowid,
x_person_id => p_person_id,
x_org_id => l_org_id,
x_correspondence_type => p_correspondence_type,
x_reference_number => gv_reference_number,
x_issue_dt => v_issue_dt,
x_sequence_number => v_igs_co_ou_co_ref_seq_num_s,
x_cal_type => NULL,
x_ci_sequence_number => NULL,
x_course_cd => NULL,
x_cv_version_number => NULL,
x_unit_cd => NULL,
x_uv_version_number => NULL,
x_s_other_reference_type => 'SPL_SEQNUM',
x_other_reference => v_spl_sequence_number,
x_mode => 'R'
);
UPDATE igs_ad_appl_ltr
SET letter_reference_number = v_letter_reference_number,
spl_sequence_number = v_spl_sequence_number
WHERE CURRENT OF c_aal;
SELECT aa.admission_cat,
aa.s_admission_process_type,
aal.sequence_number,
aal.spl_sequence_number
FROM igs_ad_appl aa,
igs_ad_appl_ltr aal
WHERE aa.person_id = cp_person_id
AND aa.admission_appl_number = cp_admission_appl_number
AND aal.person_id = aa.person_id
AND aal.admission_appl_number = aa.admission_appl_number
AND aal.correspondence_type = cp_correspondence_type
AND aal.composed_ind = 'Y';
SELECT DISTINCT
aa.person_id person_id,
aa.admission_appl_number admission_appl_number,
aal.correspondence_type correspondence_type,
aal.sequence_number sequence_number,
aa.admission_cat admission_cat,
aa.s_admission_process_type s_admission_process_type,
padv.city city,
padv.county county,
padv.country country,
padv.postal_code postal_code
FROM igs_ad_appl aa,
igs_ad_appl_ltr aal,
igs_ad_ps_appl_inst acaiv, --Bug 3150054 Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst
igs_ps_ver pv,
igs_pe_addr_v padv,
igs_pe_res_dtls_v prdv
WHERE aa.acad_cal_type LIKE cp_acad_cal_type
AND aa.acad_ci_sequence_number LIKE cp_acad_ci_sequence_number
AND NVL(acaiv.adm_cal_type, aa.adm_cal_type) LIKE cp_adm_cal_type
AND NVL(acaiv.adm_ci_sequence_number, aa.adm_ci_sequence_number) LIKE cp_adm_ci_sequence_number
AND aa.admission_cat LIKE cp_admission_cat
AND aa.s_admission_process_type LIKE cp_s_admission_process_type
AND aa.person_id = aal.person_id
AND aa.admission_appl_number = aal.admission_appl_number
AND aal.correspondence_type = cp_correspondence_type
AND aal.composed_ind = 'Y'
AND acaiv.person_id = aa.person_id
AND acaiv.admission_appl_number = aa.admission_appl_number
AND pv.course_cd = acaiv.nominated_course_cd
AND pv.version_number = acaiv.crv_version_number
AND pv.responsible_org_unit_cd LIKE NVL (cp_org_unit_code, '%')
AND acaiv.adm_outcome_status LIKE cp_adm_outcome_status
AND acaiv.nominated_course_cd LIKE cp_pgm_of_study
AND acaiv.adm_offer_resp_status LIKE cp_response_status
AND aa.person_id = prdv.person_id(+)
AND aa.person_id = padv.person_id (+)
AND prdv.residency_class (+) LIKE cp_residency_class
AND prdv.residency_status (+) LIKE cp_residency_stat
AND NVL (acaiv.offer_dt, SYSDATE) <= SYSDATE;
SELECT DISTINCT
aa.admission_appl_number,
aa.admission_cat,
aa.s_admission_process_type,
aal.sequence_number,
aal.spl_sequence_number
FROM igs_ad_appl aa,
igs_ad_appl_ltr aal,
igs_pe_addr_v padv
WHERE aa.person_id = aal.person_id
AND aa.person_id = padv.person_id (+)
AND aal.person_id = cp_person_id
AND aal.correspondence_type = cp_correspondence_type;
SELECT person_id
FROM igs_pe_prsid_grp_mem_v
WHERE group_id = cp_person_id_group AND
NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
SELECT DISTINCT
aa.admission_appl_number admission_appl_number,
aa.admission_cat admission_cat,
aa.s_admission_process_type s_admission_process_type,
aal.sequence_number sequence_number,
aal.spl_sequence_number spl_sequence_number, padv.' || p_sortby ||
' FROM igs_ad_appl aa,
igs_ad_appl_ltr aal,
igs_pe_addr_v padv
WHERE aa.person_id = aal.person_id
AND aa.person_id = padv.person_id (+)
AND aal.person_id = :1
AND aal.correspondence_type = :2
ORDER BY padv.' || p_sortby
USING lcur_person_ids.person_id, p_correspondence_type ;
SELECT DISTINCT
aa.person_id person_id,
aa.admission_appl_number admission_appl_number,
aal.correspondence_type correspondence_type,
aal.sequence_number sequence_number,
aa.admission_cat admission_cat,
aa.s_admission_process_type s_admission_process_type,
padv.city city,
padv.county county,
padv.country country,
padv.postal_code postal_code
FROM igs_ad_appl aa,
igs_ad_appl_ltr aal,
igs_ad_ps_appl_inst acaiv, --Bug 3150054 Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst
igs_ps_ver pv,
igs_pe_addr_v padv,
igs_pe_res_dtls_v prdv
WHERE aa.acad_cal_type LIKE :1
AND aa.acad_ci_sequence_number LIKE :2
AND NVL(acaiv.adm_cal_type, aa.adm_cal_type) LIKE :3
AND NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) LIKE :4
AND aa.admission_cat LIKE :5
AND aa.s_admission_process_type LIKE :6
AND aa.person_id = aal.person_id
AND aa.admission_appl_number = aal.admission_appl_number
AND aal.correspondence_type = :7
AND aal.composed_ind = ''Y''
AND acaiv.person_id = aa.person_id
AND acaiv.admission_appl_number = aa.admission_appl_number
AND pv.course_cd = acaiv.nominated_course_cd
AND pv.version_number = acaiv.crv_version_number
AND pv.responsible_org_unit_cd LIKE :8
AND acaiv.adm_outcome_status LIKE :9
AND acaiv.nominated_course_cd LIKE :10
AND acaiv.adm_offer_resp_status LIKE :11
AND aa.person_id = prdv.person_id(+)
AND aa.person_id = padv.person_id (+)
AND prdv.residency_class (+) LIKE :12
AND prdv.residency_status (+) LIKE :13
AND NVL (acaiv.offer_dt, SYSDATE) <= SYSDATE
ORDER BY padv.' || p_sortby
USING NVL (p_acad_cal_type, '%'), NVL (IGS_GE_NUMBER.TO_CANN (p_acad_ci_sequence_number), '%'), NVL (p_adm_cal_type, '%'),
NVL (IGS_GE_NUMBER.TO_CANN (p_adm_ci_sequence_number), '%'), p_admission_cat, p_s_admission_process_type, p_correspondence_type,
NVL (p_org_unit, '%'), p_adm_outcome_status, p_pgmofstudy, p_response_stat, p_resd_stat, p_resd_class
;
SELECT aalp.phrase_cd,
aalp.phrase_text
FROM IGS_AD_APPL_LTR_PHR aalp
WHERE aalp.person_id = cp_person_id AND
aalp.admission_appl_number = cp_adm_appl_number AND
aalp.correspondence_type = cp_correspondence_type AND
aalp.aal_sequence_number = cp_aal_sequence_number AND
aalp.letter_parameter_type = cp_letter_parameter_type
ORDER BY aalp.phrase_order_number,
aalp.sequence_number;
SELECT ltp.phrase_text
FROM IGS_CO_LTR_PHR ltp
WHERE ltp.phrase_cd = cp_phrase_cd;
SELECT IGS_CO_S_PER_LT_PARM_SEQ_NUM_S.NEXTVAL
FROM DUAL;
IGS_CO_S_PER_LT_Parm_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Correspondence_Type => p_correspondence_type,
X_Letter_Reference_Number => p_letter_reference_number,
X_Spl_Sequence_Number => p_spl_sequence_number,
X_Letter_Parameter_Type => p_letter_parameter_type,
X_Sequence_Number => v_sequence_number,
X_Parameter_Value => v_value,
X_Letter_Repeating_Group_Cd => p_letter_repeating_group_cd,
X_Splrg_Sequence_Number => p_splrg_sequence_number,
x_letter_order_number => p_letter_order_number,
X_ORG_ID => FND_PROFILE.value('ORG_ID')
);