[Home] [Help]
151: and the Choice Number marked as lost.
152: 2) The Person Alternate Id stored in IGS_PE_ALT_PERS_ID table for the UCAS Wrong Applicaton should be end dated.
153: 3) Delete the Wrong Application related data from UCAS interface tables.
154: 4) If all wrong application data is successfully deleted from UCAS Interface tables then mark the applicants as
155: expunged by setting the flag in IGS_UC_WRONG_APP.EXPUNGED to 'Y'.
156: 5) log the message in the log file for the each step whether the processing is succussful or not.
157:
158: Know limitations, enhancements or remarks
159: Change History
174: igs_uc_applicants ucap
175: WHERE def.system_code (+) = ucap.system_code
176: AND def.system_code IS NULL
177: AND ucap.app_no IN ( SELECT app_no
178: FROM igs_uc_wrong_app wap
179: WHERE wap.expunge = 'Y'
180: AND wap.expunged <> 'Y'
181: AND wap.app_no = NVL(p_app_no, wap.app_no) );
182:
185: SELECT 'X'
186: FROM igs_uc_defaults def
187: WHERE system_code IN ( SELECT DISTINCT system_code
188: FROM igs_uc_applicants ucap,
189: igs_uc_wrong_app wap
190: WHERE ucap.app_no = wap.app_no
191: AND wap.expunge = 'Y'
192: AND wap.expunged <> 'Y'
193: AND wap.app_no = NVL(p_app_no, wap.app_no) )
195:
196: --To loop through all the Applicant records to be expunged.
197: CURSOR cur_wrong_app IS
198: SELECT wap.ROWID row_id, wap.*
199: FROM igs_uc_wrong_app wap
200: WHERE wap.app_no = NVL(p_app_no, wap.app_no)
201: AND wap.expunge = 'Y'
202: AND wap.expunged <> 'Y'
203: ORDER BY wap.app_no;
201: AND wap.expunge = 'Y'
202: AND wap.expunged <> 'Y'
203: ORDER BY wap.app_no;
204:
205: CURSOR cur_defaults (cp_app_no igs_uc_wrong_app.app_no%TYPE) IS
206: SELECT obsolete_outcome_status,
207: decision_make_id,
208: decision_reason_id
209: FROM igs_uc_defaults def,
211: WHERE def.system_code = ucap.system_code
212: AND ucap.app_no = cp_app_no;
213:
214: --To identify the OSS Admission Application instances, which are created FOR UCAS Application number to be expunged
215: CURSOR cur_oss_ad_appl_inst (cp_app_no igs_uc_wrong_app.app_no%TYPE,
216: cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
217: SELECT aap.person_id,
218: aap.admission_appl_number,
219: aap.alt_appl_id,
253: FROM igs_pe_person_base_v
254: WHERE person_id = cp_person_id;
255:
256: --To get the Person alternate ID record to End Date
257: CURSOR cur_alt_pers_id (cp_app_no igs_uc_wrong_app.app_no%TYPE) IS
258: SELECT api.ROWID row_id, api.*
259: FROM igs_pe_alt_pers_id api,
260: igs_uc_applicants ucap
261: WHERE api.pe_person_id = ucap.oss_person_id
650: fnd_file.put_line(fnd_file.log,fnd_message.get()||' - '||l_mesg_data);
651: END;
652: END IF; ----End of Check FOR all oss applications are suspended and Alternate IDs are end dated.
653:
654: -- IGS_UC_WRONG_APP.EXPUNGED can be set to 'Y' in following 2 conditions.
655: -- 1. All application choice details and related OSS application instances are expunged and
656: -- Alternate Person IDs are closed and Interface Records are also succussfully deleted
657: -- then mark the Wrong Applicant record as expunged.
658: -- 2. All application choice details maked as LOST are expunged and also the related OSS application
660: -- which are not marked as LOST then mark the Wrong Applicant as expunged.
661: IF ( l_ucas_app_expunged = 'Y' AND l_pe_alt_pers_id_closed = 'Y' AND l_ucas_app_recs_deleted = 'Y' ) OR
662: ( l_ucas_app_expunged = 'N' AND l_all_makred_app_inst_expunged = 'Y' ) THEN
663: BEGIN
664: igs_uc_wrong_app_pkg.update_row ( x_mode => 'R',
665: x_rowid => l_wrong_app_rec.row_id,
666: x_wrong_app_id => l_wrong_app_rec.wrong_app_id,
667: x_app_no => l_wrong_app_rec.app_no,
668: x_miscoded => l_wrong_app_rec.miscoded,