78: p_sequence_number NUMBER) IS
79: SELECT
80: a.*
81: FROM
82: igs_ad_ps_appl_inst a,
83: igs_ad_ou_stat c
84: WHERE a.person_id = p_person_id
85: AND a.admission_appl_number = p_admission_appl_number
86: AND a.nominated_course_cd = p_nominated_course_cd
116: l_msg_index := igs_ge_msg_stack.count_msg;
117:
118: -- Validate all the parameters for their length
119: -- PERSON_ID
120: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
121: -- P_ADMISSION_APPL_NUMBER
122: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
123: -- p_nominated_program_cd
124: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
118: -- Validate all the parameters for their length
119: -- PERSON_ID
120: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
121: -- P_ADMISSION_APPL_NUMBER
122: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
123: -- p_nominated_program_cd
124: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
125: -- P_SEQUENCE_NUMBER
126: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
120: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
121: -- P_ADMISSION_APPL_NUMBER
122: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
123: -- p_nominated_program_cd
124: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
125: -- P_SEQUENCE_NUMBER
126: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
127: -- P_ACADEMIC_INDEX
128: check_length('ACADEMIC_INDEX', 'IGS_AD_PS_APPL_INST_ALL', length(p_academic_index));
122: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
123: -- p_nominated_program_cd
124: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
125: -- P_SEQUENCE_NUMBER
126: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
127: -- P_ACADEMIC_INDEX
128: check_length('ACADEMIC_INDEX', 'IGS_AD_PS_APPL_INST_ALL', length(p_academic_index));
129: -- P_PREDICTED_GPA
130: check_length('PREDICTED_GPA', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_predicted_gpa)));
124: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
125: -- P_SEQUENCE_NUMBER
126: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
127: -- P_ACADEMIC_INDEX
128: check_length('ACADEMIC_INDEX', 'IGS_AD_PS_APPL_INST_ALL', length(p_academic_index));
129: -- P_PREDICTED_GPA
130: check_length('PREDICTED_GPA', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_predicted_gpa)));
131: -- END OF PARAMETER VALIDATIONS.
132:
126: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
127: -- P_ACADEMIC_INDEX
128: check_length('ACADEMIC_INDEX', 'IGS_AD_PS_APPL_INST_ALL', length(p_academic_index));
129: -- P_PREDICTED_GPA
130: check_length('PREDICTED_GPA', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_predicted_gpa)));
131: -- END OF PARAMETER VALIDATIONS.
132:
133:
134: -- Initialize API return status to success
178: -- IF c_doc_status%FOUND THEN
179: -- BEGIN
180: -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'E: Record found with SATISFIED STATUS');
181: -- Call to update row
182: igs_ad_ps_appl_inst_pkg.update_row(
183: x_rowid => l_appl_inst_rec.row_id,
184: x_person_id => l_appl_inst_rec.person_id,
185: x_admission_appl_number => l_appl_inst_rec.admission_appl_number,
186: x_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
430: l_c_appl_cur c_appl_cur%ROWTYPE;
431:
432: CURSOR c_aplinst_cur IS
433: SELECT a.ROWID, a.*, b.req_for_reconsideration_ind
434: FROM igs_ad_ps_appl_inst a, IGS_AD_PS_APPL b
435: WHERE a.person_id = p_person_id
436: AND a.admission_appl_number = p_admission_appl_number
437: AND a.nominated_course_cd = p_nominated_program_cd
438: AND a.sequence_number = p_sequence_number
463: AND step_group_type <> 'TRACK';
464:
465: CURSOR c_adm_ofr_resp_stat_cur IS
466: SELECT a.adm_offer_resp_status
467: FROM igs_ad_ps_appl_inst a
468: WHERE person_id = p_person_id
469: AND admission_appl_number = p_admission_appl_number
470: AND nominated_course_cd = p_nominated_program_cd
471: AND sequence_number = p_sequence_number;
491: l_reconsider_flag VARCHAR2(1) ;
492: l_prpsd_commencement_date DATE ;
493: l_cndtnl_offer_cndtn VARCHAR2(2000) ;
494: l_cndtl_offer_must_stsfd_ind VARCHAR2(1) ;
495: l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
492: l_prpsd_commencement_date DATE ;
493: l_cndtnl_offer_cndtn VARCHAR2(2000) ;
494: l_cndtl_offer_must_stsfd_ind VARCHAR2(1) ;
495: l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
493: l_cndtnl_offer_cndtn VARCHAR2(2000) ;
494: l_cndtl_offer_must_stsfd_ind VARCHAR2(1) ;
495: l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501: l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
494: l_cndtl_offer_must_stsfd_ind VARCHAR2(1) ;
495: l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501: l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
502: l_adm_outcome_status_reason igs_ad_ps_appl_inst.adm_outcome_status_reason%TYPE;
495: l_actual_response_dt igs_ad_ps_appl_inst.actual_response_dt%TYPE;
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501: l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
502: l_adm_outcome_status_reason igs_ad_ps_appl_inst.adm_outcome_status_reason%TYPE;
503:
496: l_adm_outcome_status igs_ad_ps_appl_inst.adm_outcome_status%TYPE ;
497: l_adm_offer_resp_status igs_ad_ps_appl_inst.adm_offer_resp_status%TYPE ;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501: l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
502: l_adm_outcome_status_reason igs_ad_ps_appl_inst.adm_outcome_status_reason%TYPE;
503:
504: l_uc_tran_id igs_uc_transactions.uc_tran_id%TYPE;
498: l_cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst.cndtnl_offer_satisfied_dt%TYPE ;
499: l_adm_cndtl_offer_status igs_ad_ps_appl_inst.adm_cndtnl_offer_status%TYPE ;
500: l_adm_outcome_status_auth_dt igs_ad_ps_appl_inst.adm_outcome_status_auth_dt%TYPE;
501: l_adm_otcm_status_auth_per_num hz_parties.party_number%TYPE;
502: l_adm_outcome_status_reason igs_ad_ps_appl_inst.adm_outcome_status_reason%TYPE;
503:
504: l_uc_tran_id igs_uc_transactions.uc_tran_id%TYPE;
505: l_s_adm_outcome_status igs_ad_ou_Stat.s_adm_outcome_status%TYPE;
506:
541:
542:
543: -- Validate all the parameters for their length
544: -- PERSON_ID
545: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
546: -- P_ADMISSION_APPL_NUMBER
547: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
548: -- p_nominated_program_cd
549: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
543: -- Validate all the parameters for their length
544: -- PERSON_ID
545: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
546: -- P_ADMISSION_APPL_NUMBER
547: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
548: -- p_nominated_program_cd
549: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
550: -- P_SEQUENCE_NUMBER
551: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
545: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
546: -- P_ADMISSION_APPL_NUMBER
547: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
548: -- p_nominated_program_cd
549: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
550: -- P_SEQUENCE_NUMBER
551: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
552: -- P_ADM_OUTCOME_STATUS
553: check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
547: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
548: -- p_nominated_program_cd
549: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
550: -- P_SEQUENCE_NUMBER
551: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
552: -- P_ADM_OUTCOME_STATUS
553: check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
554: -- P_DECISION_MAKER_ID
555: check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
549: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
550: -- P_SEQUENCE_NUMBER
551: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
552: -- P_ADM_OUTCOME_STATUS
553: check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
554: -- P_DECISION_MAKER_ID
555: check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
556: -- P_DECISION_REASON_ID
557: check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
551: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
552: -- P_ADM_OUTCOME_STATUS
553: check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
554: -- P_DECISION_MAKER_ID
555: check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
556: -- P_DECISION_REASON_ID
557: check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
558: -- P_PENDING_REASON_ID
559: check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
553: check_length('ADM_OUTCOME_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status));
554: -- P_DECISION_MAKER_ID
555: check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
556: -- P_DECISION_REASON_ID
557: check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
558: -- P_PENDING_REASON_ID
559: check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
560: -- P_ADM_OTCM_STATUS_AUTH_PER_ID
561: check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
555: check_length('DECISION_MAKE_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_maker_id)));
556: -- P_DECISION_REASON_ID
557: check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
558: -- P_PENDING_REASON_ID
559: check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
560: -- P_ADM_OTCM_STATUS_AUTH_PER_ID
561: check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
562: -- P_ADM_OUTCOME_STATUS_REASON
563: check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
557: check_length('DECISION_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_decision_reason_id)));
558: -- P_PENDING_REASON_ID
559: check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
560: -- P_ADM_OTCM_STATUS_AUTH_PER_ID
561: check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
562: -- P_ADM_OUTCOME_STATUS_REASON
563: check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
564: -- P_ADM_CNDTNL_OFFER_STATUS
565: check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
559: check_length('PENDING_REASON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_pending_reason_id)));
560: -- P_ADM_OTCM_STATUS_AUTH_PER_ID
561: check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
562: -- P_ADM_OUTCOME_STATUS_REASON
563: check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
564: -- P_ADM_CNDTNL_OFFER_STATUS
565: check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
566: -- P_CNDTNL_OFFER_CNDTN
567: check_length('CNDTNL_OFFER_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtnl_offer_cndtn));
561: check_length('ADM_OTCM_STATUS_AUTH_PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_adm_otcm_status_auth_per_id)));
562: -- P_ADM_OUTCOME_STATUS_REASON
563: check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
564: -- P_ADM_CNDTNL_OFFER_STATUS
565: check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
566: -- P_CNDTNL_OFFER_CNDTN
567: check_length('CNDTNL_OFFER_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtnl_offer_cndtn));
568: -- P_CNDTL_OFFER_MUST_STSFD_IND
569: check_length('CNDTNL_OFFER_MUST_BE_STSFD_IND', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtl_offer_must_stsfd_ind));
563: check_length('ADM_OUTCOME_STATUS_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_outcome_status_reason));
564: -- P_ADM_CNDTNL_OFFER_STATUS
565: check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
566: -- P_CNDTNL_OFFER_CNDTN
567: check_length('CNDTNL_OFFER_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtnl_offer_cndtn));
568: -- P_CNDTL_OFFER_MUST_STSFD_IND
569: check_length('CNDTNL_OFFER_MUST_BE_STSFD_IND', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtl_offer_must_stsfd_ind));
570: -- P_RECONSIDER_FLAG
571: check_length('REQ_FOR_RECONSIDERATION_IND', 'IGS_AD_PS_APPL', length(p_reconsider_flag));
565: check_length('ADM_CNDTNL_OFFER_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_cndtnl_offer_status));
566: -- P_CNDTNL_OFFER_CNDTN
567: check_length('CNDTNL_OFFER_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtnl_offer_cndtn));
568: -- P_CNDTL_OFFER_MUST_STSFD_IND
569: check_length('CNDTNL_OFFER_MUST_BE_STSFD_IND', 'IGS_AD_PS_APPL_INST_ALL', length(p_cndtl_offer_must_stsfd_ind));
570: -- P_RECONSIDER_FLAG
571: check_length('REQ_FOR_RECONSIDERATION_IND', 'IGS_AD_PS_APPL', length(p_reconsider_flag));
572:
573:
976: END IF;
977:
978: -- end apadegal adtd001 igs.m
979:
980: igs_ad_ps_appl_inst_pkg.update_row(x_rowid => l_c_aplinst_cur.ROWID,
981: x_person_id => l_c_aplinst_cur.person_id,
982: x_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
983: x_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
984: x_sequence_number => l_c_aplinst_cur.sequence_number,
1141:
1142: IF p_ucas_transaction = 'Y' THEN
1143: -- Check the message count in stack before calling TBH.
1144: IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') = 'GB' THEN
1145: igs_ad_ps_appl_inst_pkg.ucas_user_hook(p_admission_appl_number => l_c_aplinst_cur.admission_appl_number,
1146: p_nominated_course_cd => l_c_aplinst_cur.nominated_course_cd,
1147: p_sequence_number => l_c_aplinst_cur.sequence_number,
1148: p_adm_outcome_status => l_adm_outcome_status,
1149: p_cond_offer_status => l_c_aplinst_cur.adm_cndtnl_offer_status,
1280: p_adm_offer_resp_status IN igs_ad_offresp_int.adm_offer_resp_status%TYPE ,
1281: p_actual_offer_response_dt IN igs_ad_offresp_int.actual_offer_response_dt%TYPE,
1282: p_applicant_acptnce_cndtn IN igs_ad_offresp_int.applicant_acptnce_cndtn%TYPE,
1283: -- p_attent_other_inst_cd IN igs_ad_offresp_int.attent_other_inst_cd%TYPE,
1284: p_adm_offer_defr_status IN OUT NOCOPY igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,
1285: -- p_authorized_dt IN DATE, -- if null then default it to sys date. and validation exists in Form(pld)
1286: -- p_authorizing_pers_id IN NUMBER, --NUMBER(15) LOV (No validation) need to write validations
1287: p_def_acad_cal_type IN igs_ad_offresp_int.def_acad_cal_type%TYPE ,
1288: p_def_acad_ci_sequence_number IN igs_ad_offresp_int.def_acad_ci_sequence_number%TYPE,
1314: WHERE person_id = cp_person_id AND
1315: admission_appl_number = cp_admission_appl_number;
1316:
1317: -- to get teh application instance
1318: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
1319: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
1320: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322: ) IS
1315: admission_appl_number = cp_admission_appl_number;
1316:
1317: -- to get teh application instance
1318: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
1319: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
1320: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322: ) IS
1323: SELECT aplinst.rowid, aplinst.*
1316:
1317: -- to get teh application instance
1318: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
1319: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
1320: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322: ) IS
1323: SELECT aplinst.rowid, aplinst.*
1324: FROM igs_ad_ps_appl_inst_all aplinst
1317: -- to get teh application instance
1318: CURSOR cur_ad_ps_appl_inst ( cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE ,
1319: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE ,
1320: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322: ) IS
1323: SELECT aplinst.rowid, aplinst.*
1324: FROM igs_ad_ps_appl_inst_all aplinst
1325: WHERE person_id = cp_person_id AND
1320: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE ,
1321: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE
1322: ) IS
1323: SELECT aplinst.rowid, aplinst.*
1324: FROM igs_ad_ps_appl_inst_all aplinst
1325: WHERE person_id = cp_person_id AND
1326: admission_appl_number = cp_admission_appl_number AND
1327: nominated_course_cd = cp_nominated_course_cd AND
1328: sequence_number = cp_sequence_number;
1362: v_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
1363: v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
1364: v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1365: v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1366: v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
1367: v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
1368: v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1369: v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1370: v_appl_dt igs_ad_appl.appl_dt%TYPE;
1363: v_acad_ci_sequence_number igs_ad_appl.acad_ci_sequence_number%TYPE;
1364: v_aa_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1365: v_aa_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1366: v_acaiv_adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE;
1367: v_acaiv_adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE;
1368: v_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
1369: v_adm_ci_sequence_number igs_ad_appl.adm_ci_sequence_number%TYPE;
1370: v_appl_dt igs_ad_appl.appl_dt%TYPE;
1371: v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
1371: v_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
1372: v_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
1373: l_single_response_flag igs_ad_prd_ad_prc_ca.single_response_flag%TYPE;
1374: l_application_id igs_ad_appl_all.application_id%TYPE;
1375: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1376: l_acad_alt_code igs_ca_inst.alternate_code%TYPE;
1377: l_adm_alt_code igs_ca_inst.alternate_code%TYPE;
1378: l_acaiv_rec cur_ad_ps_appl_inst%ROWTYPE;
1379: l_appl_rec c_adm_appl_dtl%ROWTYPE;
1438: END IF;
1439:
1440:
1441: -- ********** VALIDATIONS WHICH CHECK FOR THE PROPER COMBINATION OF New Offer response status Vs SYSTEM TABLE Offer Response Status. Stop processing in case of failure
1442: -- Check if New Offer Response status = Production table (IGS_AD_PS_APPL_INST_ALL) Offer Response Status.
1443: IF p_adm_offer_resp_status = l_acaiv_rec.adm_offer_resp_status THEN
1444: p_validation_success := 'N';
1445: fnd_message.set_name('IGS', 'IGS_AD_RESP_SATUS_NOT_CHANGE');
1446: IGS_GE_MSG_STACK.ADD;
1721: l_validation_success VARCHAR2(1);
1722: l_calc_actual_response_dt DATE;
1723: l_adm_offer_dfrmnt_status VARCHAR2(10);
1724:
1725: l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1722: l_calc_actual_response_dt DATE;
1723: l_adm_offer_dfrmnt_status VARCHAR2(10);
1724:
1725: l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1723: l_adm_offer_dfrmnt_status VARCHAR2(10);
1724:
1725: l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1724:
1725: l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1725: l_response_comments igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE;
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1733:
1726: l_def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE;
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1727: l_def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE;
1728: l_def_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE;
1729: l_def_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE;
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1730: l_decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE;
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737: cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738: SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1731: l_attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE;
1732: l_enrl_message_name VARCHAR2(30);
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737: cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738: SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1739: WHERE person_id = cp_person_id and
1732: l_enrl_message_name VARCHAR2(30);
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737: cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738: SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1739: WHERE person_id = cp_person_id and
1740: admission_appl_number = cp_admission_appl_number and
1733:
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737: cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738: SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1739: WHERE person_id = cp_person_id and
1740: admission_appl_number = cp_admission_appl_number and
1741: nominated_course_cd = cp_nominated_course_cd and
1734: CURSOR cur_ad_ps_appl_inst( cp_person_id igs_ad_ps_appl_inst.person_id%type ,
1735: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%type ,
1736: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%type,
1737: cp_sequence_no igs_ad_ps_appl_inst.sequence_number%type) IS
1738: SELECT rowid , igs_ad_ps_appl_inst.* from igs_ad_ps_appl_inst
1739: WHERE person_id = cp_person_id and
1740: admission_appl_number = cp_admission_appl_number and
1741: nominated_course_cd = cp_nominated_course_cd and
1742: sequence_number = cp_sequence_no;
1762: l_msg_index := igs_ge_msg_stack.count_msg;
1763: --Assign all defaul values
1764:
1765: -- Validate all the parameters for their length
1766: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1763: --Assign all defaul values
1764:
1765: -- Validate all the parameters for their length
1766: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1764:
1765: -- Validate all the parameters for their length
1766: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1765: -- Validate all the parameters for their length
1766: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1766: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1767: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1768: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1769: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1770: check_length('ADM_OFFER_RESP_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_resp_status));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778: check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1771: check_length('APPLICANT_ACPTNCE_CNDTN', 'IGS_AD_PS_APPL_INST_ALL', length(p_response_comments));
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778: check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1779:
1772: -- check_length('ADM_OFFER_DFRMNT_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_adm_offer_dfrmnt_status));
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778: check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1779:
1780: l_adm_offer_dfrmnt_status := NULL;
1773: check_length('DEF_ACAD_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_acad_cal_type));
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778: check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1779:
1780: l_adm_offer_dfrmnt_status := NULL;
1781:
1774: check_length('DEF_ACAD_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_acad_ci_sequence_num)));
1775: check_length('DEFERRED_ADM_CAL_TYPE', 'IGS_AD_PS_APPL_INST_ALL', length(p_def_adm_cal_type));
1776: check_length('DEFERRED_ADM_CI_SEQUENCE_NUM', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_def_adm_ci_sequence_num)));
1777: check_length('DECLINE_OFR_REASON', 'IGS_AD_PS_APPL_INST_ALL', length(p_decline_ofr_reason));
1778: check_length('ATTENT_OTHER_INST_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_attent_other_inst_cd));
1779:
1780: l_adm_offer_dfrmnt_status := NULL;
1781:
1782: OPEN cur_ad_ps_appl_inst(p_person_id,
1890:
1891:
1892:
1893:
1894: IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (
1895: X_Mode => 'R',
1896: X_RowId => cur_ad_ps_appl_inst_rec.row_id,
1897: X_Person_Id => cur_ad_ps_appl_inst_rec.Person_Id,
1898: X_Admission_Appl_Number => cur_ad_ps_appl_inst_rec.Admission_Appl_Number,
2117: p_sequence_number NUMBER) IS
2118: SELECT
2119: a.*
2120: FROM
2121: igs_ad_ps_appl_inst a,
2122: igs_ad_ou_stat c
2123: WHERE a.person_id = p_person_id
2124: AND a.admission_appl_number = p_admission_appl_number
2125: AND a.nominated_course_cd = p_nominated_course_cd
2166: l_msg_index := igs_ge_msg_stack.count_msg;
2167:
2168: -- Validate all the parameters for their length
2169: -- PERSON_ID
2170: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2171: -- P_ADMISSION_APPL_NUMBER
2172: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2173: -- p_nominated_course_cd
2174: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_course_cd));
2168: -- Validate all the parameters for their length
2169: -- PERSON_ID
2170: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2171: -- P_ADMISSION_APPL_NUMBER
2172: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2173: -- p_nominated_course_cd
2174: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_course_cd));
2175: -- P_SEQUENCE_NUMBER
2176: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2170: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2171: -- P_ADMISSION_APPL_NUMBER
2172: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2173: -- p_nominated_course_cd
2174: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_course_cd));
2175: -- P_SEQUENCE_NUMBER
2176: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2177: -- P_qualifying_type_code
2178: check_length('QUALIFYING_TYPE_CODE', 'IGS_AD_APPQUAL_CODE', length(p_qualifying_type_code));
2172: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2173: -- p_nominated_course_cd
2174: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_course_cd));
2175: -- P_SEQUENCE_NUMBER
2176: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2177: -- P_qualifying_type_code
2178: check_length('QUALIFYING_TYPE_CODE', 'IGS_AD_APPQUAL_CODE', length(p_qualifying_type_code));
2179: -- P_QUALIFYING_CODE
2180: check_length('NAME', 'IGS_AD_CODE_CLASSES', length(p_qualifying_code));
2427:
2428: -- Cursor to fetch the application instance record details for a given set of person id, application number
2429: -- nominated course code and sequence number
2430:
2431: CURSOR c_acai ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2432: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
2433: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434: cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435: IS
2428: -- Cursor to fetch the application instance record details for a given set of person id, application number
2429: -- nominated course code and sequence number
2430:
2431: CURSOR c_acai ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2432: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
2433: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434: cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435: IS
2436: SELECT aplinst.*, aplinst.rowid
2429: -- nominated course code and sequence number
2430:
2431: CURSOR c_acai ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2432: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
2433: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434: cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435: IS
2436: SELECT aplinst.*, aplinst.rowid
2437: FROM igs_ad_ps_appl_inst aplinst
2430:
2431: CURSOR c_acai ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2432: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
2433: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434: cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435: IS
2436: SELECT aplinst.*, aplinst.rowid
2437: FROM igs_ad_ps_appl_inst aplinst
2438: WHERE aplinst.person_id = cp_person_id
2433: cp_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
2434: cp_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE)
2435: IS
2436: SELECT aplinst.*, aplinst.rowid
2437: FROM igs_ad_ps_appl_inst aplinst
2438: WHERE aplinst.person_id = cp_person_id
2439: AND aplinst.admission_appl_number = cp_admission_appl_number
2440: AND aplinst.nominated_course_cd = cp_nominated_course_cd
2441: AND aplinst.sequence_number = cp_sequence_number;
2442:
2443: l_appl_inst_rec c_acai%ROWTYPE;
2444:
2445: -- Cursor to fetch the system admission process type of the admission application
2446: CURSOR c_appl (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2447: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE)
2448: IS
2449: SELECT appl.s_admission_process_type
2450: FROM igs_ad_appl_all appl
2443: l_appl_inst_rec c_acai%ROWTYPE;
2444:
2445: -- Cursor to fetch the system admission process type of the admission application
2446: CURSOR c_appl (cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
2447: cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE)
2448: IS
2449: SELECT appl.s_admission_process_type
2450: FROM igs_ad_appl_all appl
2451: WHERE appl.person_id = cp_person_id
2469:
2470:
2471: -- Validate all the parameters for their length
2472: -- person_id
2473: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2474:
2475: -- p_admission_appl_number
2476: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2477:
2472: -- person_id
2473: check_length('PERSON_ID', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_person_id)));
2474:
2475: -- p_admission_appl_number
2476: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2477:
2478: -- p_nominated_course_cd
2479: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
2480:
2475: -- p_admission_appl_number
2476: check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_admission_appl_number)));
2477:
2478: -- p_nominated_course_cd
2479: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
2480:
2481: -- p_sequence_number
2482: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2483:
2478: -- p_nominated_course_cd
2479: check_length('NOMINATED_COURSE_CD', 'IGS_AD_PS_APPL_INST_ALL', length(p_nominated_program_cd));
2480:
2481: -- p_sequence_number
2482: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2483:
2484: -- p_entry_qual_status
2485: check_length('ADM_ENTRY_QUAL_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_entry_qual_status));
2486:
2481: -- p_sequence_number
2482: check_length('SEQUENCE_NUMBER', 'IGS_AD_PS_APPL_INST_ALL', length(TRUNC(p_sequence_number)));
2483:
2484: -- p_entry_qual_status
2485: check_length('ADM_ENTRY_QUAL_STATUS', 'IGS_AD_PS_APPL_INST_ALL', length(p_entry_qual_status));
2486:
2487: -- end of parameter validations.
2488:
2489: -- Show appropriate message when the parameter values are missing
2560: RAISE FND_API.G_EXC_ERROR;
2561: END IF;
2562:
2563: -- Update the application instance record with the new value of entry qualification status
2564: igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2565: X_Mode => 'S',
2566: X_RowId => l_appl_inst_rec.rowId,
2567: X_Person_Id => p_person_id,
2568: X_Admission_Appl_Number => p_admission_appl_number,