[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_004
Source
1 PACKAGE BODY igs_ad_imp_004 AS
2 /* $Header: IGSAD82B.pls 120.4 2005/09/30 05:47:29 appldev ship $ */
3 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
4 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
5 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
6 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
7
8
9 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
10 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
11 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
12 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
13 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
14 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
15 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
16 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
17 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
18 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
19 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
20 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
21 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
22 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
23 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
24 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
25
26 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
27 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
28 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
29 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
30
31 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
32 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
33 cst_ec_val_NULL CONSTANT VARCHAR2(4) := NULL;
34
35 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
36 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
37 cst_unique_record CONSTANT NUMBER := 1;
38
39 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes*******************/
40
41 -- Initialize Application Status , Fee status
42 l_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE ;
43 l_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE ;
44 l_Late_Adm_Fee_Status IGS_AD_FEE_STAT.adm_fee_status%TYPE ;
45 l_cndnl_ofr_must_be_stsfd_ind IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind%TYPE ;
46 l_adm_pending_outcome_status VARCHAR2(127) ;
47 l_adm_cndtnl_offer_status VARCHAR2(127) ;
48 l_adm_offer_resp_status VARCHAR2(127) ;
49 l_adm_offer_dfrmnt_status VARCHAR2(127) ;
50 l_admission_application_type IGS_AD_APPL.application_type%TYPE ;
51 l_admission_cat IGS_AD_APPL.admission_cat%TYPE ;
52 l_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE ;
53 l_adm_doc_status igs_ad_ps_appl_inst.adm_doc_status%TYPE;
54
55 l_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE ;
56 l_enrolment_cat IGS_EN_ENROLMENT_CAT.enrolment_cat%TYPE ;
57 l_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE ;
58 l_adm_entry_qual_status VARCHAR2(127) ;
59 l_correspondence_cat IGS_CO_CAT.correspondence_cat%TYPE;
60 l_funding_source IGS_FI_FUND_SRC.funding_source%TYPE;
61
62 l_rank_set_allowed VARCHAR2(1);
63 l_unit_set_allowed VARCHAR2(1);
64 l_fin_aid_allowed VARCHAR2(1);
65 l_fee_cat_allowed VARCHAR2(1);
66 l_enrol_cat_allowed VARCHAR2(1);
67 l_fund_src_allowed VARCHAR2(1);
68 l_adv_std_allowed VARCHAR2(1);
69 l_int_acc_adv_no_allowed VARCHAR2(1);
70 l_pref_allowed VARCHAR2(1);
71 l_comp_yr_allowed VARCHAR2(1);
72 l_edu_goal_allowed VARCHAR2(1);
73
74 PROCEDURE populate_apc_columns(p_admission_application_type IGS_AD_APPL.APPLICATION_TYPE%TYPE) IS
75 v_description VARCHAr2(2000);
76 l_appl_type_valid VARCHAR2(10);
77 /*---------------------------------------------------------------------------------------------------------
78 Check the APC allows following attribute if not and user provides values in columns error out.
79 Attribute step type
80 1. Program Rank Set RANK-SET
81 2. Unit Set UNIT-SET
82 3. Financial Aid FINAID
83 4. Fee Category FEE-ASSESS
84 5. Enrollment Category ENRCATEGRY
85 6. Funding Source FUNDSOURCE
86 7. Advanced Standing REQ-ADV
87 8. International Acceptance Advice Number CRS-INTERN
88 ----------------------------------------------------------------------------------------------------------*/
89 BEGIN
90 l_appl_type_Valid := igs_ad_gen_016.get_appl_type_apc (p_admission_application_type ,
91 l_admission_cat ,
92 l_s_admission_process_type) ;
93 IF l_s_admission_process_type <> 'RE-ADMIT' THEN
94 l_fee_cat := IGS_AD_GEN_005.admp_get_dflt_fcm(
95 l_admission_cat,
96 v_description);
97
98 l_hecs_payment_option := IGS_AD_GEN_005.admp_get_dflt_hpo(
99 l_admission_cat,
100 v_description);
101
102 l_enrolment_cat := IGS_AD_GEN_005.admp_get_dflt_ecm(
103 l_admission_cat,
104 v_description);
105 END IF;
106
107 -----------------------------------------------------------------------------------------
108 -- Get the Application Status and Entry Qualification Status for the application instance
109 -----------------------------------------------------------------------------------------
110 IF l_s_admission_process_type = 'NON-AWARD' THEN
111 l_adm_entry_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('NOT-APPLIC');
112 l_adm_doc_status := IGS_AD_GEN_009.admp_get_sys_ads('NOT-APPLIC');
113 ELSE
114 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
115 p_s_admission_process_type => l_s_admission_process_type,
116 p_s_admission_step_type => 'DFLT_ENTRY_QUAL') = 'FALSE' THEN
117 l_adm_entry_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('PENDING');
118 ELSE
119 l_adm_entry_qual_status := IGS_AD_GEN_009.admp_get_sys_aeqs('QUALIFIED');
120 END IF;
121 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
122 p_s_admission_process_type => l_s_admission_process_type,
123 p_s_admission_step_type => 'DFLT_DOC_STATUS') = 'FALSE' THEN
124 l_adm_doc_status:= IGS_AD_GEN_009.admp_get_sys_ads('PENDING');
125 ELSE
126 l_adm_doc_status:= IGS_AD_GEN_009.admp_get_sys_ads('SATISFIED');
127 END IF;
128
129 END IF;
130 l_admission_application_type := p_admission_application_type ;
131
132 -- Program Rank Set
133 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
134 p_s_admission_process_type => l_s_admission_process_type,
135 p_s_admission_step_type => 'RANK-SET') = 'FALSE' THEN
136 l_rank_set_allowed := 'N';
137 ELSE
138 l_rank_set_allowed := 'Y';
139 END IF;
140 -- Unit Set
141 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
142 p_s_admission_process_type => l_s_admission_process_type,
143 p_s_admission_step_type => 'UNIT-SET') = 'FALSE' THEN
144 l_unit_set_allowed := 'N';
145 ELSE
146 l_unit_set_allowed := 'Y';
147 END IF;
148 --Financial Aid
149 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
150 p_s_admission_process_type => l_s_admission_process_type,
151 p_s_admission_step_type => 'FINAID') = 'FALSE' THEN
152 l_fin_aid_allowed := 'N';
153 ELSE
154 l_fin_aid_allowed := 'Y';
155 END IF;
156 --Fee Category
157 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
158 p_s_admission_process_type => l_s_admission_process_type,
159 p_s_admission_step_type => 'FEE-ASSESS') = 'FALSE' THEN
160 l_fee_cat_allowed := 'N';
161 ELSE
162 l_fee_cat_allowed := 'Y';
163 END IF;
164 --Enrollment Category
165 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
166 p_s_admission_process_type => l_s_admission_process_type,
167 p_s_admission_step_type => 'ENRCATEGRY') = 'FALSE' THEN
168 l_enrol_cat_allowed := 'N';
169 ELSE
170 l_enrol_cat_allowed := 'Y';
171 END IF;
172 -- Funding Source
173 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
174 p_s_admission_process_type => l_s_admission_process_type,
175 p_s_admission_step_type => 'FUNDSOURCE') = 'FALSE' THEN
176 l_fund_src_allowed := 'N';
177 ELSE
178 l_fund_src_allowed := 'Y';
179 END IF;
180
181 --Advanced Standing
182 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
183 p_s_admission_process_type => l_s_admission_process_type,
184 p_s_admission_step_type => 'ADVSTAND') = 'FALSE' THEN
185 l_adv_std_allowed := 'N';
186 ELSE
187 l_adv_std_allowed := 'Y';
188 END IF;
189 --International Acceptance Advice Number
190 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
191 p_s_admission_process_type => l_s_admission_process_type,
192 p_s_admission_step_type => 'CRS-INTERN') = 'FALSE' THEN
193 l_int_acc_adv_no_allowed := 'N';
194 ELSE
195 l_int_acc_adv_no_allowed := 'Y';
196 END IF;
197
198 --International Acceptance Advice Number
199 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
200 p_s_admission_process_type => l_s_admission_process_type,
201 p_s_admission_step_type => 'PREF-LIMIT') = 'FALSE' THEN
202 l_pref_allowed := 'N';
203 ELSE
204 l_pref_allowed := 'Y';
205 END IF;
206
207 --International Acceptance Advice Number
208 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
209 p_s_admission_process_type => l_s_admission_process_type,
210 p_s_admission_step_type => 'COMPLETION') = 'FALSE' THEN
211 l_comp_yr_allowed := 'N';
212 ELSE
213 l_comp_yr_allowed := 'Y';
214 END IF;
215
216 --International Acceptance Advice Number
217 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
218 p_s_admission_process_type => l_s_admission_process_type,
219 p_s_admission_step_type => 'EDU-GOALS') = 'FALSE' THEN
220 l_edu_goal_allowed := 'N';
221 ELSE
222 l_edu_goal_allowed := 'Y';
223 END IF;
224
225 END populate_apc_columns;
226
227
228 -- This procedure update in the table in different session
229 -- so when we rollback the application , it wont rollback these transactions
230
231 PROCEDURE update_appl_inst(p_appl_inst_id NUMBER, p_status VARCHAR2,
232 p_error_text VARCHAR2, p_sequence_number NUMBER,p_error_code VARCHAR2,
233 p_admission_appl_number NUMBER ,
234 p_match_ind VARCHAR2 ) AS
235 PRAGMA AUTONOMOUS_TRANSACTION;
236 l_error_text igs_ad_ps_appl_inst_int.error_text%TYPE;
237 BEGIN
238 UPDATE igs_ad_ps_appl_inst_int
239 SET error_text = p_error_text,
240 status = p_status,
241 sequence_number = p_sequence_number,
242 error_code = p_error_code,
243 admission_appl_number = p_admission_appl_number,
244 match_ind = p_match_ind
245 WHERE interface_ps_appl_inst_id = p_appl_inst_id;
246
247 COMMIT;
248 END update_appl_inst;
249
250
251 PROCEDURE prc_appcln(
252 p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
253 p_rule VARCHAR2,
254 p_enable_log VARCHAR2,
255 p_legacy_ind IN VARCHAR2) AS
256
257 l_request_id NUMBER ;
258 l_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
259
260 CURSOR
261 appl_cur IS
262 SELECT cst_insert dmlmode, api.rowid, api.*
263 FROM igs_ad_apl_int api
264 WHERE api.interface_run_id = p_interface_run_id
265 AND api.status = '2'
266 AND ( p_rule = 'R' AND api.match_ind IN ('16', '25')
267 OR update_adm_appl_number IS NULL )
268 UNION ALL
269 SELECT cst_update dmlmode, api.rowid, api.*
270 FROM igs_ad_apl_int api
271 WHERE api.interface_run_id = p_interface_run_id
272 AND api.status = '2'
273 AND ( p_rule = 'I' OR (p_rule = 'R' AND api.match_ind = '21'))
274 AND update_adm_appl_number IS NOT NULL ;
275
276 -- To be used by instances where application already exists in OSS
277 CURSOR
278 appl_cur_upd( p_interface_appl_id igs_ad_apl_int.interface_appl_id%TYPE) IS
279 SELECT cst_update dmlmode, api.rowid, api.*
280 FROM igs_ad_apl_int api
281 WHERE api.interface_run_id = p_interface_run_id
282 AND interface_appl_id = p_interface_appl_id
283 AND status = cst_s_val_1;
284
285 CURSOR
286 applinst_cur(
287 cp_interface_appl_id igs_ad_apl_int.interface_appl_id%TYPE ) IS
288 SELECT
289 cst_insert dmlmode, aplinst.rowid, aplinst.*
290 FROM
291 igs_ad_ps_appl_inst_int aplinst
292 WHERE aplinst.status = '2'
293 AND aplinst.interface_run_id = p_interface_run_id
294 AND ( p_rule = 'R' AND aplinst.match_ind IN ('16', '25')
295 OR update_adm_seq_number IS NULL )
296 AND aplinst.interface_appl_id = NVL(cp_interface_appl_id , aplinst.interface_appl_id)
297 UNION ALL
298 SELECT
299 cst_update dmlmode, aplinst.rowid, aplinst.*
300 FROM
301 igs_ad_ps_appl_inst_int aplinst
302 WHERE aplinst.status = '2'
303 AND aplinst.interface_run_id = p_interface_run_id
304 AND aplinst.interface_appl_id = NVL(cp_interface_appl_id , aplinst.interface_appl_id)
305 AND ( p_rule = 'I' OR (p_rule = 'R' AND aplinst.match_ind = '21'))
306 AND update_adm_seq_number IS NOT NULL ;
307
308
309 CURSOR c_dup_cur(appl_rec appl_cur%ROWTYPE) IS
310 SELECT
311 appl_oss.rowid, appl_oss.*
312 FROM
313 IGS_AD_APPL appl_oss
314 WHERE person_id = appl_Rec.person_id
315 AND admission_Appl_number = appl_rec.update_adm_appl_number;
316
317 CURSOR dup_applinst_cur(applinst_rec applinst_cur%ROWTYPE,
318 p_person_id hz_parties.party_id%TYPE,
319 p_admission_appl_number IGS_AD_APPL.admission_Appl_number%TYPE
320 ) IS
321 SELECT
322 acai.rowid,acai.*
323 FROM
324 igs_ad_ps_appl_inst acai
325 WHERE
326 person_id = p_person_id
327 AND admission_appl_number = p_admission_appl_number
328 AND nominated_course_cd = applinst_rec.nominated_course_cd
329 AND sequence_number = applinst_rec.update_adm_seq_number;
330 dup_applinst_rec dup_applinst_cur%ROWTYPE;
331
332 dup_cur_rec c_dup_cur%ROWTYPE;
333 l_appl_type_Valid BOOLEAN;
334 l_Status_application VARCHAR2(1);
335 l_Status_instance VARCHAR2(1);
336 l_org_id NUMBER(15) ;
337 l_prog_label VARCHAR2(100) ;
338 l_label VARCHAR2(150) ;
339 l_debug_str VARCHAR2(150) ;
340 l_processed_records NUMBER(5);
341
342
343 FUNCTION validate_apc_steps(p_applinst_rec applinst_cur%ROWTYPE )
344 RETURN BOOLEAN IS
345 l_error_code VARCHAR2(4);
346 l_error_text VARCHAR2(2000);
347 BEGIN -- Validates the interface columns with apc steps
348 --i.e if the columns are having the valid values matchng to application type
349 IF l_rank_set_allowed = 'N' AND p_applinst_rec.course_rank_set IS NOT NULL THEN
350 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
351 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_COURSE_RANK_DTLS'));
352 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
353 l_error_text := FND_MESSAGE.GET;
354 l_error_code := 'E322';
355 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
356 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
357 IF p_enable_log = 'Y' THEN
358 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
359 END IF;
360 RETURN FALSE;
361 END IF;
362
363 IF l_unit_set_allowed = 'N' AND p_applinst_rec.unit_set_cd IS NOT NULL THEN
364 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
365 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_PS_UNIT_SET'));
366 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
367 l_error_text := FND_MESSAGE.GET;
368 l_error_code := 'E322';
369 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
370 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
371 IF p_enable_log = 'Y' THEN
372 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
373 END IF;
374 RETURN FALSE;
375 END IF;
376
377 IF l_fin_aid_allowed = 'N' AND p_applinst_rec.apply_for_finaid IS NOT NULL THEN
378 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
379 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FIN_AID'));
380 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
381 l_error_text := FND_MESSAGE.GET;
382 l_error_code := 'E322';
383 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
384 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
385 IF p_enable_log = 'Y' THEN
386 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
387 END IF;
388 RETURN FALSE;
389 END IF;
390
391 IF l_fee_cat_allowed = 'N' AND p_applinst_rec.fee_cat IS NOT NULL THEN
392 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
393 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FEE_CAT'));
394 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
395 l_error_code := 'E322';
396 l_error_text := FND_MESSAGE.GET;
397 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
398 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
399 IF p_enable_log = 'Y' THEN
400 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
401 END IF;
402 RETURN FALSE;
403 END IF;
404
405 IF l_enrol_cat_allowed = 'N' AND p_applinst_rec.enrolment_cat IS NOT NULL THEN
406 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
407 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENROLMENT_CAT'));
408 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
409 l_error_text := FND_MESSAGE.GET;
410 l_error_code := 'E322';
411 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
412 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
413 IF p_enable_log = 'Y' THEN
414 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
415 END IF;
416 RETURN FALSE;
417 END IF;
418
419 IF l_fund_src_allowed = 'N' AND p_applinst_rec.funding_source IS NOT NULL THEN
420 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
421 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FUNDING_SOURCE'));
422 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
423 l_error_code := 'E322';
424 l_error_text := FND_MESSAGE.GET;
425 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
426 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
427 IF p_enable_log = 'Y' THEN
428 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
429 END IF;
430 RETURN FALSE;
431 END IF;
432
433 IF l_adv_std_allowed = 'N' AND p_applinst_rec.req_for_adv_standing_ind = 'Y' THEN
434 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
435 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REQ_ADV_STD_IND'));
436 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
437 l_error_text := FND_MESSAGE.GET;
438 l_error_code := 'E322';
439 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
440 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
441 IF p_enable_log = 'Y' THEN
442 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
443 END IF;
444 RETURN FALSE;
445 END IF;
446
447 IF l_int_acc_adv_no_allowed = 'N' AND p_applinst_rec.intrntnl_acceptance_advice_num IS NOT NULL THEN
448 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
449 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INTRNL_ACC_ADV_NUM'));
450 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
451 l_error_text := FND_MESSAGE.GET;
452 l_error_code := 'E322';
453 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
454 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
455 IF p_enable_log = 'Y' THEN
456 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
457 END IF;
458 RETURN FALSE;
459 END IF;
460
461 IF l_pref_allowed = 'N' AND p_applinst_rec.preference_number IS NOT NULL THEN
462 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_PREFNUM_NOTBE_SPECIFY');
463 l_error_text := FND_MESSAGE.GET;
464 l_error_code := 'E322';
465 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
466 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
467 IF p_enable_log = 'Y' THEN
468 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
469 END IF;
470 RETURN FALSE;
471 END IF;
472
473 IF l_comp_yr_allowed = 'N' AND p_applinst_rec.expected_completion_yr IS NOT NULL THEN
474 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
475 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EXPCT_COMP_YR'));
476 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
477 l_error_text := FND_MESSAGE.GET;
478 l_error_code := 'E322';
479 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
480 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
481 IF p_enable_log = 'Y' THEN
482 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
483 END IF;
484 RETURN FALSE;
485 END IF;
486
487 IF l_comp_yr_allowed = 'N' AND p_applinst_rec.expected_completion_perd IS NOT NULL THEN
488 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
489 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EXPCT_COMP_PRD'));
490 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
491 l_error_text := FND_MESSAGE.GET;
492 l_error_code := 'E322';
493 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
494 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
495 IF p_enable_log = 'Y' THEN
496 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
497 END IF;
498 RETURN FALSE;
499 END IF;
500
501 IF l_edu_goal_allowed = 'N' AND p_applinst_rec.edu_goal_prior_enroll_id IS NOT NULL THEN
502 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_NOT_APC_STEP');
503 FND_MESSAGE.SET_TOKEN('CATEGORY',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PRI_EN_EDU_GOAL'));
504 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_admission_application_type);
505 l_error_text := FND_MESSAGE.GET;
506 l_error_code := 'E322';
507 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
508 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
509 IF p_enable_log = 'Y' THEN
510 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
511 END IF;
512 RETURN FALSE;
513 END IF;
514 RETURN TRUE;
515 END validate_apc_steps;
516 ---------------------------------------------------------------------------------------------------------
517
518 PROCEDURE create_application_details ( p_appl_rec appl_cur%ROWTYPE,
519 p_admission_appl_number OUT NOCOPY IGS_AD_APPL.admission_Appl_number%TYPE,
520 p_status OUT NOCOPY VARCHAR2) IS
521 /*-----------------------------------------------------------------------------------------
522 Created By: pbondugu
523 Date Created : 24-Nov-2003
524 Purpose: Import PRocess enhancements
525 Known limitations,enhancements,remarks:
526 Change History
527 Who When What
528 -----------------------------------------------------------------------------------------*/
529 CURSOR c_aa IS
530 SELECT NVL(MAX(admission_appl_number),0) + 1
531 FROM IGS_AD_APPL
532 WHERE person_id = p_appl_rec.person_id;
533
534 l_rowid VARCHAR2(25);
535 l_status VARCHAR2(1);
536 l_adm_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
537 l_msg_at_index NUMBER ;
538 l_return_status VARCHAR2(1);
539 l_msg_count NUMBER ;
540 l_msg_data VARCHAR2(2000);
541 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
542
543 l_error_code VARCHAR2(10);
544 l_error_text VARCHAR2(2000);
545 BEGIN
546 OPEN c_aa;
547 FETCH c_aa INTO l_adm_appl_number;
548 CLOSE c_aa;
549 BEGIN
550 p_status := 'S';
551 SAVEPOINT before_insert_appl;
552 l_msg_at_index := igs_ge_msg_stack.count_msg;
553 IGS_AD_APPL_PKG.insert_row (
554 X_Mode => 'R',
555 X_RowId => l_rowid,
556 X_Person_Id => p_appl_rec.Person_Id,
557 X_Admission_Appl_Number => l_adm_appl_number,
558 X_Appl_Dt => p_appl_rec.Appl_Dt,
559 X_Acad_Cal_Type => p_appl_rec.Acad_Cal_Type,
560 X_Acad_Ci_Sequence_Number => p_appl_rec.Acad_Ci_Sequence_Number,
561 X_Adm_Cal_Type => p_appl_rec.Adm_Cal_Type,
562 X_Adm_Ci_Sequence_Number => p_appl_rec.Adm_Ci_Sequence_Number,
563 X_Admission_Cat => l_admission_cat,
564 X_S_Admission_Process_Type => l_s_admission_process_type,
565 X_Adm_Appl_Status => l_adm_appl_status,
566 X_Adm_Fee_Status => l_adm_fee_status,
567 X_Tac_Appl_Ind => p_appl_rec.Tac_Appl_Ind,
568 X_Org_Id => l_org_id,
569 X_Spcl_Grp_1 => p_appl_rec.spcl_grp_1,
570 X_Spcl_Grp_2 => p_appl_rec.spcl_grp_2,
571 X_Common_App => p_appl_rec.common_app,
572 X_application_type => l_admission_application_type,
573 X_choice_number => p_appl_rec.choice_number,
574 X_routeb_pref => p_appl_rec.routeb_pref,
575 X_alt_appl_id => p_appl_rec.alt_appl_id,
576 X_appl_fee_amt => NVL(p_appl_rec.appl_fee_amt,0)
577 );
578 p_admission_appl_number := l_adm_appl_number;
579 EXCEPTION
580 WHEN OTHERS THEN
581 ROLLBACK TO before_insert_appl;
582 igs_ad_gen_016.extract_msg_from_stack (
583 p_msg_at_index => l_msg_at_index,
584 p_return_status => l_return_status,
585 p_msg_count => l_msg_count,
586 p_msg_data => l_msg_data,
587 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
588 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
589 l_error_text := l_msg_data;
590 l_error_code := 'E322';
591 p_status := 'E';
592 IF p_enable_log = 'Y' THEN
593 igs_ad_imp_001.logerrormessage(p_appl_rec.interface_appl_id,l_msg_data,'IGS_AD_APL_INT');
594 END IF;
595 ELSE
596 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
597 l_error_code := 'E518';
598 p_status := 'U';
599 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
600 l_label :='igs.plsql.igs_ad_imp_004.create_application_details.exception '|| l_msg_data;
601 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
602 fnd_message.set_token('INTERFACE_ID',p_appl_rec.interface_appl_id);
603 fnd_message.set_token('ERROR_CD','E322');
604
605 l_debug_str := fnd_message.get;
606
607 fnd_log.string_with_context( fnd_log.level_exception,
608 l_label,
609 l_debug_str, NULL,
610 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
611 END IF;
612
613 END IF;
614 UPDATE IGS_AD_APL_INT
615 SET status = cst_s_val_3,
616 error_code = l_error_code,
617 error_text = l_error_text,
618 match_ind = DECODE (
619 p_appl_rec.match_ind,
620 NULL, cst_mi_val_11,
621 match_ind)
622 WHERE interface_appl_id = p_appl_rec.interface_appl_id;
623 END;
624
625 END create_application_details;
626
627
628 PROCEDURE update_application_details ( p_appl_rec appl_cur%ROWTYPE, dup_cur_rec c_dup_cur%ROWTYPE
629 ) IS
630 /*-----------------------------------------------------------------------------------------
631 Created By: pbondugu
632 Date Created : 24-Nov-2003
633 Purpose: Import PRocess enhancements
634 Known limitations,enhancements,remarks:
635 Change History
636 Who When What
637 -----------------------------------------------------------------------------------------*/
638 l_rowid VARCHAR2(25);
639 l_adm_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
640 l_msg_at_index NUMBER ;
641 l_return_status VARCHAR2(1);
642 l_msg_count NUMBER ;
643 l_msg_data VARCHAR2(2000);
644 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
645
646 l_error_code VARCHAR2(10);
647 l_error_text VARCHAR2(2000);
648 BEGIN
649 BEGIN
650 l_msg_at_index := igs_ge_msg_stack.count_msg;
651 SAVEPOINT before_update_appl;
652 IGS_AD_APPL_PKG.update_row (
653 X_Mode => 'R',
654 X_RowId => dup_cur_rec.rowid,
655 X_Person_Id => p_appl_rec.Person_Id,
656 X_admission_appl_Number => p_appl_rec.update_adm_appl_number,
657 X_appl_dt => NVL( p_appl_rec.Appl_Dt,dup_cur_rec.Appl_Dt ),
658 X_Acad_Cal_Type => NVL(p_appl_rec.Acad_Cal_Type, dup_cur_rec.Acad_Cal_Type),
659 X_Acad_Ci_Sequence_Number => NVL(p_appl_rec.Acad_Ci_Sequence_Number, dup_cur_rec.Acad_Ci_Sequence_Number),
660 X_Adm_Cal_Type => NVL(p_appl_rec.Adm_Cal_Type, dup_cur_rec.Adm_Cal_Type),
661 X_Adm_Ci_Sequence_Number => NVL(p_appl_rec.Adm_Ci_Sequence_Number, dup_cur_rec.Adm_Ci_Sequence_Number),
662 X_Admission_Cat => dup_cur_rec.Admission_Cat ,
663 X_S_Admission_Process_Type => dup_cur_rec.s_admission_process_type,
664 X_Adm_Appl_Status => dup_cur_rec.Adm_Appl_Status,
665 X_Adm_Fee_Status => dup_cur_rec.adm_fee_status,
666 X_Tac_Appl_Ind => p_appl_rec.Tac_Appl_Ind,
667 X_Spcl_Grp_1 => NVL(p_appl_rec.spcl_grp_1, dup_cur_rec.spcl_grp_1),
668 X_Spcl_Grp_2 => NVL(p_appl_rec.spcl_grp_2, dup_cur_rec.spcl_grp_2),
669 X_Common_App => NVL(p_appl_rec.common_app, dup_cur_rec.common_app),
670 X_application_type => NVL(p_appl_rec.admission_application_type, dup_cur_rec.application_type),
671 X_choice_number => NVL(p_appl_rec.choice_number, dup_cur_rec.choice_number),
672 X_routeb_pref => NVL(p_appl_rec.routeb_pref, dup_cur_rec.routeb_pref),
673 X_alt_appl_id => NVL(p_appl_rec.alt_appl_id, dup_cur_rec.alt_appl_id),
674 x_appl_fee_amt => NVL(p_appl_rec.appl_fee_amt, dup_cur_rec.appl_fee_amt)
675 );
676 UPDATE
677 igs_ad_apl_int
678 SET
679 status = cst_s_val_1,
680 admission_Appl_number = p_appl_rec.update_adm_appl_number,
681 acad_ci_sequence_number = NVL(p_appl_rec.Acad_Ci_Sequence_Number, dup_cur_rec.Acad_Ci_Sequence_Number),
682 acad_cal_type = NVL(p_appl_rec.Acad_Cal_Type, dup_cur_rec.Acad_Cal_Type),
683 adm_cal_type = NVL(p_appl_rec.Adm_Cal_Type, dup_cur_rec.Adm_Cal_Type),
684 Adm_Ci_Sequence_Number = NVL(p_appl_rec.Adm_Ci_Sequence_Number, dup_cur_rec.Adm_Ci_Sequence_Number),
685 match_ind = DECODE (
686 p_appl_rec.match_ind,
687 NULL, cst_mi_val_11,
688 match_ind)
689 WHERE
690 interface_appl_id = p_appl_rec.interface_appl_id;
691 EXCEPTION
692 WHEN OTHERS THEN
693 ROLLBACK TO before_update_appl;
694 igs_ad_gen_016.extract_msg_from_stack (
695 p_msg_at_index => l_msg_at_index,
696 p_return_status => l_return_status,
697 p_msg_count => l_msg_count,
698 p_msg_data => l_msg_data,
699 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
700 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
701 l_error_text := l_msg_data;
702 l_error_code := 'E014';
703
704 IF p_enable_log = 'Y' THEN
705 igs_ad_imp_001.logerrormessage(p_appl_rec.interface_appl_id,l_msg_data,'IGS_AD_APL_INT');
706 END IF;
707 ELSE
708 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
709 l_error_code := 'E518';
710 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
711 l_label :='igs.plsql.igs_ad_imp_004.update_application_details.exception '|| l_msg_data;
712 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
713 fnd_message.set_token('INTERFACE_ID',p_appl_rec.interface_appl_id);
714 fnd_message.set_token('ERROR_CD','E322');
715
716 l_debug_str := fnd_message.get;
717
718 fnd_log.string_with_context( fnd_log.level_exception,
719 l_label,
720 l_debug_str, NULL,
721 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
722 END IF;
723
724 END IF;
725 UPDATE IGS_AD_APL_INT
726 SET status = cst_s_val_3,
727 error_code = l_error_code,
728 error_text = l_error_text,
729 match_ind = DECODE (
730 p_appl_rec.match_ind,
731 NULL, cst_mi_val_11,
732 match_ind)
733
734 WHERE interface_appl_id = p_appl_rec.interface_appl_id;
735 END;
736
737 END update_application_details;
738 --Declaration of create_single_appl_instance
739 PROCEDURE create_single_appl_instance(p_applinst_rec applinst_cur%ROWTYPE,
740 p_person_id hz_parties.party_id%TYPE,
741 p_admission_Appl_number IGS_AD_APPL.admission_appl_number%TYPE,
742 p_appl_rec appl_cur%ROWTYPE,
743 p_Status OUT NOCOPY VARCHAR2) ;
744
745 PROCEDURE create_application_instance(p_interface_appl_id IGS_AD_APL_INT.interface_appl_id%TYPE,
746 p_person_id hz_parties.party_id%TYPE,
747 p_admission_Appl_number IGS_AD_APPL.admission_appl_number%TYPE,
748 p_appl_rec appl_cur%ROWTYPE,
749 p_Status_instance OUT NOCOPY VARCHAR2) IS
750 --------------------------------------------------------------------------
751 -- Created By : pbondugu
752 -- Date Created On : 2003/11/22
753 -- Purpose:
754 -- Know limitations, enhancements or remarks
755 -- Change History
756 -- Who When What
757 -- (reverse chronological order - newest change first)
758 --------------------------------------------------------------------------
759 l_success_records NUMBER ;
760 l_Status VARCHAR2(4);
761 BEGIN
762 --Loop for all application instance interface records
763 -- craete the instance.
764 -- if success l_success_record := l_success_record +1 ;
765 -- if fails p_Status_instance := 'W'
766 -- END LOOP
767 -- After the loop if l_Sucess_record =0 then p_Status_instance := 'E' means all instances failed
768 l_success_records := 0;
769 l_Status := '1';
770 FOR applinst_rec IN applinst_cur(p_interface_appl_id) LOOP
771 create_single_appl_instance(applinst_rec, p_person_id, p_admission_Appl_number,p_appl_rec, l_status);
772 IF l_status = '3' THEN
773 p_Status_instance := 'W';
774 ELSE
775 l_success_records := l_success_records +1 ;
776 END IF;
777 END LOOP;
778 IF p_Status_instance <> 'W' THEN
779 p_Status_instance := 'S';
780 END IF;
781 IF l_success_records = 0 THEN
782 p_Status_instance := 'E';
783 END IF;
784
785 END create_application_instance;
786
787
788 PROCEDURE create_single_appl_instance(p_applinst_rec applinst_cur%ROWTYPE,
789 p_person_id hz_parties.party_id%TYPE,
790 p_admission_Appl_number IGS_AD_APPL.admission_appl_number%TYPE,
791 p_appl_rec appl_cur%ROWTYPE,
792 p_Status OUT NOCOPY VARCHAR2) IS
793 --------------------------------------------------------------------------
794 -- Created By : pbondugu
795 -- Date Created On : 2003/11/22
796 -- Purpose:
797 -- Know limitations, enhancements or remarks
798 -- Change History
799 -- Who When What
800 -- akadam 5/02/2004 Added the raise event as part of Bug 3391593
801 -- (reverse chronological order - newest change first)
802 --------------------------------------------------------------------------
803 l_error_text1 VARCHAR2(2000);
804 l_error_code1 NUMBER;
805 CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
806 cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
807 cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
808 IS
809 SELECT tab.* --multiorg table , so rowid need not be selected explicitly
810 FROM IGS_AD_PS_APPL tab
811 WHERE person_id = cp_person_id AND
812 admission_appl_number = cp_appl_no AND
813 nominated_course_cd = cp_nominated_course_cd;
814 c_prg_exists_rec c_prg_exists%ROWTYPE;
815
816 CURSOR c_session_info(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
817 cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE) IS
818 SELECT adm_cal_type, adm_ci_sequence_number, acad_cal_type , s_admission_process_type
819 FROM igs_Ad_appl_all
820 WHERE person_id = cp_person_id
821 AND admission_Appl_number = cp_appl_no;
822
823 CURSOR c_nxt_acai_seq_num IS
824 SELECT NVL(MAX(sequence_number), 0) + 1
825 FROM IGS_AD_PS_APPL_INST
826 WHERE
827 person_id = p_person_id AND
828 admission_appl_number = p_admission_appl_number AND
829 nominated_course_cd = p_applinst_rec.nominated_course_cd;
830
831 l_name igs_ad_code_classes.name%TYPE;
832 v_app_source igs_ad_code_classes.system_status%TYPE;
833 lv_rowid VARCHAR2(20);
834 v_description VARCHAR2(2000);
835 l_error_code VARCHAR2(4);
836 l_error_text VARCHAR2(2000);
837 l_app_source_id igs_ad_ps_appl_inst_all.app_source_id%TYPE;
838 l_msg_at_index NUMBER ;
839 l_return_status VARCHAR2(1);
840 l_msg_count NUMBER ;
841 l_msg_data VARCHAR2(2000);
842 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
843 l_completion_dt DATE;
844 l_course_start_dt DATE;
845 l_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
846 l_expected_completion_perd IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
847
848 l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
849 l_acai_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
850 PROCEDURE update_person_type(p_sequence_number IN igs_pe_typ_instances_all.sequence_number%TYPE
851 ,p_nominated_course_cd IN igs_pe_typ_instances_all.nominated_course_cd%TYPE
852 ,p_person_id IN igs_pe_typ_instances_all.person_id%TYPE
853 ,p_adm_appl_number IN igs_pe_typ_instances_all.admission_appl_number%TYPE ) AS
854
855 l_rowid VARCHAR2(25);
856 l_org_id NUMBER(15);
857 l_type_instance_id NUMBER;
858 l_person_type_code IGS_PE_PERSON_TYPES.person_type_code%TYPE;
859 CURSOR
860 c_person_type_code(l_system_type IGS_PE_PERSON_TYPES.system_type%TYPE) IS
861 SELECT
862 person_type_code
863 FROM
864 igs_pe_person_types
865 WHERE
866 system_type=l_system_type;
867 BEGIN --Begin Local Loop 1
868 l_org_id := igs_ge_gen_003.get_org_id;
869
870 OPEN c_person_type_code('APPLICANT');
871 FETCH c_person_type_code INTO l_person_type_code;
872 CLOSE c_person_type_code;
873
874 IGS_PE_TYP_INSTANCES_PKG.insert_row(
875 x_rowid=>l_rowid,
876 x_org_id=>l_org_id,
877 x_person_id=>p_person_id,
878 x_course_cd=>NULL,
879 x_type_instance_id=>l_type_instance_id,
880 x_person_type_code=>l_person_type_code,
881 x_cc_version_number=>NULL,
882 x_funnel_status =>NULL,
883 x_admission_appl_number=> p_admission_Appl_number,
884 x_nominated_course_cd=> p_nominated_course_cd,--c_admappl_pgm_rec.nominated_course_cd,
885 x_ncc_version_number=>NULL,
886 x_sequence_number =>p_sequence_number,
887 x_start_date=>SYSDATE,
888 x_end_date=>NULL,
889 x_create_method =>'CREATE_APPL_INSTANCE',
890 x_ended_by => NULL,
891 x_end_method =>NULL,
892 x_mode=>'R',
893 x_emplmnt_category_code =>null);
894 END update_person_type;
895 BEGIN
896 ------------------------------------------------------------------------------
897 --create_single_appl_instance
898 -------------------------------------------------------------------------------
899 IF NOT validate_apc_steps(p_applinst_rec) THEN
900 p_status := '3';
901 RETURN;
902 END IF;
903 OPEN c_prg_exists(p_person_id,p_admission_Appl_number,p_applinst_rec.nominated_course_cd);
904 FETCH c_prg_exists INTO c_prg_exists_rec;
905 --create/update program
906 l_msg_at_index := igs_ge_msg_stack.count_msg;
907 SAVEPOINT before_insert_ps_appl;
908 IF c_prg_exists%NOTFOUND THEN
909 IGS_AD_PS_APPL_PKG.Insert_Row (
910 X_Mode => 'R',
911 X_RowId => lv_rowid,
912 X_Person_Id => p_person_id,
913 X_Admission_Appl_Number => p_admission_Appl_number,
914 X_Nominated_Course_Cd => p_applinst_rec.nominated_course_cd,
915 X_Transfer_Course_Cd => p_applinst_rec.transfer_course_cd,
916 X_Basis_For_Admission_Type => p_applinst_rec.basis_for_admission_type,
917 X_Admission_Cd => NULL,
918 X_Course_Rank_Set => NULL,
919 X_Course_Rank_Schedule => NULL,
920 X_Req_For_Reconsideration_Ind => 'N',
921 X_Req_For_Adv_Standing_Ind => p_applinst_rec.req_for_adv_standing_ind,
922 X_Org_Id => l_org_id
923 );
924
925 ELSE
926 IGS_AD_PS_APPL_PKG.update_Row (
927 X_RowId => c_prg_exists_rec.row_id,
928 X_Person_Id => NVL(p_person_id,c_prg_exists_rec.person_id),
929 X_Admission_Appl_Number => NVL(p_admission_Appl_number,c_prg_exists_rec.admission_appl_number),
930 X_Nominated_Course_Cd => NVL(p_applinst_rec.nominated_course_cd,c_prg_exists_rec.nominated_course_cd),
931 X_Transfer_Course_Cd => NVL(p_applinst_rec.transfer_course_cd,c_prg_exists_rec.transfer_course_cd),
932 X_Basis_For_Admission_Type => NVL(p_applinst_rec.basis_for_admission_type,c_prg_exists_rec.basis_for_admission_type),
933 X_Admission_Cd => c_prg_exists_rec.admission_cd,
934 X_Course_Rank_Set => c_prg_exists_rec.Course_Rank_Set,
935 X_Course_Rank_Schedule => c_prg_exists_rec.Course_Rank_Schedule,
936 X_Req_For_Reconsideration_Ind => c_prg_exists_rec.req_for_reconsideration_ind,
937 X_Req_For_Adv_Standing_Ind => NVL(p_applinst_rec.req_for_adv_standing_ind,c_prg_exists_rec.req_for_adv_standing_ind),
938 X_Mode => 'R'
939 );
940 END IF;
941 CLOSE c_prg_exists;
942 ---------------------------------------------
943 -- To validate descriptive flexfield columns.
944 ---------------------------------------------
945 IF NOT IGS_AD_IMP_018.validate_desc_flex_40_cols(
946 p_attribute_category => p_applinst_rec.attribute_category,
947 p_attribute1 => p_applinst_rec.attribute1,
948 p_attribute2 => p_applinst_rec.attribute2,
949 p_attribute3 => p_applinst_rec.attribute3,
950 p_attribute4 => p_applinst_rec.attribute4,
951 p_attribute5 => p_applinst_rec.attribute5,
952 p_attribute6 => p_applinst_rec.attribute6,
953 p_attribute7 => p_applinst_rec.attribute7,
954 p_attribute8 => p_applinst_rec.attribute8,
955 p_attribute9 => p_applinst_rec.attribute9,
956 p_attribute10 => p_applinst_rec.attribute10,
957 p_attribute11 => p_applinst_rec.attribute11,
958 p_attribute12 => p_applinst_rec.attribute12,
959 p_attribute13 => p_applinst_rec.attribute13,
960 p_attribute14 => p_applinst_rec.attribute14,
961 p_attribute15 => p_applinst_rec.attribute15,
962 p_attribute16 => p_applinst_rec.attribute16,
963 p_attribute17 => p_applinst_rec.attribute17,
964 p_attribute18 => p_applinst_rec.attribute18,
965 p_attribute19 => p_applinst_rec.attribute19,
966 p_attribute20 => p_applinst_rec.attribute20,
967 p_attribute21 => p_applinst_rec.attribute21,
968 p_attribute22 => p_applinst_rec.attribute22,
969 p_attribute23 => p_applinst_rec.attribute23,
970 p_attribute24 => p_applinst_rec.attribute24,
971 p_attribute25 => p_applinst_rec.attribute25,
972 p_attribute26 => p_applinst_rec.attribute26,
973 p_attribute27 => p_applinst_rec.attribute27,
974 p_attribute28 => p_applinst_rec.attribute28,
975 p_attribute29 => p_applinst_rec.attribute29,
976 p_attribute30 => p_applinst_rec.attribute30,
977 p_attribute31 => p_applinst_rec.attribute31,
978 p_attribute32 => p_applinst_rec.attribute32,
979 p_attribute33 => p_applinst_rec.attribute33,
980 p_attribute34 => p_applinst_rec.attribute34,
981 p_attribute35 => p_applinst_rec.attribute35,
982 p_attribute36 => p_applinst_rec.attribute36,
983 p_attribute37 => p_applinst_rec.attribute37,
984 p_attribute38 => p_applinst_rec.attribute38,
985 p_attribute39 => p_applinst_rec.attribute39,
986 p_attribute40 => p_applinst_rec.attribute40,
987 p_desc_flex_name => 'IGS_AD_APPL_INST_FLEX'
988 ) THEN
989 FND_MESSAGE.set_name('IGS','IGS_AD_INVALID_DESC_FLEX');
990 l_error_text := FND_MESSAGE.get;
991 p_status := '3';
992 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
993 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => 'E322');
994
995 IF p_enable_log = 'Y' THEN
996 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
997 END IF;
998 RETURN;
999 END IF;
1000
1001 --------------------------------
1002 -- Set Funding Source
1003 --------------------------------
1004 l_funding_source := IGS_AD_GEN_005.admp_get_dflt_fs(
1005 p_applinst_rec.nominated_course_cd,
1006 p_applinst_rec.crv_version_number,
1007 v_description);
1008 IF p_applinst_rec.expected_completion_yr IS NULL AND p_applinst_rec.expected_completion_perd IS NULL THEN
1009 FOR c_session_info_rec IN c_session_info(p_person_id,p_admission_Appl_number) LOOP
1010 IF c_session_info_rec.s_admission_process_type NOT IN ('RE-ADMIT', 'TRANSFER') THEN
1011 -- Derive the Program Start date by calling the function ADMP_GET_CRV_STRT_DT
1012
1013 l_course_start_dt := IGS_AD_GEN_005.ADMP_GET_CRV_STRT_DT (
1014 c_session_info_rec.adm_cal_type,
1015 c_session_info_rec.adm_ci_sequence_number);
1016
1017 --Call the procedure ADMP_GET_CRV_COMP_DT with the additional parameters attenance_mode and location_cd. Bug: 2647482
1018 igs_ad_gen_004.admp_get_crv_comp_dt (
1019 p_applinst_rec.nominated_course_cd,
1020 p_applinst_rec.crv_version_number,
1021 c_session_info_rec.acad_cal_type,
1022 p_applinst_rec.attendance_type,
1023 l_course_start_dt,
1024 l_expected_completion_yr,
1025 l_expected_completion_perd,
1026 l_completion_dt,
1027 p_applinst_rec.attendance_mode,
1028 p_applinst_rec.location_cd);
1029 END IF;
1030 END LOOP;
1031 END IF;
1032 IF NVL(p_applinst_rec.apply_for_finaid,'N') = 'N' THEN
1033 l_finaid_apply_date := NULL;
1034 ELSE
1035 l_finaid_apply_date := p_applinst_rec.finaid_apply_date;
1036 END IF;
1037
1038 ---------------------------------------------------
1039 -- Get the next sequence number for the application
1040 ---------------------------------------------------
1041 OPEN c_nxt_acai_seq_num;
1042 FETCH c_nxt_acai_seq_num INTO l_acai_sequence_number;
1043 CLOSE c_nxt_acai_seq_num;
1044 lv_rowid := NULL;
1045 IGS_AD_PS_APPL_INST_PKG.Insert_Row (
1046 X_ROWID => lv_rowid,
1047 X_PERSON_ID => p_Person_Id,
1048 X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
1049 X_NOMINATED_COURSE_CD => p_applinst_rec.nominated_course_cd,
1050 X_SEQUENCE_NUMBER => l_acai_sequence_number,
1051 X_PREDICTED_GPA => NULL,
1052 X_ACADEMIC_INDEX => NULL,
1053 X_ADM_CAL_TYPE => p_appl_rec.adm_cal_type,
1054 X_APP_FILE_LOCATION => NULL,
1055 X_ADM_CI_SEQUENCE_NUMBER => p_appl_rec.adm_ci_sequence_number,
1056 X_COURSE_CD => p_applinst_rec.nominated_course_cd,
1057 X_APP_SOURCE_ID => p_applinst_rec.app_source_id,
1058 X_CRV_VERSION_NUMBER => p_applinst_rec.Crv_Version_Number,
1059 X_WAITLIST_RANK => NULL,
1060 X_LOCATION_CD => p_applinst_rec.Location_Cd,
1061 X_ATTENT_OTHER_INST_CD => NULL,
1062 X_ATTENDANCE_MODE => p_applinst_rec.Attendance_Mode,
1063 X_EDU_GOAL_PRIOR_ENROLL_ID => p_applinst_rec.edu_goal_prior_enroll_id,
1064 X_ATTENDANCE_TYPE => p_applinst_rec.Attendance_Type,
1065 X_DECISION_MAKE_ID => NULL,
1066 X_UNIT_SET_CD => p_applinst_rec.Unit_Set_Cd,
1067 X_DECISION_DATE => NULL,
1068 X_ATTRIBUTE_CATEGORY => p_applinst_rec.attribute_category ,
1069 X_ATTRIBUTE1=> p_applinst_rec.ATTRIBUTE1,
1070 X_ATTRIBUTE2=> p_applinst_rec.ATTRIBUTE2,
1071 X_ATTRIBUTE3=> p_applinst_rec.ATTRIBUTE3,
1072 X_ATTRIBUTE4=>p_applinst_rec.ATTRIBUTE4,
1073 X_ATTRIBUTE5=>p_applinst_rec.ATTRIBUTE5,
1074 X_ATTRIBUTE6=>p_applinst_rec.ATTRIBUTE6,
1075 X_ATTRIBUTE7=>p_applinst_rec.ATTRIBUTE7,
1076 X_ATTRIBUTE8=>p_applinst_rec.ATTRIBUTE8,
1077 X_ATTRIBUTE9=>p_applinst_rec.ATTRIBUTE9,
1078 X_ATTRIBUTE10=>p_applinst_rec.ATTRIBUTE10,
1079 X_ATTRIBUTE11=>p_applinst_rec.ATTRIBUTE11,
1080 X_ATTRIBUTE12=>p_applinst_rec.ATTRIBUTE12,
1081 X_ATTRIBUTE13=>p_applinst_rec.ATTRIBUTE13,
1082 X_ATTRIBUTE14=>p_applinst_rec.ATTRIBUTE14,
1083 X_ATTRIBUTE15=>p_applinst_rec.ATTRIBUTE15,
1084 X_ATTRIBUTE16=>p_applinst_rec.ATTRIBUTE16,
1085 X_ATTRIBUTE17=>p_applinst_rec.ATTRIBUTE17,
1086 X_ATTRIBUTE18=>p_applinst_rec.ATTRIBUTE18,
1087 X_ATTRIBUTE19=>p_applinst_rec.ATTRIBUTE19,
1088 X_ATTRIBUTE20=>p_applinst_rec.ATTRIBUTE20,
1089 X_WAITLIST_STATUS => NULL,
1090 X_ATTRIBUTE21=>p_applinst_rec.ATTRIBUTE21,
1091 X_ATTRIBUTE22=>p_applinst_rec.ATTRIBUTE22,
1092 X_ATTRIBUTE23=>p_applinst_rec.ATTRIBUTE23,
1093 X_ATTRIBUTE24=>p_applinst_rec.ATTRIBUTE24,
1094 X_ATTRIBUTE25=>p_applinst_rec.ATTRIBUTE25,
1095 X_ATTRIBUTE26=>p_applinst_rec.ATTRIBUTE26,
1096 X_ATTRIBUTE27=>p_applinst_rec.ATTRIBUTE27,
1097 X_ATTRIBUTE28=>p_applinst_rec.ATTRIBUTE28,
1098 X_ATTRIBUTE29=>p_applinst_rec.ATTRIBUTE29,
1099 X_ATTRIBUTE30=>p_applinst_rec.ATTRIBUTE30,
1100 X_ATTRIBUTE31=>p_applinst_rec.ATTRIBUTE31,
1101 X_ATTRIBUTE32=>p_applinst_rec.ATTRIBUTE32,
1102 X_ATTRIBUTE33=>p_applinst_rec.ATTRIBUTE33,
1103 X_ATTRIBUTE34=>p_applinst_rec.ATTRIBUTE34,
1104 X_ATTRIBUTE35=>p_applinst_rec.ATTRIBUTE35,
1105 X_ATTRIBUTE36=>p_applinst_rec.ATTRIBUTE36,
1106 X_ATTRIBUTE37=>p_applinst_rec.ATTRIBUTE37,
1107 X_ATTRIBUTE38=>p_applinst_rec.ATTRIBUTE38,
1108 X_ATTRIBUTE39=>p_applinst_rec.ATTRIBUTE39,
1109 X_ATTRIBUTE40=>p_applinst_rec.ATTRIBUTE40,
1110 X_SS_APPLICATION_ID=> NULL,
1111 X_SS_PWD=> NULL,
1112 X_DECISION_REASON_ID=> NULL,
1113 X_US_VERSION_NUMBER => p_applinst_rec.Us_Version_Number,
1114 X_DECISION_NOTES=> NULL,
1115 X_PENDING_REASON_ID=> NULL,
1116 X_PREFERENCE_NUMBER => p_applinst_rec.Preference_Number,
1117 X_ADM_DOC_STATUS=> l_Adm_Doc_Status,
1118 X_ADM_ENTRY_QUAL_STATUS=> l_Adm_Entry_Qual_Status,
1119 X_DEFICIENCY_IN_PREP=> NULL,
1120 X_LATE_ADM_FEE_STATUS => l_Late_Adm_Fee_Status,
1121 X_SPL_CONSIDER_COMMENTS=> NULL,
1122 X_APPLY_FOR_FINAID => p_applinst_rec.apply_for_finaid,
1123 X_FINAID_APPLY_DATE=> l_finaid_apply_date,
1124 X_ADM_OUTCOME_STATUS => l_adm_pending_outcome_status,
1125 X_ADM_OTCM_STAT_AUTH_PER_ID => NULL,
1126 X_ADM_OUTCOME_STATUS_AUTH_DT => NULL,
1127 X_ADM_OUTCOME_STATUS_REASON => NULL,
1128 X_OFFER_DT => NULL,
1129 X_OFFER_RESPONSE_DT => NULL,
1130 X_PRPSD_COMMENCEMENT_DT => NULL,
1131 X_ADM_CNDTNL_OFFER_STATUS => l_adm_cndtnl_offer_status,
1132 X_CNDTNL_OFFER_SATISFIED_DT => NULL,
1133 X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
1134 X_ADM_OFFER_RESP_STATUS => l_adm_offer_resp_status,
1135 X_ACTUAL_RESPONSE_DT => NULL,
1136 X_ADM_OFFER_DFRMNT_STATUS => l_adm_offer_dfrmnt_status,
1137 X_DEFERRED_ADM_CAL_TYPE => NULL,
1138 X_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
1139 X_DEFERRED_TRACKING_ID => NULL,
1140 X_ASS_RANK => p_applinst_rec.ass_rank,
1141 X_SECONDARY_ASS_RANK => p_applinst_rec.secondary_ass_rank,
1142 X_INTR_ACCEPT_ADVICE_NUM => p_applinst_rec.intrntnl_acceptance_advice_num,
1143 X_ASS_TRACKING_ID => p_applinst_rec.ass_tracking_id,
1144 X_FEE_CAT=> NVL(p_applinst_rec.Fee_Cat,l_fee_cat),
1145 X_HECS_PAYMENT_OPTION => NVL(p_applinst_rec.Hecs_Payment_Option, l_Hecs_Payment_Option),
1146 X_EXPECTED_COMPLETION_YR => NVL(p_applinst_rec.expected_completion_yr, l_expected_completion_yr),
1147 X_EXPECTED_COMPLETION_PERD => NVL(p_applinst_rec.expected_completion_perd, l_expected_completion_perd),
1148 X_CORRESPONDENCE_CAT => NULL,
1149 X_ENROLMENT_CAT => NVL(p_applinst_rec.Enrolment_Cat,l_Enrolment_Cat),
1150 X_FUNDING_SOURCE => NVL(p_applinst_rec.funding_source,l_funding_source),
1151 X_APPLICANT_ACPTNCE_CNDTN => NULL,
1152 X_CNDTNL_OFFER_CNDTN => NULL,
1153 X_AUTHORIZED_DT => NULL,
1154 X_AUTHORIZING_PERS_ID => NULL,
1155 X_IDX_CALC_DATE => NULL,
1156 X_MODE =>'R',
1157 X_FUT_ACAD_CAL_TYPE => NULL , -- Bug # 2217104
1158 X_FUT_ACAD_CI_SEQUENCE_NUMBER => NULL ,-- Bug # 2217104
1159 X_FUT_ADM_CAL_TYPE => NULL , -- Bug # 2217104
1160 X_FUT_ADM_CI_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1161 X_PREV_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
1162 X_PREV_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1163 X_FUT_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
1164 X_FUT_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1165 X_DEF_ACAD_CAL_TYPE => NULL, -- Bug 2395510
1166 X_DEF_ACAD_CI_SEQUENCE_NUM => NULL,-- Bug 2395510
1167 X_DEF_PREV_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
1168 X_DEF_PREV_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
1169 X_DEF_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
1170 X_DEF_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
1171 X_Org_Id => l_org_id,
1172 X_ENTRY_STATUS => p_applinst_rec.entry_status,
1173 X_ENTRY_LEVEL => p_applinst_rec.entry_level,
1174 X_SCH_APL_TO_ID => p_applinst_rec.sch_apl_to_id);
1175
1176 update_person_type(p_sequence_number=> l_acai_sequence_number
1177 ,p_nominated_course_cd => p_applinst_rec.nominated_course_cd
1178 ,p_person_id => p_person_id
1179 ,p_adm_appl_number => p_admission_Appl_number);
1180 update_appl_inst(p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '1',
1181 p_error_text => NULL, p_sequence_number => l_acai_sequence_number, p_error_code => NULL,
1182 p_admission_appl_number => p_admission_Appl_number,
1183 p_match_ind => NVL(p_applinst_rec.match_ind , cst_mi_val_11) );
1184
1185 p_Status := '1';
1186
1187 /* Added the raise event as part of Bug 3391593 */
1188 IF l_acai_sequence_number IS NOT NULL THEN
1189 -- Call The assign requirment procedure and admission tracking completion Procedure *** Autoadmit
1190 -- This single process will call both the procedure
1191 l_error_code1 := NULL;
1192 IGS_AD_GEN_014.auto_assign_requirement(
1193 p_person_id => p_Person_Id,
1194 p_admission_appl_number => p_Admission_Appl_Number,
1195 p_course_cd => p_applinst_rec.nominated_course_cd,
1196 p_sequence_number => l_acai_sequence_number,
1197 p_called_from => 'IM',
1198 p_error_text => l_error_text1,
1199 p_error_code => l_error_code1
1200 );
1201 IF l_error_code1 IS NOT NULL THEN
1202 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_ASSIGNREQ_ERRM');
1203 FND_MESSAGE.SET_TOKEN ('PERSON_ID', p_person_id);
1204 FND_MESSAGE.SET_TOKEN ('ADM_APPL_NUM', p_admission_Appl_number);
1205 FND_MESSAGE.SET_TOKEN ('NOMINATED_COURSE_CODE', p_applinst_rec.nominated_course_cd);
1206 FND_MESSAGE.SET_TOKEN ('SEQUENCE_NUMBER', l_acai_sequence_number);
1207 FND_MESSAGE.SET_TOKEN ('ERROR_MESSAGE', l_error_text1);
1208 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1209
1210 END IF;
1211
1212 --Assign Qualification Types to application instance being submitted **** Auto admit
1213 IGS_AD_GEN_014.assign_qual_type(p_person_id => p_Person_Id,
1214 p_admission_appl_number => p_Admission_Appl_Number,
1215 p_course_cd => p_applinst_rec.nominated_course_cd,
1216 p_sequence_number => l_acai_sequence_number
1217 );
1218 -- Application Instance has been successfully created and the business event needs to be raised
1219 igs_ad_wf_001.wf_raise_event(p_person_id => p_person_id,
1220 p_raised_for => 'IAC'
1221 );
1222 END IF;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 ROLLBACK TO before_insert_ps_appl;
1227 p_status := '3';
1228 igs_ad_gen_016.extract_msg_from_stack (
1229 p_msg_at_index => l_msg_at_index,
1230 p_return_status => l_return_status,
1231 p_msg_count => l_msg_count,
1232 p_msg_data => l_msg_data,
1233 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1234 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1235 l_error_text := l_msg_data;
1236 l_error_code := 'E322';
1237
1238 IF p_enable_log = 'Y' THEN
1239 igs_ad_imp_001.logerrormessage(p_applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
1240 END IF;
1241 ELSE
1242 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
1243 l_error_code := 'E518';
1244 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1245
1246 l_label :='igs.plsql.igs_ad_imp_004.create_single_appl_instance.exception ';
1247 l_debug_str := 'Failed Creating Application instance'||l_msg_data;
1248 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1249 fnd_message.set_token('INTERFACE_ID',p_applinst_rec.interface_ps_appl_inst_id);
1250 fnd_message.set_token('ERROR_CD','E322');
1251
1252 l_debug_str := fnd_message.get;
1253
1254 fnd_log.string_with_context( fnd_log.level_exception,
1255 l_label,
1256 l_debug_str, NULL,
1257 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1258 END IF;
1259
1260 END IF;
1261 update_appl_inst(p_admission_appl_number => NULL, p_match_ind => NULL, p_appl_inst_id => p_applinst_rec.interface_ps_appl_inst_id, p_status => '3',
1262 p_error_text => l_error_text, p_sequence_number => NULL, p_error_code => l_error_code);
1263
1264 END create_single_appl_instance;
1265
1266 PROCEDURE Process_application_instance(p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE
1267 ) IS
1268
1269 l_Status VARCHAR2(1);
1270
1271 l_msg_at_index NUMBER ;
1272 l_return_status VARCHAR2(1);
1273 l_msg_count NUMBER ;
1274 l_msg_data VARCHAR2(2000);
1275 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1276 l_error_code VARCHAR2(4);
1277 l_error_text VARCHAR2(2000);
1278 l_finaid_apply_date igs_ad_ps_appl_inst.finaid_apply_date%TYPE;
1279
1280 BEGIN
1281 --For all application instance interface record
1282 -- IF update_seq_num is NOT NULL
1283 -- update the instance
1284 -- ELSE create_application_instance
1285 --If given invalid update transcript ID then error out.
1286 UPDATE igs_ad_ps_appl_inst_int aplinst
1287 SET
1288 status = '3'
1289 , error_code = 'E706'
1290 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E706', 8405)
1291 WHERE interface_run_id = p_interface_run_id
1292 AND status ='2'
1293 AND update_adm_seq_number IS NOT NULL
1294 AND NOT EXISTS ( SELECT 1 FROM igs_ad_ps_appl_inst
1295 WHERE person_id = aplinst.person_id
1296 AND admission_Appl_number = aplinst.admission_Appl_number
1297 AND nominated_course_cd = aplinst.nominated_course_cd
1298 AND sequence_number = aplinst.update_adm_seq_number
1299 ) ;
1300 COMMIT;
1301 -- 1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
1302 IF p_rule IN ('E', 'I') THEN
1303 UPDATE igs_ad_ps_appl_inst_int
1304 SET
1305 status = '3'
1306 , error_code = 'E700'
1307 ,error_Text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1308 WHERE interface_run_id = p_interface_run_id
1309 AND status = '2'
1310 AND NVL (match_ind, '15') <> '15';
1311 END IF;
1312 COMMIT;
1313
1314 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1315 IF p_rule = 'R' THEN
1316 UPDATE igs_ad_ps_appl_inst_int
1317 SET
1318 status = '1', error_code = NULL
1319 WHERE interface_run_id = p_interface_run_id
1320 AND status = '2'
1321 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
1322 END IF;
1323
1324 IF p_rule = 'E' THEN
1325 UPDATE igs_ad_ps_appl_inst_int
1326 SET
1327 status = '1'
1328 , match_ind = '19'
1329 , SEQUENCE_NUMBER = update_adm_seq_number
1330 WHERE interface_run_id = p_interface_run_id
1331 AND status = '2'
1332 AND update_adm_seq_number IS NOT NULL ;
1333 COMMIT;
1334 END IF;
1335 l_admission_application_type := NULL;
1336 FOR applinst_rec IN applinst_cur(NULL) LOOP
1337 IF applinst_rec.dmlmode = cst_insert THEN
1338 FOR p_appl_rec IN appl_cur_upd(applinst_rec.interface_appl_id) LOOP
1339 IF l_admission_application_type IS NULL
1340 OR p_appl_rec.admission_application_type <> l_admission_application_type THEN
1341 populate_apc_columns(p_appl_rec.admission_application_type);
1342 END IF;
1343 create_single_appl_instance(applinst_rec, applinst_rec.person_id, applinst_rec.admission_Appl_number, p_appl_rec, l_status);
1344 END LOOP;
1345 ELSIF applinst_rec.dmlmode = cst_update THEN
1346 BEGIN
1347 FOR p_appl_rec IN appl_cur_upd(applinst_rec.interface_appl_id) LOOP
1348 IF l_admission_application_type IS NULL
1349 OR p_appl_rec.admission_application_type <> l_admission_application_type THEN
1350 populate_apc_columns(p_appl_rec.admission_application_type);
1351 END IF;
1352 IF validate_apc_steps(applinst_rec) THEN
1353 OPEN dup_applinst_cur(applinst_rec, applinst_rec.person_id, applinst_rec.admission_appl_number);
1354 FETCH dup_applinst_cur INTO dup_applinst_rec;
1355 CLOSE dup_applinst_cur;
1356
1357 IF NVL(applinst_rec.apply_for_finaid,'N') = 'N' THEN
1358 l_finaid_apply_date := NULL;
1359 ELSE
1360 l_finaid_apply_date := applinst_rec.finaid_apply_date;
1361 END IF;
1362
1363 l_msg_at_index := igs_ge_msg_stack.count_msg;
1364 SAVEPOINT before_update_appl_inst;
1365 igs_ad_ps_appl_inst_pkg.update_row(
1366 x_rowid => dup_applinst_rec.rowid,
1367 x_person_id =>dup_applinst_rec.person_id,
1368 x_admission_appl_number => dup_applinst_rec.admission_appl_number,
1369 x_nominated_course_cd=> dup_applinst_rec.nominated_course_cd,
1370 x_sequence_number=> dup_applinst_rec.sequence_number,
1371 x_predicted_gpa=> dup_applinst_rec.predicted_gpa,
1372 x_academic_index=>dup_applinst_rec.academic_index,
1373 x_adm_cal_type => dup_applinst_rec.adm_cal_type,
1374 x_app_file_location =>dup_applinst_rec.app_file_location,
1375 x_adm_ci_sequence_number=>dup_applinst_rec.adm_ci_sequence_number,
1376 x_course_cd=>dup_applinst_rec.course_cd,
1377 x_app_source_id=> NVL(applinst_rec.app_source_id,dup_applinst_rec.app_source_id),
1378 x_crv_version_number=>NVL(applinst_rec.crv_version_number, dup_applinst_rec.crv_version_number),
1379 x_waitlist_rank=>dup_applinst_rec.waitlist_rank,
1380 x_waitlist_status=>dup_applinst_rec.waitlist_status,
1381 x_location_cd=> NVL(applinst_rec.location_cd,dup_applinst_rec.location_cd),
1382 x_attent_other_inst_cd=>dup_applinst_rec.attent_other_inst_cd,
1383 x_attendance_mode=>NVL(applinst_rec.attendance_mode,dup_applinst_rec.attendance_mode),
1384 x_edu_goal_prior_enroll_id=> NVL(applinst_rec.edu_goal_prior_enroll_id,dup_applinst_rec.edu_goal_prior_enroll_id),
1385 x_attendance_type=>NVL(applinst_rec.attendance_type,dup_applinst_rec.attendance_type),
1386 x_decision_make_id=>dup_applinst_rec.decision_make_id,
1387 x_unit_set_cd=>NVL(applinst_rec.unit_set_cd,dup_applinst_rec.unit_set_cd),
1388 x_decision_date=>dup_applinst_rec.decision_date,
1389 x_attribute_category=>NVL(applinst_rec.attribute_category,dup_applinst_rec.attribute_category),
1390 x_attribute1=>NVL(applinst_rec.attribute1,dup_applinst_rec.attribute1),
1391 x_attribute2=>NVL(applinst_rec.attribute2,dup_applinst_rec.attribute2),
1392 x_attribute3=>NVL(applinst_rec.attribute3,dup_applinst_rec.attribute3),
1393 x_attribute4=>NVL(applinst_rec.attribute4,dup_applinst_rec.attribute4),
1394 x_attribute5=>NVL(applinst_rec.attribute5,dup_applinst_rec.attribute5),
1395 x_attribute6=>NVL(applinst_rec.attribute6,dup_applinst_rec.attribute6),
1396 x_attribute7=>NVL(applinst_rec.attribute7,dup_applinst_rec.attribute7),
1397 x_attribute8=>NVL(applinst_rec.attribute8,dup_applinst_rec.attribute8),
1398 x_attribute9=>NVL(applinst_rec.attribute9,dup_applinst_rec.attribute9),
1399 x_attribute10=>NVL(applinst_rec.attribute10,dup_applinst_rec.attribute10),
1400 x_attribute11=>NVL(applinst_rec.attribute11,dup_applinst_rec.attribute11),
1401 x_attribute12=>NVL(applinst_rec.attribute12,dup_applinst_rec.attribute12),
1402 x_attribute13=>NVL(applinst_rec.attribute13,dup_applinst_rec.attribute13),
1403 x_attribute14=>NVL(applinst_rec.attribute14,dup_applinst_rec.attribute14),
1404 x_attribute15=>NVL(applinst_rec.attribute15,dup_applinst_rec.attribute15),
1405 x_attribute16=>NVL(applinst_rec.attribute16,dup_applinst_rec.attribute16),
1406 x_attribute17=>NVL(applinst_rec.attribute17,dup_applinst_rec.attribute17),
1407 x_attribute18=>NVL(applinst_rec.attribute18,dup_applinst_rec.attribute18),
1408 x_attribute19=>NVL(applinst_rec.attribute19,dup_applinst_rec.attribute19),
1409 x_attribute20=>NVL(applinst_rec.attribute20,dup_applinst_rec.attribute20),
1410 x_decision_reason_id=>dup_applinst_rec.decision_reason_id,
1411 x_us_version_number=>NVL(applinst_rec.us_version_number,dup_applinst_rec.us_version_number),
1412 x_decision_notes=>dup_applinst_rec.decision_notes,
1413 x_pending_reason_id=>dup_applinst_rec.pending_reason_id,
1414 x_preference_number=>NVL(applinst_rec.preference_number,dup_applinst_rec.preference_number),
1415 x_adm_doc_status=>dup_applinst_rec.adm_doc_status,
1416 x_adm_entry_qual_status=>dup_applinst_rec.adm_entry_qual_status,
1417 x_deficiency_in_prep=>dup_applinst_rec.deficiency_in_prep,
1418 x_late_adm_fee_status=>dup_applinst_rec.late_adm_fee_status,
1419 x_spl_consider_comments=>dup_applinst_rec.spl_consider_comments,
1420 x_apply_for_finaid=> NVL(applinst_rec.apply_for_finaid,dup_applinst_rec.apply_for_finaid),
1421 x_finaid_apply_date=> l_finaid_apply_date,
1422 x_adm_outcome_status=>dup_applinst_rec.adm_outcome_status,
1423 x_adm_otcm_stat_auth_per_id=>dup_applinst_rec.adm_otcm_status_auth_person_id,
1424 x_adm_outcome_status_auth_dt=>dup_applinst_rec.adm_outcome_status_auth_dt,
1425 x_adm_outcome_status_reason=> dup_applinst_rec.adm_outcome_status_reason,
1426 x_offer_dt=>dup_applinst_rec.offer_dt,
1427 x_offer_response_dt=>dup_applinst_rec.offer_response_dt,
1428 x_prpsd_commencement_dt=>dup_applinst_rec.prpsd_commencement_dt,
1429 x_adm_cndtnl_offer_status=>dup_applinst_rec.adm_cndtnl_offer_status,
1430 x_cndtnl_offer_satisfied_dt=> dup_applinst_rec.cndtnl_offer_satisfied_dt,
1431 x_cndnl_ofr_must_be_stsfd_ind=>dup_applinst_rec.cndtnl_offer_must_be_stsfd_ind,
1432 x_adm_offer_resp_status=> dup_applinst_rec.adm_offer_resp_status,
1433 x_actual_response_dt=> dup_applinst_rec.actual_response_dt,
1434 x_adm_offer_dfrmnt_status=> dup_applinst_rec.adm_offer_dfrmnt_status,
1435 x_deferred_adm_cal_type=> dup_applinst_rec.deferred_adm_cal_type,
1436 x_deferred_adm_ci_sequence_num=> dup_applinst_rec.deferred_adm_ci_sequence_num,
1437 x_deferred_tracking_id=> dup_applinst_rec.deferred_tracking_id,
1438 x_ass_rank=> NVL(applinst_rec.ass_rank,dup_applinst_rec.ass_rank),
1439 x_secondary_ass_rank=>NVL(applinst_rec.secondary_ass_rank,dup_applinst_rec.secondary_ass_rank),
1440 x_intr_accept_advice_num=>NVL(applinst_rec.intrntnl_acceptance_advice_num,dup_applinst_rec.intrntnl_acceptance_advice_num),
1441 x_ass_tracking_id=> NVL(applinst_rec.ass_tracking_id,dup_applinst_rec.ass_tracking_id),
1442 x_fee_cat=>NVL(applinst_rec.fee_cat,dup_applinst_rec.fee_cat),
1443 x_hecs_payment_option=>NVL(applinst_rec.hecs_payment_option, dup_applinst_rec.hecs_payment_option),
1444 x_expected_completion_yr=>NVL(applinst_rec.expected_completion_yr, dup_applinst_rec.expected_completion_yr),
1445 x_expected_completion_perd=>NVL(applinst_rec.expected_completion_perd, dup_applinst_rec.expected_completion_perd),
1446 x_correspondence_cat=> dup_applinst_rec.correspondence_cat,
1447 x_enrolment_cat=>NVL(applinst_rec.enrolment_cat,dup_applinst_rec.enrolment_cat),
1448 x_funding_source=> NVL(applinst_rec.funding_source, dup_applinst_rec.funding_source),
1449 x_applicant_acptnce_cndtn=> dup_applinst_rec.applicant_acptnce_cndtn,
1450 x_cndtnl_offer_cndtn=> dup_applinst_rec.cndtnl_offer_cndtn,
1451 x_ss_application_id=>dup_applinst_rec.ss_application_id,
1452 x_ss_pwd=>dup_applinst_rec.ss_pwd, --Bug Enh No : 1891835 Added two columns
1453 x_authorized_dt => dup_applinst_rec.authorized_dt,
1454 --Bug Enh No : 1891835 Added two columns
1455 x_authorizing_pers_id => dup_applinst_rec.authorizing_pers_id,
1456 -- Enh Bug#1964478 added three parameters
1457 x_entry_status => NVL(applinst_rec.entry_status, dup_applinst_rec.entry_status),
1458 x_entry_level => NVL(applinst_rec.entry_level, dup_applinst_rec.entry_level),
1459 x_sch_apl_to_id=> NVL(applinst_rec.sch_apl_to_id, dup_applinst_rec.sch_apl_to_id),
1460 x_idx_calc_date => dup_applinst_rec.idx_calc_date,
1461 x_fut_acad_cal_type => dup_applinst_rec.future_acad_cal_type, -- bug # 2217104
1462 x_fut_acad_ci_sequence_number => dup_applinst_rec.future_acad_ci_sequence_number,-- bug # 2217104
1463 x_fut_adm_cal_type => dup_applinst_rec.future_adm_cal_type, -- bug # 2217104
1464 x_fut_adm_ci_sequence_number => dup_applinst_rec.future_adm_ci_sequence_number, -- bug # 2217104
1465 x_prev_term_adm_appl_number => dup_applinst_rec.previous_term_adm_appl_number, -- bug # 2217104
1466 x_prev_term_sequence_number => dup_applinst_rec.previous_term_sequence_number, -- bug # 2217104
1467 x_fut_term_adm_appl_number => dup_applinst_rec.future_term_adm_appl_number, -- bug # 2217104
1468 x_fut_term_sequence_number => dup_applinst_rec.future_term_sequence_number, -- bug # 2217104
1469 x_def_acad_cal_type => dup_applinst_rec.def_acad_cal_type, --bug 2395510
1470 x_def_acad_ci_sequence_num => dup_applinst_rec.def_acad_ci_sequence_num, --bug 2395510
1471 x_def_prev_term_adm_appl_num => dup_applinst_rec.def_prev_term_adm_appl_num,--bug 2395510
1472 x_def_prev_appl_sequence_num => dup_applinst_rec.def_prev_appl_sequence_num,--bug 2395510
1473 x_def_term_adm_appl_num => dup_applinst_rec.def_term_adm_appl_num,--bug 2395510
1474 x_def_appl_sequence_num => dup_applinst_rec.def_appl_sequence_num,--bug 2395510
1475 x_mode=>'R',
1476 x_appl_inst_status =>dup_applinst_rec.appl_inst_status,
1477 x_ais_reason =>dup_applinst_rec.ais_reason
1478 );
1479
1480 UPDATE igs_ad_ps_appl_inst_int
1481 SET
1482 status = '1',
1483 sequence_number = dup_applinst_rec.sequence_number
1484 WHERE interface_ps_appl_inst_id = applinst_rec.interface_ps_appl_inst_id;
1485 END IF; --Validate_Apc_steps
1486
1487 END LOOP;
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 ROLLBACK TO before_update_appl_inst;
1491 igs_ad_gen_016.extract_msg_from_stack (
1492 p_msg_at_index => l_msg_at_index,
1493 p_return_status => l_return_status,
1494 p_msg_count => l_msg_count,
1495 p_msg_data => l_msg_data,
1496 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1497 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1498 l_error_text := l_msg_data;
1499 l_error_code := NULL;
1500
1501 IF p_enable_log = 'Y' THEN
1502 igs_ad_imp_001.logerrormessage(applinst_rec.interface_ps_appl_inst_id,l_error_text,'IGS_AD_APPL_INST_INT');
1503 END IF;
1504 ELSE
1505 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E518', 8405);
1506 l_error_code := 'E518';
1507 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1508 l_label :='igs.plsql.igs_ad_imp_004.Process_application_instance.exception '||l_msg_data;
1509
1510 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1511 fnd_message.set_token('INTERFACE_ID',applinst_rec.interface_ps_appl_inst_id);
1512 fnd_message.set_token('ERROR_CD','E322');
1513
1514 l_debug_str := fnd_message.get;
1515
1516 fnd_log.string_with_context( fnd_log.level_exception,
1517 l_label,
1518 l_debug_str, NULL,
1519 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1520 END IF;
1521 END IF;
1522 UPDATE igs_ad_ps_appl_inst_int
1523 SET
1524 status = '3',
1525 error_code = l_error_code,
1526 error_text = l_error_text
1527 WHERE interface_ps_appl_inst_id = applinst_rec.interface_ps_appl_inst_id;
1528
1529 END;
1530 END IF;
1531 END LOOP;
1532
1533 IF p_rule = 'R' THEN
1534 UPDATE igs_ad_ps_appl_inst_int applinst
1535 SET
1536 status = '1'
1537 , match_ind = '23'
1538 , sequence_number = applinst.update_adm_seq_number
1539 WHERE EXISTS ( SELECT 'X'
1540 FROM igs_ad_ps_appl_inst ai
1541 WHERE ai.person_id = applinst.person_id AND
1542 ai.nominated_course_cd = applinst.nominated_course_cd AND
1543 ai.admission_appl_number = applinst.admission_Appl_number AND
1544 ai.sequence_number = applinst.update_adm_seq_number AND
1545 NVL(ai.crv_version_number,-99)=
1546 NVL(NVL(applinst.crv_version_number, ai.crv_version_number),-99) AND
1547 NVL(ai.location_cd,'*') =
1548 NVL(NVL(applinst.location_cd, ai.location_cd),'*') AND
1549 NVL(ai.attendance_mode,'*')=
1550 NVL(NVL(applinst.attendance_mode, ai.attendance_mode), '*') AND
1551 NVL(ai.edu_goal_prior_enroll_id,-99) =
1552 NVL(NVL(applinst.edu_goal_prior_enroll_id, ai.edu_goal_prior_enroll_id), -99) AND
1553 NVL(ai.app_source_id,-99) =
1554 NVL(NVL(applinst.app_source_id, ai.app_source_id), -99) AND
1555 NVL(ai.attendance_type,'*') =
1556 NVL(NVL(applinst.attendance_type, ai.attendance_type), '*') AND
1557 NVL(ai.unit_set_cd, '*') =
1558 NVL(NVL(applinst.unit_set_cd, ai.unit_set_cd ), '*') AND
1559 NVL(ai.us_version_number,-99)=
1560 NVL(NVL(applinst.us_version_number, ai.us_version_number), -99) AND
1561 NVL(ai.preference_number,-99)=
1562 NVL(NVL(applinst.preference_number, ai.preference_number), -99) AND
1563 NVL(ai.apply_for_finaid,'*') =
1564 NVL(NVL(applinst.apply_for_finaid, ai.apply_for_finaid), '*') AND
1565 NVL(ai.finaid_apply_date,sysdate) =
1566 NVL(NVL(applinst.finaid_apply_date, ai.finaid_apply_date), sysdate) AND
1567 NVL(ai.attribute_category,'*')=
1568 NVL(NVL(applinst.attribute_category, ai.attribute_category), '*') AND
1569 NVL(ai.attribute1,'*') = NVL(NVL(applinst.attribute1, ai.attribute1), '*') AND
1570 NVL(ai.attribute2,'*') = NVL(NVL(applinst.attribute2, ai.attribute2), '*') AND
1571 NVL(ai.attribute3,'*') = NVL(NVL(applinst.attribute3, ai.attribute3), '*') AND
1572 NVL(ai.attribute4,'*') = NVL(NVL(applinst.attribute4, ai.attribute4), '*') AND
1573 NVL(ai.attribute5,'*') = NVL(NVL(applinst.attribute5, ai.attribute5), '*') AND
1574 NVL(ai.attribute6,'*') = NVL(NVL(applinst.attribute6, ai.attribute6), '*') AND
1575 NVL(ai.attribute7,'*') = NVL(NVL(applinst.attribute7, ai.attribute7), '*') AND
1576 NVL(ai.attribute8,'*') = NVL(NVL(applinst.attribute8, ai.attribute8), '*') AND
1577 NVL(ai.attribute9,'*') = NVL(NVL(applinst.attribute9, ai.attribute9), '*') AND
1578 NVL(ai.attribute10,'*') = NVL(NVL(applinst.attribute10, ai.attribute10), '*') AND
1579 NVL(ai.attribute11,'*') = NVL(NVL(applinst.attribute11, ai.attribute11), '*') AND
1580 NVL(ai.attribute12,'*') = NVL(NVL(applinst.attribute12, ai.attribute12), '*') AND
1581 NVL(ai.attribute13,'*') = NVL(NVL(applinst.attribute13, ai.attribute13), '*') AND
1582 NVL(ai.attribute14,'*') = NVL(NVL(applinst.attribute14, ai.attribute14), '*') AND
1583 NVL(ai.attribute15,'*') = NVL(NVL(applinst.attribute15, ai.attribute15), '*') AND
1584 NVL(ai.attribute16,'*') = NVL(NVL(applinst.attribute16, ai.attribute16), '*') AND
1585 NVL(ai.attribute17,'*') = NVL(NVL(applinst.attribute17, ai.attribute17), '*') AND
1586 NVL(ai.attribute18,'*') = NVL(NVL(applinst.attribute18, ai.attribute18), '*') AND
1587 NVL(ai.attribute19,'*') = NVL(NVL(applinst.attribute19, ai.attribute19), '*') AND
1588 NVL(ai.attribute20,'*') = NVL(NVL(applinst.attribute20, ai.attribute20), '*')
1589 );
1590 END IF;
1591
1592
1593 IF p_rule = 'R' THEN
1594 UPDATE igs_ad_ps_appl_inst_int
1595 SET
1596 status = '3'
1597 , match_ind = '20'
1598 WHERE interface_run_id = p_interface_run_id
1599 AND status = '2'
1600 AND update_adm_seq_number IS NOT NULL;
1601 END IF;
1602
1603 IF p_rule = 'R' THEN
1604 UPDATE igs_ad_ps_appl_inst_int
1605 SET
1606 status = '3'
1607 , error_code = 'E700'
1608 , error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1609 WHERE interface_run_id = p_interface_run_id
1610 AND status = '2'
1611 AND match_ind IS NOT NULL;
1612 END IF;
1613 COMMIT;
1614 END Process_application_instance;
1615
1616
1617
1618
1619 BEGIN
1620 -------------------------------------------------------------------------------
1621 --------------------- Main procedure prc_appcln--------------------------
1622 --------------------------------------------------------------------------------
1623 --------------------- Initialization of Package Variables--------------------------
1624 l_adm_appl_status := Igs_Ad_Gen_008.ADMP_GET_SYS_AAS('RECEIVED');
1625 l_adm_fee_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AFS('NOT-APPLIC');
1626 l_Late_Adm_Fee_Status := Igs_Ad_Gen_009.ADMP_GET_SYS_AFS('NOT-APPLIC');
1627 l_cndnl_ofr_must_be_stsfd_ind := 'N';
1628 l_adm_pending_outcome_status := IGS_AD_GEN_009.admp_get_sys_aos('PENDING');
1629 l_adm_cndtnl_offer_status := IGS_AD_GEN_009.admp_get_sys_acos('NOT-APPLIC');
1630 l_adm_offer_resp_status := IGS_AD_GEN_009.admp_get_sys_aors('NOT-APPLIC');
1631 l_adm_offer_dfrmnt_status := IGS_AD_GEN_009.admp_get_sys_aods('NOT-APPLIC');
1632 l_admission_application_type := NULL;
1633 l_admission_cat := NULL;
1634 l_s_admission_process_type := NULL;
1635
1636 l_fee_cat := NULL ;
1637 l_enrolment_cat := NULL;
1638 l_hecs_payment_option := NULL;
1639 l_adm_entry_qual_status := NULL;
1640 l_request_id := fnd_global.conc_request_id; -- This is Local Variable
1641 l_prog_label := 'igs.plsql.igs_ad_imp_004.prc_appcln';
1642 l_label := 'igs.plsql.igs_ad_imp_004.prc_appcln.';
1643 l_debug_str := 'igs.plsql.igs_ad_imp_004.prc_appcln.';
1644 l_org_id := igs_ge_gen_003.get_org_id;
1645 --------------------------------------------------------------------------------
1646
1647 l_prog_label := 'igs.plsql.igs_ad_imp_004.prc_appcln.';
1648 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1649
1650 l_label :='igs.plsql.igs_ad_imp_004.prc_appcln.begin';
1651 l_debug_str := 'Process Application begin';
1652
1653 fnd_log.string_with_context( fnd_log.level_procedure,
1654 l_label,
1655 l_debug_str, NULL,
1656 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1657 END IF;
1658
1659 UPDATE igs_ad_apl_int api
1660 SET
1661 status = '3',
1662 error_code = 'E523',
1663 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E523', 8405)
1664 WHERE interface_run_id = p_interface_run_id
1665 AND update_adm_appl_number IS NOT NULL
1666 AND NOT EXISTS ( SELECT 1 FROM IGS_AD_APPL appl_oss
1667 WHERE person_id = api.person_id
1668 AND admission_Appl_number =
1669 NVL(api.update_adm_appl_number, appl_oss.admission_Appl_number)
1670 ) ;
1671 COMMIT;
1672 -- 1. Set STATUS to 3 for interface records with RULE = E or I and MATCH IND is not null and not '15'
1673 IF p_rule IN ('E', 'I') THEN
1674 UPDATE igs_ad_apl_int
1675 SET
1676 status = '3'
1677 , error_code = 'E700'
1678 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405)
1679 WHERE interface_run_id = p_interface_run_id
1680 AND status = '2'
1681 AND NVL (match_ind, '15') <> '15';
1682 END IF;
1683 COMMIT;
1684
1685 -- 2. Set STATUS to 1 for interface records with RULE = R and MATCH IND = 17,18,19,22,23,24,27
1686 IF p_rule = 'R' THEN
1687 UPDATE igs_ad_apl_int
1688 SET
1689 status = '1'
1690 ,error_code = NULL
1691 ,error_text = NULL
1692 WHERE interface_run_id = p_interface_run_id
1693 AND status = '2'
1694 AND match_ind IN ('17', '18', '19', '22', '23', '24', '27');
1695 END IF;
1696 COMMIT;
1697
1698 -- 5. Set STATUS to 1 and MATCH IND to 19 for interface records with RULE = E matching OSS record(s)
1699 IF p_rule = 'E' THEN
1700 UPDATE igs_ad_apl_int api
1701 SET
1702 status = '1'
1703 , match_ind = '19'
1704 , admission_appl_number = update_adm_appl_number
1705 WHERE interface_run_id = p_interface_run_id
1706 AND status = '2'
1707 AND update_adm_appl_number IS NOT NULL;
1708 END IF;
1709 COMMIT;
1710 -- Error out applications that need to be updated but they have legacy data
1711 IF p_legacy_ind = 'Y' THEN
1712 UPDATE igs_ad_apl_int api
1713 SET
1714 status = '3'
1715 ,error_code = 'E677'
1716 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E677', 8405)
1717 WHERE interface_run_id = p_interface_run_id
1718 AND status = '2'
1719 AND update_adm_appl_number IS NOT NULL
1720 AND ( EXISTS (SELECT 1 FROM igs_ad_apphist_int WHERE person_id = api.person_id
1721 AND admission_appl_number = api.update_adm_appl_number )
1722 OR EXISTS (SELECT 1 FROM igs_ad_insthist_int WHERE person_id = api.person_id
1723 AND admission_appl_number = api.update_adm_appl_number )
1724 );
1725 END IF;
1726 COMMIT;
1727
1728 UPDATE igs_ad_apl_int api
1729 SET
1730 status = '3'
1731 ,error_code = 'E176'
1732 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E176', 8405)
1733 WHERE interface_run_id = p_interface_run_id
1734 AND status = '2'
1735 AND NOT EXISTS( SELECT '1'
1736 FROM igs_ad_ss_appl_typ
1737 WHERE admission_application_type = api.admission_application_type);
1738
1739 COMMIT;
1740
1741 /**********************************************************************************
1742 Create / Update the OSS record after validating successfully the interface record
1743 Create
1744 If RULE I (match indicator will be 15 or NULL by now no need to check) and matching system record not found OR
1745 RULE = R and MATCH IND = 16, 25
1746 Update
1747 If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
1748 RULE = R and MATCH IND = 21
1749
1750 Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying the DML operation.
1751 This is done to have one code section for record validation, exception handling and interface table update.
1752 This avoids call to separate PLSQL blocks, tuning performance on stack maintenance during the process.
1753
1754 **********************************************************************************/
1755 FOR appl_rec IN appl_cur
1756 LOOP
1757 IF l_admission_application_type IS NULL
1758 OR appl_rec.admission_application_type <> l_admission_application_type THEN
1759 populate_apc_columns(appl_rec.admission_application_type);
1760 END IF;
1761 IF appl_rec.dmlmode = cst_insert THEN
1762 SAVEPOINT before_create_application;
1763 create_application_details(appl_rec, l_admission_appl_number, l_status_application);
1764 IF l_status_application = 'S' THEN
1765 create_application_instance(appl_rec.interface_appl_id, appl_rec.person_id,
1766 l_admission_appl_number, appl_rec, l_Status_instance);
1767 IF l_Status_instance = 'E' THEN
1768 ROLLBACK to before_create_application;
1769 UPDATE
1770 igs_ad_apl_int
1771 SET
1772 status = '3',
1773 error_code = 'E347'
1774 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
1775 WHERE
1776 interface_appl_id = appl_rec.interface_appl_id;
1777 ELSIF l_Status_instance = 'W' THEN
1778 UPDATE
1779 igs_ad_apl_int
1780 SET
1781 status = '4',
1782 error_code = 'E347'
1783 ,error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
1784 WHERE
1785 interface_appl_id = appl_rec.interface_appl_id;
1786 ELSE
1787 UPDATE
1788 igs_ad_apl_int
1789 SET
1790 status = cst_s_val_1,
1791 admission_Appl_number = l_admission_appl_number
1792 WHERE
1793 interface_appl_id = appl_rec.interface_appl_id;
1794 END IF;
1795 END IF;
1796
1797
1798 ELSIF appl_rec.dmlmode = cst_update THEN
1799 OPEN c_dup_cur(appl_rec);
1800 FETCH c_dup_cur INTO dup_cur_rec;
1801 CLOSE c_dup_cur;
1802 update_application_details(appl_rec, dup_cur_rec);
1803 END IF;
1804 l_processed_records := l_processed_records + 1;
1805 IF l_processed_records = 50 THEN
1806 COMMIT;
1807 l_processed_records := 0;
1808 END IF;
1809
1810 END LOOP;
1811 IF l_processed_records < 100 AND l_processed_records > 0 THEN
1812 COMMIT;
1813 END IF;
1814 /*Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching OSS record(s) in
1815 ALL updateable column values, if column nullification is not allowed then the 2 DECODE should be replaced by a single NVL*/
1816 IF p_rule = 'R' THEN
1817 UPDATE igs_ad_apl_int api
1818 SET
1819 status = '1'
1820 , match_ind = '23'
1821 , admission_appl_number = api.update_adm_appl_number
1822 WHERE interface_run_id = p_interface_run_id
1823 AND status = '2'
1824 AND NVL (match_ind, '15') = '15'
1825 AND EXISTS ( SELECT 1 FROM IGS_AD_APPL appl_oss
1826 WHERE person_id = api.person_id
1827 AND admission_appl_number = api.update_adm_appl_number
1828 AND TRUNC(appl_dt) = TRUNC(api.appl_dt)
1829 AND tac_appl_ind = api.tac_appl_ind
1830 AND NVL(spcl_grp_1, -1) = NVL( NVL(api.spcl_grp_1, appl_oss.spcl_grp_1), -1)
1831 AND NVL(spcl_grp_2,-1) = NVL( NVL(api.spcl_grp_2, appl_oss.spcl_grp_2), -1)
1832 AND NVL(common_app, '*') = NVL( NVL(api.common_app,appl_oss.common_app),'*')
1833 AND NVL(choice_number, -1) = NVL( NVL(api.choice_number, appl_oss.choice_number), -1)
1834 AND NVL(routeb_pref , '*') = NVL( NVL(api.routeb_pref,appl_oss.routeb_pref), '*')
1835 AND NVL(alt_appl_id , '*') = NVL( NVL(api.alt_appl_id, appl_oss.alt_appl_id) , '*')
1836 AND NVL(adm_cal_type,'*') = NVL(NVL(api.adm_cal_type, appl_oss.adm_cal_type),'*')
1837 AND NVL(acad_cal_type, '*') = NVL(NVL(api.acad_cal_type, appl_oss.acad_cal_type),'*')
1838 AND NVL(api.acad_ci_sequence_number,-99) = NVL(NVL(api.acad_ci_sequence_number, appl_oss.acad_ci_sequence_number),-99)
1839 AND NVL(api.adm_ci_sequence_number,-99) = NVL(NVL(api.adm_ci_sequence_number, appl_oss.adm_ci_sequence_number), -99)
1840 );
1841 END IF;
1842 COMMIT;
1843
1844 UPDATE igs_ad_ps_appl_inst_int a
1845 SET (person_id, admission_appl_number,interface_run_id ) =
1846 ( SELECT person_id, admission_appl_number ,interface_run_id
1847 FROM igs_ad_apl_int
1848 WHERE interface_appl_id = a.interface_appl_id
1849 AND status IN ('1','4') )
1850 WHERE status = '2'
1851 AND interface_appl_id IN (SELECT interface_appl_id
1852 FROM igs_ad_apl_int
1853 WHERE interface_run_id = p_interface_run_id
1854 AND update_adm_appl_number IS NOT NULL
1855 AND status IN ('1','4'));
1856
1857 COMMIT;
1858 -- All the application creations and updations are completed.
1859 -- Only the updated applications' isntances needs to be processed.
1860 Process_application_instance(p_interface_run_id);
1861 --Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and
1862 --MATCH IND <> 21, 25, ones failed above discrepancy check
1863 IF p_rule = 'R' THEN
1864 UPDATE igs_ad_apl_int api
1865 SET
1866 status = '3'
1867 , match_ind = '20'
1868 WHERE interface_run_id = p_interface_run_id
1869 AND status = '2'
1870 AND update_adm_appl_number IS NOT NULL;
1871
1872 END IF;
1873 COMMIT;
1874
1875
1876 --Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
1877 IF p_rule = 'R' THEN
1878 UPDATE igs_ad_apl_int api
1879 SET
1880 status = '3'
1881 , error_code = 'E700'
1882 WHERE interface_run_id = p_interface_run_id
1883 AND status = '2'
1884 AND match_ind IS NOT NULL;
1885 END IF;
1886 COMMIT;
1887
1888 END prc_appcln;
1889
1890 END igs_ad_imp_004;