[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE check_offer_resp_update(
p_person_id IN NUMBER,
p_admission_application_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
AS
/*************************************************************
Created By :samaresh
Date : 20-DEC-2001
Created By : Sandhya.Amaresh
Purpose : This api check if the offer response can be made
'Accepted' for the offer
Know limitations, enhancements or remarks
Change History
Who When What
***************************************************************/
CURSOR c_appl(cp_person_id NUMBER,cp_admission_appl_number NUMBER) IS
SELECT *
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
SELECT *
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_cd
AND sequence_number = cp_seq_number;
END check_offer_resp_update;
PROCEDURE check_offer_update(
p_person_id IN NUMBER,
p_admission_application_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
AS
/*************************************************************
Created By :samaresh
Date : 20-DEC-2001
Created By : Sandhya.Amaresh
Purpose : This api check if an offer can be made for the
application
Know limitations, enhancements or remarks
Change History
Who When What
***************************************************************/
CURSOR c_appl(cp_person_id NUMBER,cp_admission_appl_number NUMBER) IS
SELECT *
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
SELECT *
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_cd
AND sequence_number = cp_seq_number;
END check_offer_update;
PROCEDURE check_update_aeps_acs(
p_person_id IN NUMBER,
p_admission_application_number IN NUMBER,
p_nominated_course_cd IN VARCHAR2,
p_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
AS
/*************************************************************
Created By :rboddu
Date Created By : 31-DEC-2001
Purpose : 2158524
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
--Cursor which retrieves the system admission process type associated with the application.
CURSOR c_appl_cur IS
SELECT s_admission_process_type, admission_cat
FROM igs_ad_appl
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_application_number;
SELECT 1 FROM IGS_AD_PRCS_CAT_STEP APCS , IGS_TR_TYPE_ALL TRT
WHERE
APCS.S_ADMISSION_STEP_TYPE = TRT.TRACKING_TYPE AND
APCS.STEP_GROUP_TYPE = 'TRACK' AND
TRT.S_TRACKING_TYPE IS NOT NULL
AND admission_cat = p_adm_cat
AND s_admission_process_type = p_adm_prc_typ;
END check_update_aeps_acs;
SELECT application_type
FROM igs_ad_appl
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT
gl_rev_acct_ccid,
gl_cash_acct_ccid,
rev_account_code,
cash_account_code
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = l_adm_application_type;
SELECT
(NVL(apl.appl_fee_amt,0)-sum(req.FEE_AMOUNT)) OutstandingBal
FROM
IGS_AD_APPL_ALL apl,
igs_ad_app_req req
WHERE
apl.person_id= p_person_id AND
apl.admission_appl_number = p_admission_appl_number AND
apl.person_id = req.person_id AND
apl.admission_appl_number= req.admission_appl_number AND
EXISTS (SELECT 'x'
FROM igs_ad_code_classes
WHERE class = 'SYS_FEE_TYPE'
AND system_status = 'APPL_FEE'
AND applicant_fee_type = code_id
AND CLASS_TYPE_CODE = 'ADM_CODE_CLASSES')
GROUP BY apl.appl_fee_amt;
SELECT
NVL(apl.appl_fee_amt,0) appfee
FROM IGS_AD_APPL_ALL apl
WHERE
apl.person_id= p_person_id AND
apl.admission_appl_number = p_admission_appl_number;
Modified call to igs_ad_app_req_pkg.insert_row() - added 3 new parameters
VVUTUKUR 26-NOV-2002 Enh#2584986.GL Interface Build. Added 9 new parameters to this procedure.
These additional attributes, i.e. credit card details, Accounting information and the GL_DATE are passed to the call to igs_ad_app_req_pkg.insert_row.
***************************************************************/
--CURSOR which checks whether the application is a valid one
CURSOR is_valid_appl_cur(l_person_id IN NUMBER, l_admission_appl_number IN NUMBER) IS
SELECT person_id
FROM igs_ad_appl apai
WHERE apai.person_id = l_person_id AND
apai.admission_appl_number = l_admission_appl_number;
SELECT code_id
FROM igs_ad_code_classes
WHERE system_status = p_sys_fee_method
AND system_default = 'Y'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT code_id
FROM igs_ad_code_classes
WHERE system_status = p_sys_fee_status
AND system_default = 'Y'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT code_id
FROM igs_ad_code_classes
WHERE system_status = p_sys_fee_type
AND system_default = 'Y'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
igs_ad_app_req_pkg.insert_row(
X_ROWID => l_rowid,
X_APP_REQ_ID => l_app_req_id,
X_PERSON_ID => p_person_id,
X_ADMISSION_APPL_NUMBER => p_admission_appl_number,
X_APPLICANT_FEE_TYPE => l_default_fee_type,
X_APPLICANT_FEE_STATUS => l_default_fee_status,
X_FEE_DATE => TRUNC(SYSDATE),
X_FEE_PAYMENT_METHOD => l_default_pay_method,
X_FEE_AMOUNT => p_app_fee_amt,
X_REFERENCE_NUM => p_authorization_number,
X_CREDIT_CARD_CODE => p_credit_card_code,
X_CREDIT_CARD_HOLDER_NAME => p_credit_card_holder_name,
X_CREDIT_CARD_NUMBER => p_credit_card_number,
X_CREDIT_CARD_EXPIRATION_DATE => p_credit_card_expiration_date,
X_REV_GL_CCID => p_rev_gl_ccid,
X_CASH_GL_CCID => p_cash_gl_ccid,
X_REV_ACCOUNT_CD => p_rev_account_cd,
X_CASH_ACCOUNT_CD => p_cash_account_cd,
X_GL_DATE => p_gl_date,
X_GL_POSTED_DATE => NULL,
X_POSTING_CONTROL_ID => NULL,
x_credit_card_tangible_cd => p_credit_card_tangible_cd,
x_credit_card_payee_cd => fnd_profile.value('IGS_FI_PAYEE_NAME'),
x_credit_card_status_code => 'PENDING'
);