[Home] [Help]
PACKAGE BODY: APPS.IGS_PRECREATE_APPL_PUB
Source
1 PACKAGE BODY IGS_PRECREATE_APPL_PUB AS
2 /* $Header: IGSPSAPB.pls 120.3 2006/05/25 06:19:40 arvsrini noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2 (30):='IGS_PRECREATE_APPL_PUB';
4
5 PROCEDURE check_length(p_param_name IN VARCHAR2, p_table_name IN VARCHAR2, p_param_length IN NUMBER) AS
6 CURSOR c_col_length IS
7 SELECT WIDTH , precision , column_type ,scale
8 FROM FND_COLUMNS
9 WHERE table_id IN
10 ( SELECT TABLE_ID
11 FROM FND_TABLES
12 WHERE table_name = p_table_name AND APPLICATION_ID = 8405)
13 AND column_name = p_param_name
14 AND APPLICATION_ID = 8405;
15
16 l_col_length c_col_length%ROWTYPE;
17
18 BEGIN
19 OPEN c_col_length;
20 FETCH c_col_length INTO l_col_length;
21 CLOSE c_col_length;
22 IF l_col_length.column_type = 'V' AND p_param_length > l_col_length.width THEN
23 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
24 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
25 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.width);
26 IGS_GE_MSG_STACK.ADD;
27 RAISE FND_API.G_EXC_ERROR;
28 ELSIF l_col_length.column_type ='N' AND p_param_length > (l_col_length.precision - l_col_length.scale) THEN
29 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
30 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
31 IF l_col_length.scale > 0 THEN
32 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision || ',' || l_col_length.scale);
33 ELSE
34 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision );
35 END IF;
36 IGS_GE_MSG_STACK.ADD;
37 RAISE FND_API.G_EXC_ERROR;
38 END IF;
39 END check_length;
40
41 --API
42 PROCEDURE PRE_CREATE_APPLICATION(
43 --Standard Parameters Start
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
46 p_commit IN VARCHAR2 default FND_API.G_FALSE,
47 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 --Standard parameter ends
52 p_person_id IN NUMBER,
53 p_appl_date IN DATE,
54 p_acad_cal_type IN VARCHAR2,
55 p_acad_cal_seq_number IN NUMBER,
56 p_adm_cal_type IN VARCHAR2,
57 p_adm_cal_seq_number IN NUMBER,
58 p_entry_status IN NUMBER,
59 p_entry_level IN NUMBER,
60 p_spcl_gr1 IN NUMBER,
61 p_spcl_gr2 IN NUMBER,
62 p_apply_for_finaid IN VARCHAR2,
63 p_finaid_apply_date IN DATE,
64 p_admission_application_type IN VARCHAR2,
65 p_apsource_id IN NUMBER,
66 p_application_fee_amount IN NUMBER,
67 x_ss_adm_appl_id OUT NOCOPY NUMBER
68 )
69 AS
70 l_api_version CONSTANT NUMBER := '1.0';
71 l_api_name CONSTANT VARCHAR2(30) := 'PRE_CREATE_APPLICATION';
72 l_msg_index NUMBER;
73 l_return_status VARCHAR2(1);
74 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
75
76 -- Cursor to validate person
77 CURSOR c_person(p_person_id hz_parties.party_id%TYPE) IS
78 SELECT party_id
79 FROM hz_parties hzp
80 WHERE hzp.party_id = p_person_id;
81
82 -- Cursor to validate Entry Status
83 CURSOR c_estatus(p_estatus IGS_AD_CODE_CLASSES.code_id%TYPE) IS
84 SELECT code_id
85 FROM IGS_AD_CODE_CLASSES
86 WHERE class = 'STATUS' AND
87 code_id = p_estatus;
88
89 -- Cursor to validate Entry Level
90 CURSOR c_elevel(p_elevel IGS_AD_CODE_CLASSES.code_id%TYPE) IS
91 SELECT code_id
92 FROM IGS_AD_CODE_CLASSES
93 WHERE class = 'LEVEL' AND
94 code_id = p_elevel;
95
96 -- Cursor to validate Special Group 1
97 CURSOR c_spcl_grp1(p_spcl_grp1 IGS_AD_CODE_CLASSES.code_id%TYPE) IS
98 SELECT code_id
99 FROM IGS_AD_CODE_CLASSES
100 WHERE class = 'SPECIAL_GROUP1' AND
101 code_id = p_spcl_grp1;
102
103 -- Cursor to validate Special Group 2
104 CURSOR c_spcl_grp2(p_spcl_grp2 IGS_AD_CODE_CLASSES.code_id%TYPE) IS
105 SELECT code_id
106 FROM IGS_AD_CODE_CLASSES
107 WHERE class = 'SPECIAL_GROUP2' AND
108 code_id = p_spcl_grp2;
109
110 -- Cursor to validate Admission Application Type
111 CURSOR c_apptype(p_app_type igs_ad_ss_appl_typ.admission_application_type%TYPE) IS
112 SELECT admission_application_type, admission_cat, s_admission_process_type
113 FROM igs_ad_ss_appl_typ
114 WHERE admission_application_type = p_app_type;
115
116 -- Cursor to validate whether the Application Type is available in the current Admission Calendar
117 CURSOR c_apptype_admcal(p_adm_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,p_adm_cal_seq IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE,
118 p_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE, p_s_admission_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%TYPE) IS
119 SELECT adm_cal_type
120 FROM IGS_AD_PRD_AD_PRC_CA
121 WHERE adm_cal_type = p_adm_cal_type AND
122 adm_ci_sequence_number = p_adm_cal_seq AND
123 admission_cat = p_admission_cat AND
124 s_admission_process_type = p_s_admission_process_type;
125
126 -- Cursor to validate Application Source Id. Applicant could be either Web Applicant or Web Staff
127 CURSOR c_appsource(p_app_source_id IGS_AD_CODE_CLASSES.code_id%TYPE) IS
128 SELECT code_id
129 FROM IGS_AD_CODE_CLASSES
130 WHERE class = 'SYS_APPL_SOURCE' AND
131 code_id = p_app_source_id AND
132 SYSTEM_STATUS IN ('WEB_STAFF', 'WEB_APPL');
133
134 -- Cursor to validate Calendars
135 CURSOR c_cal(p_cal_type IGS_CA_INST.cal_type%TYPE, p_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
136 SELECT cal.cal_type, cal.sequence_number,cal.description
137 FROM IGS_CA_INST cal,
138 igs_ca_stat cstat
139 WHERE cal.cal_type = p_cal_type AND
140 cal.sequence_number = p_sequence_number AND
141 end_dt > sysdate AND
142 cstat.cal_status = cal.cal_status AND
143 cstat.s_cal_status = 'ACTIVE';
144
145 -- Cursor to validte relationship between Academic Calendar and Admission Calendar
146 CURSOR c_cal_rel(p_adm_cal_type IGS_CA_INST.cal_type%TYPE, p_adm_sequence_number IGS_CA_INST.sequence_number%TYPE,p_acad_cal_type IGS_CA_INST.cal_type%TYPE, p_acad_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
147 SELECT ci1.CAL_TYPE
148 FROM
149 igs_ca_inst ci1,
150 igs_ca_inst_rel cir,
151 igs_ca_inst ci2,
152 igs_ca_type cat1,
153 igs_ca_type cat2,
154 igs_ca_stat cstat
155 WHERE
156 ci1.CAL_TYPE = p_adm_cal_type AND
157 ci1.sequence_number = p_adm_sequence_number AND
158 ci2.CAL_TYPE = p_acad_cal_type AND
159 ci2.sequence_number = p_acad_sequence_number AND
160 cir.sub_cal_type = ci1.CAL_TYPE AND
161 cir.sub_ci_sequence_number = ci1.sequence_number AND
162 ci2.CAL_TYPE = cir.sup_cal_type AND
163 ci2.sequence_number = cir.sup_ci_sequence_number AND
164 cat1.CAL_TYPE = ci1.CAL_TYPE AND
165 cat1.S_CAL_CAT = 'ADMISSION' AND
166 cat2.CAL_TYPE = ci2.CAL_TYPE AND
167 cat2.S_CAL_CAT = 'ACADEMIC' AND
168 ci2.end_dt > sysdate AND
169 ci1.end_dt > sysdate AND
170 cstat.cal_status = ci1.cal_status AND
171 cstat.cal_status = ci2.cal_status AND
172 cstat.s_cal_status = 'ACTIVE';
173
174 -- Cursor to find out if Late Applications are allowed
175 CURSOR c_late_app(p_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,p_s_adm_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
176 SELECT S_ADMISSION_STEP_TYPE
177 FROM IGS_AD_PRCS_CAT_STEP apcs
178 WHERE admission_cat = p_admission_cat AND
179 s_admission_process_type = p_s_adm_process_type AND
180 s_admission_step_type = 'LATE-APP' AND
181 mandatory_step_ind = 'Y';
182
183 l_person_rec igs_pe_hz_parties.party_id%TYPE := NULL;
184 l_estatus_rec IGS_AD_CODE_CLASSES.CODE_ID%TYPE := NULL;
185 l_elevel_rec IGS_AD_CODE_CLASSES.CODE_ID%TYPE := NULL;
186 l_spcl_grp1_rec IGS_AD_CODE_CLASSES.CODE_ID%TYPE := NULL;
187 l_spcl_grp2_rec IGS_AD_CODE_CLASSES.CODE_ID%TYPE := NULL;
188 l_apptype_admcal_rec IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE := NULL;
189 l_late_app_rec IGS_AD_PRCS_CAT_STEP.S_ADMISSION_STEP_TYPE%TYPE := NULL;
190 l_appsource_rec IGS_AD_CODE_CLASSES.CODE_ID%TYPE := NULL;
191 l_cal_rel_rec IGS_CA_INST.CAL_TYPE%TYPE := NULL;
192
193 l_apptype_rec c_apptype%ROWTYPE := NULL;
194 l_cal_rec c_cal%ROWTYPE := NULL;
195
196 l_person_id IGS_SS_ADM_APPL_STG.person_id%TYPE;
197 l_appl_date IGS_SS_ADM_APPL_STG.appl_date%TYPE;
198 l_acad_cal_type IGS_SS_ADM_APPL_STG.acad_cal_type%TYPE;
199 l_acad_cal_seq_number IGS_SS_ADM_APPL_STG.acad_cal_seq_number%TYPE;
200 l_adm_cal_type IGS_SS_ADM_APPL_STG.adm_cal_type%TYPE;
201 l_adm_cal_seq_number IGS_SS_ADM_APPL_STG.adm_cal_seq_number%TYPE;
202 l_description IGS_SS_ADM_APPL_STG.description%TYPE;
203 l_entry_status IGS_SS_ADM_APPL_STG.entry_status%TYPE;
204 l_entry_level IGS_SS_ADM_APPL_STG.entry_level%TYPE;
205 l_spcl_gr1 IGS_SS_ADM_APPL_STG.SPCL_GRP_1%TYPE;
206 l_spcl_gr2 IGS_SS_ADM_APPL_STG.SPCL_GRP_2%TYPE;
207 l_apply_for_finaid IGS_SS_ADM_APPL_STG.apply_for_finaid%TYPE;
208 l_finaid_apply_date IGS_SS_ADM_APPL_STG.finaid_apply_date%TYPE;
209 l_admission_application_type IGS_SS_ADM_APPL_STG.admission_application_type%TYPE;
210 l_s_adm_process_type IGS_SS_ADM_APPL_STG.s_adm_process_type%TYPE;
211 l_admission_cat IGS_SS_ADM_APPL_STG.admission_cat%TYPE;
212 l_apsource_id IGS_SS_ADM_APPL_STG.app_source_id%TYPE;
213 l_application_fee_amount IGS_SS_ADM_APPL_STG.appl_fee_amt%TYPE;
214 l_ss_adm_appl_id IGS_SS_ADM_APPL_STG.ss_adm_appl_id%TYPE;
215 l_message_name VARCHAR2(2000);
216
217 BEGIN
218 SAVEPOINT PRE_CREATE_APPLICATION_pub;
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220
221 l_msg_index := 0;
222
223 -- Standard call to check for call compatibility.
224 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227
228 -- Initialize message list if p_init_msg_list is set to TRUE.
229 IF FND_API.to_Boolean(p_init_msg_list) THEN
230 FND_MSG_PUB.initialize;
231 END IF;
232 l_msg_index := igs_ge_msg_stack.count_msg;
233
234 -----------------------
235 -- Validate all the parameters for their length
236 -----------------------
237 -- p_person_id
238 check_length('PERSON_ID', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_person_id)));
239 -- p_acad_cal_type
240 check_length('ACAD_CAL_TYPE', 'IGS_SS_ADM_APPL_STG', length(p_acad_cal_type));
241 -- p_acad_cal_seq_number
242 check_length('ACAD_CAL_SEQ_NUMBER', 'IGS_SS_ADM_APPL_STG', length(p_acad_cal_seq_number));
243 -- p_adm_cal_type
244 check_length('ADM_CAL_TYPE', 'IGS_SS_ADM_APPL_STG', length(p_adm_cal_type));
245 -- p_adm_cal_seq_number
246 check_length('ADM_CAL_SEQ_NUMBER', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_adm_cal_seq_number)));
247 -- p_entry_status
248 check_length('ENTRY_STATUS', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_entry_status)));
249 -- p_entry_level
250 check_length('ENTRY_LEVEL', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_entry_level)));
251 -- p_spcl_gr1
252 check_length('SPCL_GR1', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_spcl_gr1)));
253 -- p_spcl_gr2
254 check_length('SPCL_GR2', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_spcl_gr2)));
255 -- p_apply_for_finaid
256 check_length('APPLY_FOR_FINAID', 'IGS_SS_ADM_APPL_STG', length(p_apply_for_finaid));
257 -- p_admission_application_type
258 check_length('ADMISSION_APPLICATION_TYPE', 'IGS_SS_ADM_APPL_STG', length(p_admission_application_type));
259 -- p_apsource_id
260 check_length('APSOURCE_ID', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_apsource_id)));
261 -- p_application_fee_amount
262 check_length('APPLICATION_FEE_AMOUNT', 'IGS_SS_ADM_APPL_STG', length(TRUNC(p_application_fee_amount)));
263
264 -- end of parameter-lenghth validations.
265
266 ---------------------
267 --Intialization of variables to handle G_MISS_CHAR/NUM/DATE
268 ---------------------
269
270 IF p_person_id = FND_API.G_MISS_NUM OR p_person_id IS NULL THEN
271 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
272 IGS_GE_MSG_STACK.ADD;
273 RAISE FND_API.G_EXC_ERROR;
274 ELSE
275 l_person_id := p_person_id;
276 END IF;
277
278 IF p_appl_date IS NULL OR p_appl_date = FND_API.G_MISS_DATE THEN
279 l_appl_date := SYSDATE;
280 ELSE
281 l_appl_date := p_appl_date;
282 END IF;
283
284 IF p_acad_cal_type = FND_API.G_MISS_CHAR OR p_acad_cal_type IS NULL THEN
285 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
286 IGS_GE_MSG_STACK.ADD;
287 RAISE FND_API.G_EXC_ERROR;
288 ELSE
289 l_acad_cal_type := p_acad_cal_type;
290 END IF;
291
292 IF p_acad_cal_seq_number = FND_API.G_MISS_NUM OR p_acad_cal_seq_number IS NULL THEN
293 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
294 IGS_GE_MSG_STACK.ADD;
295 RAISE FND_API.G_EXC_ERROR;
296 ELSE
297 l_acad_cal_seq_number := p_acad_cal_seq_number;
298 END IF;
299
300 IF p_adm_cal_type = FND_API.G_MISS_CHAR OR p_adm_cal_type IS NULL THEN
301 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
302 IGS_GE_MSG_STACK.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 ELSE
305 l_adm_cal_type := p_adm_cal_type;
306 END IF;
307
308 IF p_adm_cal_seq_number = FND_API.G_MISS_NUM OR p_adm_cal_seq_number IS NULL THEN
309 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
310 IGS_GE_MSG_STACK.ADD;
311 RAISE FND_API.G_EXC_ERROR;
312 ELSE
313 l_adm_cal_seq_number :=p_adm_cal_seq_number ;
314 END IF;
315
316
317 IF p_entry_status = FND_API.G_MISS_NUM THEN
318 l_entry_status := NULL;
319 ELSE
320 l_entry_status := p_entry_status;
321 END IF;
322
323 IF p_entry_level = FND_API.G_MISS_NUM THEN
324 l_entry_level := NULL;
325 ELSE
326 l_entry_level := p_entry_level;
327 END IF;
328
329 IF p_spcl_gr1 = FND_API.G_MISS_NUM THEN
330 l_spcl_gr1 := NULL;
331 ELSE
332 l_spcl_gr1 := p_spcl_gr1;
333 END IF;
334
335 IF p_spcl_gr2 = FND_API.G_MISS_NUM THEN
336 l_spcl_gr2 := NULL;
337 ELSE
338 l_spcl_gr2 := p_spcl_gr2;
339 END IF;
340
341 IF p_apply_for_finaid = FND_API.G_MISS_CHAR THEN
342 l_apply_for_finaid := NULL;
343 ELSE
344 l_apply_for_finaid := p_apply_for_finaid;
345 END IF;
346
347 IF p_finaid_apply_date = FND_API.G_MISS_DATE THEN
348 l_finaid_apply_date := NULL;
349 ELSE
350 l_finaid_apply_date := p_finaid_apply_date;
351 END IF;
352
353 IF p_admission_application_type = FND_API.G_MISS_CHAR OR p_admission_application_type IS NULL THEN
354 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
355 IGS_GE_MSG_STACK.ADD;
356 RAISE FND_API.G_EXC_ERROR;
357 ELSE
358 l_admission_application_type := p_admission_application_type;
359 END IF;
360
361 IF p_apsource_id = FND_API.G_MISS_NUM OR p_apsource_id IS NULL THEN
362 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PARAM_MISSING');
363 IGS_GE_MSG_STACK.ADD;
364 RAISE FND_API.G_EXC_ERROR;
365 ELSE
366 l_apsource_id := p_apsource_id;
367 END IF;
368
369 IF p_application_fee_amount = FND_API.G_MISS_NUM OR p_application_fee_amount IS NULL THEN
370 l_application_fee_amount := 0;
371 ELSE
372 l_application_fee_amount := p_application_fee_amount;
373 END IF;
374
375 ---------------------------
376 -- Validate the values of the parameters passed in the API
377 ---------------------------
378
379
380 -- Validate Application Fee Amount
381
382 IF l_application_fee_amount < 0 THEN
383 FND_MESSAGE.SET_NAME('IGS','IGS_AD_FEE_AMT_NON_NEGATIVE');
384 IGS_GE_MSG_STACK.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388
389 -- Validate Person
390 OPEN c_person(l_person_id);
391 FETCH c_person INTO l_person_rec;
392 CLOSE c_person;
393
394 IF l_person_rec IS NULL THEN
395 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
396 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'));
397 IGS_GE_MSG_STACK.ADD;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 -- Validate Admission Calendar
402 OPEN c_cal(l_adm_cal_type,l_adm_cal_seq_number);
403 FETCH c_cal INTO l_cal_rec;
404 CLOSE c_cal;
405
406 IF l_cal_rec.cal_type IS NULL THEN
407 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
408 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_CAL'));
409 IGS_GE_MSG_STACK.ADD;
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412
413 -- derive Admission Calendar Description
414 l_description := l_cal_rec.description;
415
416 -- Validate Academic Calendar
417 l_cal_rec := NULL;
418 OPEN c_cal(l_acad_cal_type,l_acad_cal_seq_number);
419 FETCH c_cal INTO l_cal_rec;
420 CLOSE c_cal;
421
422 IF l_cal_rec.cal_type IS NULL THEN
423 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
424 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ACAD_CAL'));
425 IGS_GE_MSG_STACK.ADD;
426 RAISE FND_API.G_EXC_ERROR;
427 END IF;
428
429 -- Validate Admission and Academic Calendar Relationship
430 OPEN c_cal_rel(l_adm_cal_type,l_adm_cal_seq_number,l_acad_cal_type,l_acad_cal_seq_number);
431 FETCH c_cal_rel INTO l_cal_rel_rec;
432 CLOSE c_cal_rel;
433
434 IF l_cal_rel_rec IS NULL THEN
435 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ADMCAL_CHILD_ACACAL');
436 IGS_GE_MSG_STACK.ADD;
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440 -- Validate Entry Status
441 IF l_entry_status IS NOT NULL THEN
442 OPEN c_estatus(l_entry_status);
443 FETCH c_estatus INTO l_estatus_rec;
444 CLOSE c_estatus;
445
446 IF l_estatus_rec IS NULL THEN
447 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
448 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENTRY_STAT'));
449 IGS_GE_MSG_STACK.ADD;
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452 END IF;
453
454 -- Validate Entry Level
455 IF l_entry_level IS NOT NULL THEN
456 OPEN c_elevel(l_entry_level);
457 FETCH c_elevel INTO l_elevel_rec;
458 CLOSE c_elevel;
459
460 IF l_elevel_rec IS NULL THEN
461 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
462 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENTRY_LVL'));
463 IGS_GE_MSG_STACK.ADD;
464 RAISE FND_API.G_EXC_ERROR;
465 END IF;
466 END IF;
467
468 -- Validate Special Group 1
469 IF l_spcl_gr1 IS NOT NULL THEN
470 OPEN c_spcl_grp1(l_spcl_gr1);
471 FETCH c_spcl_grp1 INTO l_spcl_grp1_rec;
472 CLOSE c_spcl_grp1;
473
474 IF l_spcl_grp1_rec IS NULL THEN
475 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_GRP1'));
477 IGS_GE_MSG_STACK.ADD;
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480 END IF;
481
482 -- Validate Special Group 2
483 IF l_spcl_gr2 IS NOT NULL THEN
484 OPEN c_spcl_grp2(l_spcl_gr2);
485 FETCH c_spcl_grp2 INTO l_spcl_grp2_rec;
486 CLOSE c_spcl_grp2;
487
488 IF l_spcl_grp2_rec IS NULL THEN
489 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
490 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_GRP2'));
491 IGS_GE_MSG_STACK.ADD;
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494 END IF;
495
496 -- Validate Admission Application Type
497 OPEN c_apptype(l_admission_application_type);
498 FETCH c_apptype INTO l_apptype_rec;
499 CLOSE c_apptype;
500
501 IF l_apptype_rec.admission_application_type IS NULL THEN
502 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
503 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_APPL_TYPE'));
504 IGS_GE_MSG_STACK.ADD;
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507
508 -- Derive ADMISSION_CAT and S_ADM_PROCESS_TYPE
509 l_s_adm_process_type := l_apptype_rec.s_admission_process_type;
510 l_admission_cat := l_apptype_rec.admission_cat;
511
512 -- Validate whether the Application Type is available in the current Admission Calendar
513 OPEN c_apptype_admcal(l_adm_cal_type,l_adm_cal_seq_number,l_admission_cat,l_s_adm_process_type);
514 FETCH c_apptype_admcal INTO l_apptype_admcal_rec;
515 CLOSE c_apptype_admcal;
516
517 IF l_apptype_admcal_rec IS NULL THEN
518 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_APP_TYPE');
519 IGS_GE_MSG_STACK.ADD;
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523 -- Validate Apply for Financial Aid
524 IF l_apply_for_finaid IS NOT NULL THEN
525 IF (NOT (l_apply_for_finaid = 'Y' OR l_apply_for_finaid = 'N')) THEN
526 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
527 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FIN_AID'));
528 IGS_GE_MSG_STACK.ADD;
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531 END IF;
532
533 -- Validate Financial Aid Apply Date
534 IF l_apply_for_finaid IS NULL AND l_finaid_apply_date IS NOT NULL THEN
535 FND_MESSAGE.SET_NAME('IGS','IGS_AD_FIN_DATE_FIN_AID_REQ');
536 IGS_GE_MSG_STACK.ADD;
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 -- Validate Application Date.
541 -- 1. If Application Date is NULL default to SYSDATE.
542
543 -- 2. Application Date cannot be greater than SYSDATE.
544 IF l_appl_date > SYSDATE THEN
545 FND_MESSAGE.SET_NAME('IGS','IGS_AD_APPLDT_LE_CURRENT_DT');
546 IGS_GE_MSG_STACK.ADD;
547 RAISE FND_API.G_EXC_ERROR;
548 END IF;
549
550 -- 3. SYSDATE should not have passed the Application submission deadline.
551 OPEN c_late_app(l_admission_cat,l_s_adm_process_type);
552 FETCH c_late_app INTO l_late_app_rec;
553 CLOSE c_late_app;
554
555 IF l_late_app_rec IS NULL THEN -- Late Applications Not Allowed
556 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_late (
557 SYSDATE,
558 NULL, --p_course_cd,
559 NULL, --p_version_number,
560 l_acad_cal_type,
561 NULL, --p_location_cd,
562 NULL, --p_attendance_mode,
563 NULL, --p_attendance_type,
564 l_adm_cal_type,
565 l_adm_cal_seq_number,
566 l_admission_cat,
567 l_s_adm_process_type,
568 'N', -- late app not allowed
569 l_message_name) THEN
570 -- SYSDATE has passed submission deadline
571 FND_MESSAGE.SET_NAME('IGS','IGS_AD_SUB_DEADLINE');
572 IGS_GE_MSG_STACK.ADD;
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575 ELSE -- Late Applications Allowed
576 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_late (
577 SYSDATE,
578 NULL, --p_course_cd,
579 NULL, --p_version_number,
580 l_acad_cal_type,
581 NULL, --p_location_cd,
582 NULL, --p_attendance_mode,
583 NULL, --p_attendance_type,
584 l_adm_cal_type,
585 l_adm_cal_seq_number,
586 l_admission_cat,
587 l_s_adm_process_type,
588 'Y', -- late app allowed
589 l_message_name) THEN
590 -- SYSDATE has passed submission deadline
591 FND_MESSAGE.SET_NAME('IGS','IGS_AD_SUB_DEADLINE');
592 IGS_GE_MSG_STACK.ADD;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595 END IF;
596
597 -- Validate Application Source Id
598 OPEN c_appsource(l_apsource_id);
599 FETCH c_appsource INTO l_appsource_rec;
600 CLOSE c_appsource;
601
602 IF l_appsource_rec IS NULL THEN
603 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
604 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APP_SOURCE'));
605 IGS_GE_MSG_STACK.ADD;
606 RAISE FND_API.G_EXC_ERROR;
607 END IF;
608
609 ---------------------
610 -- Insert the Application Record.
611 ---------------------
612
613 INSERT INTO IGS_SS_ADM_APPL_STG (SS_ADM_APPL_ID,
614 PERSON_ID,
615 APPL_DATE,
616 ACAD_CAL_TYPE,
617 ACAD_CAL_SEQ_NUMBER,
618 ADM_CAL_TYPE,
619 ADM_CAL_SEQ_NUMBER,
620 ADMISSION_CAT,
621 S_ADM_PROCESS_TYPE,
622 ENTRY_STATUS,
623 ENTRY_LEVEL,
624 SPCL_GRP_1,
625 SPCL_GRP_2,
626 APPLY_FOR_FINAID,
627 FINAID_APPLY_DATE,
628 LAST_UPDATE_DATE,
629 LAST_UPDATED_BY,
630 CREATION_DATE,
631 CREATED_BY,
632 LAST_UPDATE_LOGIN,
633 ADMISSION_APPLICATION_TYPE,
634 DESCRIPTION,
635 APP_SOURCE_ID,
636 APPL_FEE_AMT)
637 VALUES (IGS_SS_ADM_APPL_S.NEXTVAL,
638 l_person_id,
639 l_appl_date,
640 l_acad_cal_type,
641 l_acad_cal_seq_number,
642 l_adm_cal_type,
643 l_adm_cal_seq_number,
644 l_admission_cat,
645 l_s_adm_process_type,
646 l_entry_status,
647 l_entry_level,
648 l_spcl_gr1,
649 l_spcl_gr2,
650 l_apply_for_finaid,
651 l_finaid_apply_date,
652 SYSDATE,
653 FND_GLOBAL.USER_ID,
654 SYSDATE,
655 FND_GLOBAL.USER_ID,
656 FND_GLOBAL.USER_ID,
657 l_admission_application_type,
658 l_description,
659 l_apsource_id,
660 l_application_fee_amount
661 ) RETURNING SS_ADM_APPL_ID INTO x_ss_adm_appl_id;
662
663 IF FND_API.To_Boolean( p_commit ) THEN
664 COMMIT;
665 END IF;
666
667 -- Exception Handling
668 EXCEPTION
669 WHEN FND_API.G_EXC_ERROR THEN
670 ROLLBACK TO PRE_CREATE_APPLICATION_pub;
671 igs_ad_gen_016.extract_msg_from_stack (
672 p_msg_at_index => l_msg_index,
673 p_return_status => l_return_status,
674 p_msg_count => x_msg_count,
675 p_msg_data => x_msg_data,
676 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
677
678 IF l_hash_msg_name_text_type_tab(x_msg_count - 2).name <> 'ORA' THEN
679 x_return_status := FND_API.G_RET_STS_ERROR;
680 ELSE
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 END IF;
683
684 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
685 x_msg_count := x_msg_count-1;
686
687 WHEN OTHERS THEN
688 ROLLBACK TO PRE_CREATE_APPLICATION_pub;
689 igs_ad_gen_016.extract_msg_from_stack (
690 p_msg_at_index => l_msg_index,
691 p_return_status => l_return_status,
692 p_msg_count => x_msg_count,
693 p_msg_data => x_msg_data,
694 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
695
696 IF l_hash_msg_name_text_type_tab(x_msg_count - 1).name <> 'ORA' THEN
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 ELSE
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 END IF;
701
702 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-1).text;
703
704 END PRE_CREATE_APPLICATION;
705
706
707 --API
708 PROCEDURE PRE_CREATE_APPLICATION_INST(
709 --Standard Parameters Start
710 p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
712 p_commit IN VARCHAR2 default FND_API.G_FALSE,
713 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 --Standard parameter ends
718 p_ss_adm_appl_id IN NUMBER,
719 p_sch_apl_to_id IN NUMBER,
720 p_location_cd IN VARCHAR2,
721 p_attendance_type IN VARCHAR2,
722 p_attendance_mode IN VARCHAR2,
723 p_attribute_category IN VARCHAR2,
724 p_attribute1 IN VARCHAR2,
725 p_attribute2 IN VARCHAR2,
726 p_attribute3 IN VARCHAR2,
727 p_attribute4 IN VARCHAR2,
728 p_attribute5 IN VARCHAR2,
729 p_attribute6 IN VARCHAR2,
730 p_attribute7 IN VARCHAR2,
731 p_attribute8 IN VARCHAR2,
732 p_attribute9 IN VARCHAR2,
733 p_attribute10 IN VARCHAR2,
734 p_attribute11 IN VARCHAR2,
735 p_attribute12 IN VARCHAR2,
736 p_attribute13 IN VARCHAR2,
737 p_attribute14 IN VARCHAR2,
738 p_attribute15 IN VARCHAR2,
739 p_attribute16 IN VARCHAR2,
740 p_attribute17 IN VARCHAR2,
741 p_attribute18 IN VARCHAR2,
742 p_attribute19 IN VARCHAR2,
743 p_attribute20 IN VARCHAR2,
744 p_attribute21 IN VARCHAR2,
745 p_attribute22 IN VARCHAR2,
746 p_attribute23 IN VARCHAR2,
747 p_attribute24 IN VARCHAR2,
748 p_attribute25 IN VARCHAR2,
749 p_attribute26 IN VARCHAR2,
750 p_attribute27 IN VARCHAR2,
751 p_attribute28 IN VARCHAR2,
752 p_attribute29 IN VARCHAR2,
753 p_attribute30 IN VARCHAR2,
754 p_attribute31 IN VARCHAR2,
755 p_attribute32 IN VARCHAR2,
756 p_attribute33 IN VARCHAR2,
757 p_attribute34 IN VARCHAR2,
758 p_attribute35 IN VARCHAR2,
759 p_attribute36 IN VARCHAR2,
760 p_attribute37 IN VARCHAR2,
761 p_attribute38 IN VARCHAR2,
762 p_attribute39 IN VARCHAR2,
763 p_attribute40 IN VARCHAR2,
764 x_ss_admappl_pgm_id OUT NOCOPY NUMBER
765 )
766 AS
767 l_api_version CONSTANT NUMBER := '1.0';
768 l_api_name CONSTANT VARCHAR2(30) := 'PRE_CREATE_APPLICATION_INST';
769 l_msg_index NUMBER;
770 l_return_status VARCHAR2(1);
771 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
772
773 /* ****************** PUT CURSOR DEFINITIONS FOR VALIDATION PURPOSE HERE ************************** */
774
775 -- Cursor to validate Parent Application's Application Identifier
776 CURSOR c_admappl(p_ss_adm_appl_id NUMBER) IS
777 SELECT ss_adm_appl_id, person_id, admission_cat,s_adm_process_type, admission_application_type
778 FROM IGS_SS_ADM_APPL_STG
779 WHERE ss_adm_appl_id = p_ss_adm_appl_id;
780
781
782 -- Cursor to validate School Applying To
783 CURSOR c_sch_apl_to(p_sch_apl_to_id NUMBER) IS
784 SELECT sch_apl_to_id
785 FROM IGS_AD_SCHL_APLY_TO
786 WHERE sch_apl_to_id = p_sch_apl_to_id;
787
788 -- Cursor to validate Location Code
789 CURSOR c_location(p_location_cd VARCHAR2) IS
790 SELECT location_cd
791 FROM IGS_AD_LOCATION
792 WHERE location_cd = p_location_cd;
793
794 -- Cursor to validate Attendance Type
795 CURSOR c_att_type(p_attendance_type VARCHAR2) IS
796 SELECT attendance_type
797 FROM IGS_EN_ATD_TYPE
798 WHERE attendance_type = p_attendance_type;
799
800 -- Cursor to validate Attendance Mode
801 CURSOR c_att_mode(p_attendance_mode VARCHAR2) IS
802 SELECT attendance_mode
803 FROM IGS_EN_ATD_MODE
804 WHERE attendance_mode = p_attendance_mode;
805
806
807 -- Cursor to validate Program Offering Options
808 CURSOR c_off_pattern(p_admission_cat VARCHAR2, p_s_adm_process_type VARCHAR2,p_location_cd VARCHAR2, p_attendance_type VARCHAR2,p_attendance_mode VARCHAR2) IS
809 SELECT acopv.ACAD_CAL_TYPE
810 FROM IGS_PS_OFR_PAT_OFERPAT_V acopv,
811 IGS_AD_LOCATION loc,
812 IGS_EN_ATD_MODE atd_mode,
813 IGS_EN_ATD_TYPE atd_type
814 WHERE
815 (IGS_AD_GEN_013.ADMS_GET_COO_CRV(
816 acopv.course_cd,
817 acopv.version_number,
818 acopv.s_admission_process_type, 'Y') = 'Y') AND
819 (IGS_AD_GEN_013.ADMS_GET_ACAI_COO (
820 acopv.course_cd,
821 acopv.version_number,
822 acopv.location_cd,
823 acopv.attendance_mode,
824 acopv.attendance_type,
825 acopv.acad_cal_type,
826 acopv.acad_ci_sequence_number,
827 acopv.adm_cal_type,
828 acopv.adm_ci_sequence_number,
829 acopv.admission_cat,
830 acopv.s_admission_process_type,
831 'Y',
832 trunc(sysdate),
833 'Y')= 'Y') AND
834 acopv.admission_cat = p_admission_cat AND
835 acopv.s_admission_process_type = p_s_adm_process_type AND
836 acopv.location_cd = NVL(p_location_cd,acopv.location_cd )AND
837 acopv.attendance_mode = NVL(p_attendance_mode,acopv.attendance_mode) AND
838 acopv.attendance_type = NVL(p_attendance_type,acopv.attendance_type);
839
840 -- Cursor to check if the Preference Limit APC Step is set or not against the Application Type
841 CURSOR c_pref_limit(p_admission_cat VARCHAR2,p_s_adm_process_type VARCHAR2) IS
842 SELECT S_ADMISSION_STEP_TYPE
843 FROM IGS_AD_PRCS_CAT_STEP apcs
844 WHERE admission_cat = p_admission_cat AND
845 s_admission_process_type = p_s_adm_process_type AND
846 s_admission_step_type = 'PREF-LIMIT' AND
847 mandatory_step_ind = 'Y';
848
849
850 -- Cursor to check if another Program Instance for this Instance's Parent Application exists or not.
851 CURSOR c_prog_inst(p_ss_adm_appl_id NUMBER) IS
852 SELECT SS_ADMAPPL_PGM_ID
853 FROM IGS_SS_APP_PGM_STG
854 WHERE SS_ADM_APPL_ID = p_ss_adm_appl_id;
855
856 /* ******************* Put additional parameters here ******************************************** */
857 l_admappl_rec c_admappl%ROWTYPE := NULL;
858 l_sch_apl_to_rec IGS_AD_SCHL_APLY_TO.sch_apl_to_id%TYPE := NULL;
859 l_location_rec IGS_AD_LOCATION.location_cd%TYPE := NULL;
860 l_att_type_rec IGS_EN_ATD_TYPE.ATTENDANCE_TYPE%TYPE := NULL;
861 l_att_mode_rec IGS_EN_ATD_MODE.ATTENDANCE_MODE%TYPE := NULL;
862 l_off_pattern_rec IGS_PS_OFR_PAT_OFERPAT_V.ACAD_CAL_TYPE%TYPE := NULL;
863 l_pref_limit_rec IGS_AD_PRCS_CAT_STEP.S_ADMISSION_STEP_TYPE%TYPE := NULL;
864 l_prog_inst_rec IGS_SS_APP_PGM_STG.SS_ADMAPPL_PGM_ID%TYPE := NULL;
865
866 l_ss_adm_appl_id IGS_SS_APP_PGM_STG.ss_adm_appl_id%TYPE;
867 l_preference_number IGS_SS_APP_PGM_STG.preference_number%TYPE;
868 l_person_id IGS_SS_APP_PGM_STG.person_id%TYPE;
869 l_sch_apl_to_id IGS_SS_APP_PGM_STG.sch_apl_to_id%TYPE;
870 l_location_cd IGS_SS_APP_PGM_STG.location_cd%TYPE;
871 l_attendance_type IGS_SS_APP_PGM_STG.attendance_type%TYPE;
872 l_attendance_mode IGS_SS_APP_PGM_STG.attendance_mode%TYPE;
873 l_attribute_category IGS_SS_APP_PGM_STG.attribute_category%TYPE;
874 l_attribute1 IGS_SS_APP_PGM_STG.ATTRIBUTE1%TYPE;
875 l_attribute2 IGS_SS_APP_PGM_STG.ATTRIBUTE2%TYPE;
876 l_attribute3 IGS_SS_APP_PGM_STG.ATTRIBUTE3%TYPE;
877 l_attribute4 IGS_SS_APP_PGM_STG.ATTRIBUTE4%TYPE;
878 l_attribute5 IGS_SS_APP_PGM_STG.ATTRIBUTE5%TYPE;
879 l_attribute6 IGS_SS_APP_PGM_STG.ATTRIBUTE6%TYPE;
880 l_attribute7 IGS_SS_APP_PGM_STG.ATTRIBUTE7%TYPE;
881 l_attribute8 IGS_SS_APP_PGM_STG.ATTRIBUTE8%TYPE;
882 l_attribute9 IGS_SS_APP_PGM_STG.ATTRIBUTE9%TYPE;
883 l_attribute10 IGS_SS_APP_PGM_STG.ATTRIBUTE10%TYPE;
884 l_attribute11 IGS_SS_APP_PGM_STG.ATTRIBUTE11%TYPE;
885 l_attribute12 IGS_SS_APP_PGM_STG.ATTRIBUTE12%TYPE;
886 l_attribute13 IGS_SS_APP_PGM_STG.ATTRIBUTE13%TYPE;
887 l_attribute14 IGS_SS_APP_PGM_STG.ATTRIBUTE14%TYPE;
888 l_attribute15 IGS_SS_APP_PGM_STG.ATTRIBUTE15%TYPE;
889 l_attribute16 IGS_SS_APP_PGM_STG.ATTRIBUTE16%TYPE;
890 l_attribute17 IGS_SS_APP_PGM_STG.ATTRIBUTE17%TYPE;
891 l_attribute18 IGS_SS_APP_PGM_STG.ATTRIBUTE18%TYPE;
892 l_attribute19 IGS_SS_APP_PGM_STG.ATTRIBUTE19%TYPE;
893 l_attribute20 IGS_SS_APP_PGM_STG.ATTRIBUTE20%TYPE;
894 l_attribute21 IGS_SS_APP_PGM_STG.ATTRIBUTE21%TYPE;
895 l_attribute22 IGS_SS_APP_PGM_STG.ATTRIBUTE22%TYPE;
896 l_attribute23 IGS_SS_APP_PGM_STG.ATTRIBUTE23%TYPE;
897 l_attribute24 IGS_SS_APP_PGM_STG.ATTRIBUTE24%TYPE;
898 l_attribute25 IGS_SS_APP_PGM_STG.ATTRIBUTE25%TYPE;
899 l_attribute26 IGS_SS_APP_PGM_STG.ATTRIBUTE26%TYPE;
900 l_attribute27 IGS_SS_APP_PGM_STG.ATTRIBUTE27%TYPE;
901 l_attribute28 IGS_SS_APP_PGM_STG.ATTRIBUTE28%TYPE;
902 l_attribute29 IGS_SS_APP_PGM_STG.ATTRIBUTE29%TYPE;
903 l_attribute30 IGS_SS_APP_PGM_STG.ATTRIBUTE30%TYPE;
904 l_attribute31 IGS_SS_APP_PGM_STG.ATTRIBUTE31%TYPE;
905 l_attribute32 IGS_SS_APP_PGM_STG.ATTRIBUTE32%TYPE;
906 l_attribute33 IGS_SS_APP_PGM_STG.ATTRIBUTE33%TYPE;
907 l_attribute34 IGS_SS_APP_PGM_STG.ATTRIBUTE34%TYPE;
908 l_attribute35 IGS_SS_APP_PGM_STG.ATTRIBUTE35%TYPE;
909 l_attribute36 IGS_SS_APP_PGM_STG.ATTRIBUTE36%TYPE;
910 l_attribute37 IGS_SS_APP_PGM_STG.ATTRIBUTE37%TYPE;
911 l_attribute38 IGS_SS_APP_PGM_STG.ATTRIBUTE38%TYPE;
912 l_attribute39 IGS_SS_APP_PGM_STG.ATTRIBUTE39%TYPE;
913 l_attribute40 IGS_SS_APP_PGM_STG.ATTRIBUTE40%TYPE;
914 l_ss_admappl_pgm_id IGS_SS_APP_PGM_STG.ss_admappl_pgm_id%TYPE;
915
916 BEGIN
917 SAVEPOINT PRE_CREATE_APPL_INST_pub;
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919 l_msg_index := 0;
920
921 -- Standard call to check for call compatibility.
922 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925
926 -- Initialize message list if p_init_msg_list is set to TRUE.
927 IF FND_API.to_Boolean( p_init_msg_list ) THEN
928 FND_MSG_PUB.initialize;
929 END IF;
930 l_msg_index := igs_ge_msg_stack.count_msg;
931
932 -----------------------
933 -- Validate all the parameters for their length
934 -----------------------
935 --p_ss_adm_appl_id
936 check_length('PERSON_ID', 'IGS_SS_APP_PGM_STG', length(TRUNC(p_ss_adm_appl_id)));
937
938 --p_sch_apl_to_id
939 check_length('SCH_APL_TO_ID', 'IGS_SS_APP_PGM_STG', length(TRUNC(p_sch_apl_to_id)));
940
941 --p_location_cd
942 check_length('LOCATION_CD', 'IGS_SS_APP_PGM_STG', length(p_location_cd));
943
944 --p_attendance_type
945 check_length('ATTENDANCE_TYPE', 'IGS_SS_APP_PGM_STG', length(p_attendance_type));
946
947 --p_attendance_mode
948 check_length('ATTENDANCE_MODE', 'IGS_SS_APP_PGM_STG', length(p_attendance_mode));
949
950 --p_attribute_category
951 check_length('ATTRIBUTE_CATEGORY', 'IGS_SS_APP_PGM_STG', length(p_attribute_category));
952
953 --p_attribute1
954 check_length('ATTRIBUTE1', 'IGS_SS_APP_PGM_STG', length(p_attribute1));
955
956 --p_attribute2
957 check_length('ATTRIBUTE2', 'IGS_SS_APP_PGM_STG', length(p_attribute2));
958
959 --p_attribute3
960 check_length('ATTRIBUTE3', 'IGS_SS_APP_PGM_STG', length(p_attribute3));
961
962 --p_attribute4
963 check_length('ATTRIBUTE4', 'IGS_SS_APP_PGM_STG', length(p_attribute4));
964
965 --p_attribute5
966 check_length('ATTRIBUTE5', 'IGS_SS_APP_PGM_STG', length(p_attribute5));
967
968 --p_attribute6
969 check_length('ATTRIBUTE6', 'IGS_SS_APP_PGM_STG', length(p_attribute6));
970
971 --p_attribute7
972 check_length('ATTRIBUTE7', 'IGS_SS_APP_PGM_STG', length(p_attribute7));
973
974 --p_attribute8
975 check_length('ATTRIBUTE8', 'IGS_SS_APP_PGM_STG', length(p_attribute8));
976
977 --p_attribute9
978 check_length('ATTRIBUTE9', 'IGS_SS_APP_PGM_STG', length(p_attribute9));
979
980 --p_attribute10
981 check_length('ATTRIBUTE10', 'IGS_SS_APP_PGM_STG', length(p_attribute10));
982
983 --p_attribute11
984 check_length('ATTRIBUTE11', 'IGS_SS_APP_PGM_STG', length(p_attribute11));
985
986 --p_attribute12
987 check_length('ATTRIBUTE12', 'IGS_SS_APP_PGM_STG', length(p_attribute12));
988
989 --p_attribute13
990 check_length('ATTRIBUTE13', 'IGS_SS_APP_PGM_STG', length(p_attribute13));
991
992 --p_attribute14
993 check_length('ATTRIBUTE14', 'IGS_SS_APP_PGM_STG', length(p_attribute14));
994
995 --p_attribute15
996 check_length('ATTRIBUTE15', 'IGS_SS_APP_PGM_STG', length(p_attribute15));
997
998 --p_attribute16
999 check_length('ATTRIBUTE16', 'IGS_SS_APP_PGM_STG', length(p_attribute16));
1000
1001 --p_attribute17
1002 check_length('ATTRIBUTE17', 'IGS_SS_APP_PGM_STG', length(p_attribute17));
1003
1004 --p_attribute18
1005 check_length('ATTRIBUTE18', 'IGS_SS_APP_PGM_STG', length(p_attribute18));
1006
1007 --p_attribute19
1008 check_length('ATTRIBUTE19', 'IGS_SS_APP_PGM_STG', length(p_attribute19));
1009
1010 --p_attribute20
1011 check_length('ATTRIBUTE20', 'IGS_SS_APP_PGM_STG', length(p_attribute20));
1012
1013 --p_attribute21
1014 check_length('ATTRIBUTE21', 'IGS_SS_APP_PGM_STG', length(p_attribute21));
1015
1016 --p_attribute22
1017 check_length('ATTRIBUTE22', 'IGS_SS_APP_PGM_STG', length(p_attribute22));
1018
1019 --p_attribute23
1020 check_length('ATTRIBUTE23', 'IGS_SS_APP_PGM_STG', length(p_attribute23));
1021
1022 --p_attribute24
1023 check_length('ATTRIBUTE24', 'IGS_SS_APP_PGM_STG', length(p_attribute24));
1024
1025 --p_attribute25
1026 check_length('ATTRIBUTE25', 'IGS_SS_APP_PGM_STG', length(p_attribute25));
1027
1028 --p_attribute26
1029 check_length('ATTRIBUTE26', 'IGS_SS_APP_PGM_STG', length(p_attribute26));
1030
1031 --p_attribute27
1032 check_length('ATTRIBUTE27', 'IGS_SS_APP_PGM_STG', length(p_attribute27));
1033
1034 --p_attribute28
1035 check_length('ATTRIBUTE28', 'IGS_SS_APP_PGM_STG', length(p_attribute28));
1036
1037 --p_attribute29
1038 check_length('ATTRIBUTE29', 'IGS_SS_APP_PGM_STG', length(p_attribute29));
1039
1040 --p_attribute30
1041 check_length('ATTRIBUTE30', 'IGS_SS_APP_PGM_STG', length(p_attribute30));
1042
1043 --p_attribute31
1044 check_length('ATTRIBUTE31', 'IGS_SS_APP_PGM_STG', length(p_attribute31));
1045
1046 --p_attribute32
1047 check_length('ATTRIBUTE32', 'IGS_SS_APP_PGM_STG', length(p_attribute32));
1048
1049 --p_attribute33
1050 check_length('ATTRIBUTE33', 'IGS_SS_APP_PGM_STG', length(p_attribute33));
1051
1052 --p_attribute34
1053 check_length('ATTRIBUTE34', 'IGS_SS_APP_PGM_STG', length(p_attribute34));
1054
1055 --p_attribute35
1056 check_length('ATTRIBUTE35', 'IGS_SS_APP_PGM_STG', length(p_attribute35));
1057
1058 --p_attribute36
1059 check_length('ATTRIBUTE36', 'IGS_SS_APP_PGM_STG', length(p_attribute36));
1060
1061 --p_attribute37
1062 check_length('ATTRIBUTE37', 'IGS_SS_APP_PGM_STG', length(p_attribute37));
1063
1064 --p_attribute38
1065 check_length('ATTRIBUTE38', 'IGS_SS_APP_PGM_STG', length(p_attribute38));
1066
1067 --p_attribute39
1068 check_length('ATTRIBUTE39', 'IGS_SS_APP_PGM_STG', length(p_attribute39));
1069
1070 --p_attribute40
1071 check_length('ATTRIBUTE40', 'IGS_SS_APP_PGM_STG', length(p_attribute40));
1072
1073 ---------------------
1074 --Intialization of variables to handle G_MISS_CHAR/NUM/DATE
1075 ---------------------
1076
1077 -- p_ss_adm_appl_id
1078 IF p_ss_adm_appl_id = FND_API.G_MISS_NUM OR p_ss_adm_appl_id IS NULL THEN
1079 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRECREATE_PAR_INST_MISS');
1080 IGS_GE_MSG_STACK.ADD;
1081 RAISE FND_API.G_EXC_ERROR;
1082 ELSE
1083 l_ss_adm_appl_id := p_ss_adm_appl_id;
1084 END IF;
1085
1086 -- p_sch_apl_to_id
1087 IF p_sch_apl_to_id = FND_API.G_MISS_NUM THEN
1088 l_sch_apl_to_id := NULL;
1089 ELSE
1090 l_sch_apl_to_id := p_sch_apl_to_id;
1091 END IF;
1092
1093 -- p_location_cd
1094 IF p_location_cd = FND_API.G_MISS_CHAR THEN
1095 l_location_cd := NULL;
1096 ELSE
1097 l_location_cd := p_location_cd;
1098 END IF;
1099
1100 -- p_attendance_type
1101 IF p_attendance_type = FND_API.G_MISS_CHAR THEN
1102 l_attendance_type := NULL;
1103 ELSE
1104 l_attendance_type := p_attendance_type;
1105 END IF;
1106
1107 -- p_attendance_mode
1108 IF p_attendance_mode = FND_API.G_MISS_CHAR THEN
1109 l_attendance_mode := NULL;
1110 ELSE
1111 l_attendance_mode := p_attendance_mode;
1112 END IF;
1113
1114 -- p_attribute_category
1115 IF p_attribute_category = FND_API.G_MISS_CHAR THEN
1116 l_attribute_category := NULL;
1117 ELSE
1118 l_attribute_category := p_attribute_category;
1119 END IF;
1120
1121 -- p_attribute1
1122 IF p_attribute1 = FND_API.G_MISS_CHAR THEN
1123 l_attribute1 := NULL;
1124 ELSE
1125 l_attribute1 := p_attribute1;
1126 END IF;
1127
1128 -- p_attribute2
1129 IF p_attribute2 = FND_API.G_MISS_CHAR THEN
1130 l_attribute2 := NULL;
1131 ELSE
1132 l_attribute2 := p_attribute2;
1133 END IF;
1134
1135 -- p_attribute3
1136 IF p_attribute3 = FND_API.G_MISS_CHAR THEN
1137 l_attribute3 := NULL;
1138 ELSE
1139 l_attribute3 := p_attribute3;
1140 END IF;
1141
1142 -- p_attribute4
1143 IF p_attribute4 = FND_API.G_MISS_CHAR THEN
1144 l_attribute4 := NULL;
1145 ELSE
1146 l_attribute4 := p_attribute4;
1147 END IF;
1148
1149 -- p_attribute5
1150 IF p_attribute5 = FND_API.G_MISS_CHAR THEN
1151 l_attribute5 := NULL;
1152 ELSE
1153 l_attribute5 := p_attribute5;
1154 END IF;
1155
1156 -- p_attribute6
1157 IF p_attribute6 = FND_API.G_MISS_CHAR THEN
1158 l_attribute6 := NULL;
1159 ELSE
1160 l_attribute6 := p_attribute6;
1161 END IF;
1162
1163 -- p_attribute7
1164 IF p_attribute7 = FND_API.G_MISS_CHAR THEN
1165 l_attribute7 := NULL;
1166 ELSE
1167 l_attribute7 := p_attribute7;
1168 END IF;
1169
1170 -- p_attribute8
1171 IF p_attribute8 = FND_API.G_MISS_CHAR THEN
1172 l_attribute8 := NULL;
1173 ELSE
1174 l_attribute8 := p_attribute8;
1175 END IF;
1176
1177 -- p_attribute9
1178 IF p_attribute9 = FND_API.G_MISS_CHAR THEN
1179 l_attribute9 := NULL;
1180 ELSE
1181 l_attribute9 := p_attribute9;
1182 END IF;
1183
1184 -- p_attribute10
1185 IF p_attribute10 = FND_API.G_MISS_CHAR THEN
1186 l_attribute10 := NULL;
1187 ELSE
1188 l_attribute10 := p_attribute10;
1189 END IF;
1190
1191 -- p_attribute11
1192 IF p_attribute11 = FND_API.G_MISS_CHAR THEN
1193 l_attribute11 := NULL;
1194 ELSE
1195 l_attribute11 := p_attribute11;
1196 END IF;
1197
1198 -- p_attribute12
1199 IF p_attribute12 = FND_API.G_MISS_CHAR THEN
1200 l_attribute12 := NULL;
1201 ELSE
1202 l_attribute12 := p_attribute12;
1203 END IF;
1204
1205 -- p_attribute13
1206 IF p_attribute13 = FND_API.G_MISS_CHAR THEN
1207 l_attribute13 := NULL;
1208 ELSE
1209 l_attribute13 := p_attribute13;
1210 END IF;
1211
1212 -- p_attribute14
1213 IF p_attribute14 = FND_API.G_MISS_CHAR THEN
1214 l_attribute14 := NULL;
1215 ELSE
1216 l_attribute14 := p_attribute14;
1217 END IF;
1218
1219 -- p_attribute14
1220 IF p_attribute15 = FND_API.G_MISS_CHAR THEN
1221 l_attribute15 := NULL;
1222 ELSE
1223 l_attribute15 := p_attribute15;
1224 END IF;
1225
1226 -- p_attribute16
1227 IF p_attribute16 = FND_API.G_MISS_CHAR THEN
1228 l_attribute16 := NULL;
1229 ELSE
1230 l_attribute16 := p_attribute16;
1231 END IF;
1232
1233 -- p_attribute17
1234 IF p_attribute17 = FND_API.G_MISS_CHAR THEN
1235 l_attribute17 := NULL;
1236 ELSE
1237 l_attribute17 := p_attribute17;
1238 END IF;
1239
1240 -- p_attribute18
1241 IF p_attribute18 = FND_API.G_MISS_CHAR THEN
1242 l_attribute18 := NULL;
1243 ELSE
1244 l_attribute18 := p_attribute18;
1245 END IF;
1246
1247 -- p_attribute19
1248 IF p_attribute19 = FND_API.G_MISS_CHAR THEN
1249 l_attribute19 := NULL;
1250 ELSE
1251 l_attribute19 := p_attribute19;
1252 END IF;
1253
1254 -- p_attribute20
1255 IF p_attribute20 = FND_API.G_MISS_CHAR THEN
1256 l_attribute20 := NULL;
1257 ELSE
1258 l_attribute20 := p_attribute20;
1259 END IF;
1260
1261 -- p_attribute21
1262 IF p_attribute21 = FND_API.G_MISS_CHAR THEN
1263 l_attribute21 := NULL;
1264 ELSE
1265 l_attribute21 := p_attribute21;
1266 END IF;
1267
1268 -- p_attribute22
1269 IF p_attribute22 = FND_API.G_MISS_CHAR THEN
1270 l_attribute22 := NULL;
1271 ELSE
1272 l_attribute22 := p_attribute22;
1273 END IF;
1274
1275 -- p_attribute23
1276 IF p_attribute23 = FND_API.G_MISS_CHAR THEN
1277 l_attribute23 := NULL;
1278 ELSE
1279 l_attribute23 := p_attribute23;
1280 END IF;
1281
1282 -- p_attribute24
1283 IF p_attribute24 = FND_API.G_MISS_CHAR THEN
1284 l_attribute24 := NULL;
1285 ELSE
1286 l_attribute24 := p_attribute24;
1287 END IF;
1288
1289 -- p_attribute25
1290 IF p_attribute25 = FND_API.G_MISS_CHAR THEN
1291 l_attribute25 := NULL;
1292 ELSE
1293 l_attribute25 := p_attribute25;
1294 END IF;
1295
1296 -- p_attribute26
1297 IF p_attribute26 = FND_API.G_MISS_CHAR THEN
1298 l_attribute26 := NULL;
1299 ELSE
1300 l_attribute26 := p_attribute26;
1301 END IF;
1302
1303 -- p_attribute27
1304 IF p_attribute27 = FND_API.G_MISS_CHAR THEN
1305 l_attribute27 := NULL;
1306 ELSE
1307 l_attribute27 := p_attribute27;
1308 END IF;
1309
1310 -- p_attribute28
1311 IF p_attribute28 = FND_API.G_MISS_CHAR THEN
1312 l_attribute28 := NULL;
1313 ELSE
1314 l_attribute28 := p_attribute28;
1315 END IF;
1316
1317 -- p_attribute29
1318 IF p_attribute29 = FND_API.G_MISS_CHAR THEN
1319 l_attribute29 := NULL;
1320 ELSE
1321 l_attribute29 := p_attribute29;
1322 END IF;
1323
1324 -- p_attribute30
1325 IF p_attribute30 = FND_API.G_MISS_CHAR THEN
1326 l_attribute30 := NULL;
1327 ELSE
1328 l_attribute30 := p_attribute30;
1329 END IF;
1330
1331 -- p_attribute31
1332 IF p_attribute31 = FND_API.G_MISS_CHAR THEN
1333 l_attribute31 := NULL;
1334 ELSE
1335 l_attribute31 := p_attribute31;
1336 END IF;
1337
1338 -- p_attribute32
1339 IF p_attribute32 = FND_API.G_MISS_CHAR THEN
1340 l_attribute32 := NULL;
1341 ELSE
1342 l_attribute32 := p_attribute32;
1343 END IF;
1344
1345 -- p_attribute33
1346 IF p_attribute33 = FND_API.G_MISS_CHAR THEN
1347 l_attribute33 := NULL;
1348 ELSE
1349 l_attribute33 := p_attribute33;
1350 END IF;
1351
1352 -- p_attribute34
1353 IF p_attribute34 = FND_API.G_MISS_CHAR THEN
1354 l_attribute34 := NULL;
1355 ELSE
1356 l_attribute34 := p_attribute34;
1357 END IF;
1358
1359 -- p_attribute35
1360 IF p_attribute35 = FND_API.G_MISS_CHAR THEN
1361 l_attribute35 := NULL;
1362 ELSE
1363 l_attribute35 := p_attribute35;
1364 END IF;
1365
1366 -- p_attribute36
1367 IF p_attribute36 = FND_API.G_MISS_CHAR THEN
1368 l_attribute36 := NULL;
1369 ELSE
1370 l_attribute36 := p_attribute36;
1371 END IF;
1372
1373 -- p_attribute37
1374 IF p_attribute37 = FND_API.G_MISS_CHAR THEN
1375 l_attribute37 := NULL;
1376 ELSE
1377 l_attribute37 := p_attribute37;
1378 END IF;
1379
1380 -- p_attribute38
1381 IF p_attribute38 = FND_API.G_MISS_CHAR THEN
1382 l_attribute38 := NULL;
1383 ELSE
1384 l_attribute38 := p_attribute38;
1385 END IF;
1386
1387 -- p_attribute39
1388 IF p_attribute39 = FND_API.G_MISS_CHAR THEN
1389 l_attribute39 := NULL;
1390 ELSE
1391 l_attribute39 := p_attribute39;
1392 END IF;
1393
1394 -- p_attribute40
1395 IF p_attribute40 = FND_API.G_MISS_CHAR THEN
1396 l_attribute40 := NULL;
1397 ELSE
1398 l_attribute40 := p_attribute40;
1399 END IF;
1400
1401 ---------------------------
1402 -- Validate the values of the parameters passed in the API
1403 ---------------------------
1404
1405 -- validate p_ss_adm_appl_id
1406 -- The application id should exist in the parent Table-IGS_SS_ADM_APPL_STG
1407 OPEN c_admappl(l_ss_adm_appl_id);
1408 FETCH c_admappl INTO l_admappl_rec;
1409 CLOSE c_admappl;
1410
1411 IF l_admappl_rec.ss_adm_appl_id IS NULL THEN
1412 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1413 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_APPL_ID'));
1414 IGS_GE_MSG_STACK.ADD;
1415 RAISE FND_API.G_EXC_ERROR;
1416 END IF;
1417
1418 ---------------------
1419 -- Derive Person Id from the application Type
1420 ---------------------
1421 l_person_id := l_admappl_rec.person_id;
1422
1423
1424 /*
1425 For the parameters p_sch_apl_to_id, p_location_cd, p_attendance_type and p_attendance_mode there will be 2 kinds of validations:
1426 a) That the individual values exist in OSS Tables IGS_AD_SCHL_APLY_TO, IGS_AD_LOCATION, IGS_EN_ATD_MODE, IGS_EN_ATD_TYPE resp.
1427 b) That the combination of these parameter values is a part of a valid Offering Option corresponding to this Application's Calendar and Application Type.
1428 */
1429
1430 -- validate p_sch_apl_to_id
1431 IF l_sch_apl_to_id IS NOT NULL THEN
1432 OPEN c_sch_apl_to(l_sch_apl_to_id);
1433 FETCH c_sch_apl_to INTO l_sch_apl_to_rec;
1434 CLOSE c_sch_apl_to;
1435
1436 IF l_sch_apl_to_rec IS NULL THEN
1437 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1438 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCH_APPLY_TO'));
1439 IGS_GE_MSG_STACK.ADD;
1440 RAISE FND_API.G_EXC_ERROR;
1441 END IF;
1442 END IF;
1443
1444 -- validate l_location_cd
1445 IF l_location_cd IS NOT NULL THEN
1446 OPEN c_location(l_location_cd);
1447 FETCH c_location INTO l_location_rec;
1448 CLOSE c_location;
1449
1450 IF l_location_rec IS NULL THEN
1451 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LOC_CD_INV');
1452 IGS_GE_MSG_STACK.ADD;
1453 RAISE FND_API.G_EXC_ERROR;
1454 END IF;
1455 END IF;
1456
1457 -- validate p_attendance_type
1458 IF l_attendance_type IS NOT NULL THEN
1459 OPEN c_att_type(l_attendance_type);
1460 FETCH c_att_type INTO l_att_type_rec;
1461 CLOSE c_att_type;
1462
1463 IF l_att_type_rec IS NULL THEN
1464 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1465 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ATTENDANCE_TYPE'));
1466 IGS_GE_MSG_STACK.ADD;
1467 RAISE FND_API.G_EXC_ERROR;
1468 END IF;
1469 END IF;
1470
1471 -- validate p_attendance_mode
1472 IF l_attendance_mode IS NOT NULL THEN
1473 OPEN c_att_mode(l_attendance_mode);
1474 FETCH c_att_mode INTO l_att_mode_rec;
1475 CLOSE c_att_mode;
1476
1477 IF l_att_mode_rec IS NULL THEN
1478 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1479 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ATTENDANCE_MODE'));
1480 IGS_GE_MSG_STACK.ADD;
1481 RAISE FND_API.G_EXC_ERROR;
1482 END IF;
1483 END IF;
1484
1485 -- validate p_location_cd, p_attendance_type and p_attendance_mode combination
1486 IF l_location_cd IS NULL AND l_attendance_type IS NULL AND l_attendance_mode IS NULL THEN
1487 FND_MESSAGE.SET_NAME('IGS','IGS_AD_COMB_NOT_NULL');
1488 IGS_GE_MSG_STACK.ADD;
1489 RAISE FND_API.G_EXC_ERROR;
1490 END IF;
1491
1492 OPEN c_off_pattern(l_admappl_rec.admission_cat, l_admappl_rec.s_adm_process_type, l_location_cd, l_attendance_type,l_attendance_mode);
1493 FETCH c_off_pattern INTO l_off_pattern_rec;
1494 CLOSE c_off_pattern;
1495
1496 IF l_off_pattern_rec IS NULL THEN
1497 FND_MESSAGE.SET_NAME('IGS','IGS_AD_COMB_NOT_EXIST');
1498 IGS_GE_MSG_STACK.ADD;
1499 RAISE FND_API.G_EXC_ERROR;
1500 END IF;
1501
1502 -- Validate/ Derive Preference Number
1503 OPEN c_pref_limit(l_admappl_rec.admission_cat, l_admappl_rec.s_adm_process_type);
1504 FETCH c_pref_limit INTO l_pref_limit_rec;
1505 CLOSE c_pref_limit;
1506
1507 OPEN c_prog_inst(l_ss_adm_appl_id);
1508 FETCH c_prog_inst INTO l_prog_inst_rec;
1509 CLOSE c_prog_inst;
1510
1511 IF l_pref_limit_rec IS NULL THEN -- Preference Limit APC step is not set
1512 l_preference_number := NULL;
1513 ELSE -- Preference Limit APC Step is set
1514 IF l_prog_inst_rec IS NULL THEN -- Another Program Instance for same Parent Application does not exists
1515 l_preference_number := 1;
1516 ELSE
1517 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROG_PREF_EXIST');
1518 IGS_GE_MSG_STACK.ADD;
1519 RAISE FND_API.G_EXC_ERROR;
1520 END IF;
1521 END IF;
1522
1523 -- Check for correctness of Descriptive FlexField values
1524 IF NOT IGS_AD_IMP_018.validate_desc_flex_40_cols(
1525 l_attribute_category,
1526 l_attribute1,
1527 l_attribute2,
1528 l_attribute3,
1529 l_attribute4,
1530 l_attribute5,
1531 l_attribute6,
1532 l_attribute7,
1533 l_attribute8,
1534 l_attribute9,
1535 l_attribute10,
1536 l_attribute11,
1537 l_attribute12,
1538 l_attribute13,
1539 l_attribute14,
1540 l_attribute15,
1541 l_attribute16,
1542 l_attribute17,
1543 l_attribute18,
1544 l_attribute19,
1545 l_attribute20,
1546 l_attribute21,
1547 l_attribute22,
1548 l_attribute23,
1549 l_attribute24,
1550 l_attribute25,
1551 l_attribute26,
1552 l_attribute27,
1553 l_attribute28,
1554 l_attribute29,
1555 l_attribute30,
1556 l_attribute31,
1557 l_attribute32,
1558 l_attribute33,
1559 l_attribute34,
1560 l_attribute35,
1561 l_attribute36,
1562 l_attribute37,
1563 l_attribute38,
1564 l_attribute39,
1565 l_attribute40,
1566 'IGS_AD_APPL_INST_FLEX'
1567 ) THEN
1568 FND_MESSAGE.SET_NAME('IGS','IGS_AD_INVALID_DESC_FLEX');
1569 IGS_GE_MSG_STACK.ADD;
1570 RAISE FND_API.G_EXC_ERROR;
1571 END IF;
1572
1573 ---------------------
1574 -- Insert the Application Instance Record.
1575 ---------------------
1576
1577 INSERT INTO IGS_SS_APP_PGM_STG (SS_ADMAPPL_PGM_ID,
1578 SS_ADM_APPL_ID,
1579 PERSON_ID,
1580 PREFERENCE_NUMBER,
1581 SCH_APL_TO_ID,
1582 LOCATION_CD,
1583 ATTENDANCE_TYPE,
1584 ATTENDANCE_MODE,
1585 ATTRIBUTE_CATEGORY,
1586 ATTRIBUTE1,
1587 ATTRIBUTE2,
1588 ATTRIBUTE3,
1589 ATTRIBUTE4,
1590 ATTRIBUTE5,
1591 ATTRIBUTE6,
1592 ATTRIBUTE7,
1593 ATTRIBUTE8,
1594 ATTRIBUTE9,
1595 ATTRIBUTE10,
1596 ATTRIBUTE11,
1597 ATTRIBUTE12,
1598 ATTRIBUTE13,
1599 ATTRIBUTE14,
1600 ATTRIBUTE15,
1601 ATTRIBUTE16,
1602 ATTRIBUTE17,
1603 ATTRIBUTE18,
1604 ATTRIBUTE19,
1605 ATTRIBUTE20,
1606 ATTRIBUTE21,
1607 ATTRIBUTE22,
1608 ATTRIBUTE23,
1609 ATTRIBUTE24,
1610 ATTRIBUTE25,
1611 ATTRIBUTE26,
1612 ATTRIBUTE27,
1613 ATTRIBUTE28,
1614 ATTRIBUTE29,
1615 ATTRIBUTE30,
1616 ATTRIBUTE31,
1617 ATTRIBUTE32,
1618 ATTRIBUTE33,
1619 ATTRIBUTE34,
1620 ATTRIBUTE35,
1621 ATTRIBUTE36,
1622 ATTRIBUTE37,
1623 ATTRIBUTE38,
1624 ATTRIBUTE39,
1625 ATTRIBUTE40,
1626 LAST_UPDATE_DATE,
1627 LAST_UPDATED_BY,
1628 CREATION_DATE,
1629 CREATED_BY,
1630 LAST_UPDATE_LOGIN)
1631 VALUES (IGS_SS_ADMAPPL_PGM_S.NEXTVAL,
1632 l_ss_adm_appl_id,
1633 l_person_id,
1634 l_preference_number,
1635 l_sch_apl_to_id,
1636 l_location_cd,
1637 l_attendance_type,
1638 l_attendance_mode,
1639 l_attribute_category,
1640 l_attribute1,
1641 l_attribute2,
1642 l_attribute3,
1643 l_attribute4,
1644 l_attribute5,
1645 l_attribute6,
1646 l_attribute7,
1647 l_attribute8,
1648 l_attribute9,
1649 l_attribute10,
1650 l_attribute11,
1651 l_attribute12,
1652 l_attribute13,
1653 l_attribute14,
1654 l_attribute15,
1655 l_attribute16,
1656 l_attribute17,
1657 l_attribute18,
1658 l_attribute19,
1659 l_attribute20,
1660 l_attribute21,
1661 l_attribute22,
1662 l_attribute23,
1663 l_attribute24,
1664 l_attribute25,
1665 l_attribute26,
1666 l_attribute27,
1667 l_attribute28,
1668 l_attribute29,
1669 l_attribute30,
1670 l_attribute31,
1671 l_attribute32,
1672 l_attribute33,
1673 l_attribute34,
1674 l_attribute35,
1675 l_attribute36,
1676 l_attribute37,
1677 l_attribute38,
1678 l_attribute39,
1679 l_attribute40,
1680 SYSDATE,
1681 FND_GLOBAL.USER_ID,
1682 SYSDATE,
1683 FND_GLOBAL.USER_ID,
1684 FND_GLOBAL.USER_ID) RETURNING SS_ADMAPPL_PGM_ID INTO x_ss_admappl_pgm_id;
1685
1686 IF FND_API.To_Boolean( p_commit ) THEN
1687 COMMIT;
1688 END IF;
1689
1690 -- Exception Handling
1691 EXCEPTION
1692 WHEN FND_API.G_EXC_ERROR THEN
1693 ROLLBACK TO PRE_CREATE_APPL_INST_pub;
1694 igs_ad_gen_016.extract_msg_from_stack (
1695 p_msg_at_index => l_msg_index,
1696 p_return_status => l_return_status,
1697 p_msg_count => x_msg_count,
1698 p_msg_data => x_msg_data,
1699 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1700
1701 IF l_hash_msg_name_text_type_tab(x_msg_count - 2).name <> 'ORA' THEN
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 ELSE
1704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1705 END IF;
1706
1707 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
1708 x_msg_count := x_msg_count-1;
1709
1710
1711 WHEN OTHERS THEN
1712 ROLLBACK TO PRE_CREATE_APPL_INST_pub;
1713 igs_ad_gen_016.extract_msg_from_stack (
1714 p_msg_at_index => l_msg_index,
1715 p_return_status => l_return_status,
1716 p_msg_count => x_msg_count,
1717 p_msg_data => x_msg_data,
1718 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1719
1720 IF l_hash_msg_name_text_type_tab(x_msg_count - 1).name <> 'ORA' THEN
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 ELSE
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 END IF;
1725
1726 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-1).text;
1727
1728 END PRE_CREATE_APPLICATION_INST;
1729
1730 PROCEDURE INSERT_STG_FEE_REQ_DET (
1731
1732 p_api_version IN NUMBER, -- standard Public API IN params
1733 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1734 p_commit IN VARCHAR2 default FND_API.G_FALSE,
1735 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1736 x_return_status OUT NOCOPY VARCHAR2, -- standard Public API OUT params
1737 x_msg_count OUT NOCOPY NUMBER,
1738 x_msg_data OUT NOCOPY VARCHAR2,
1739 p_SS_ADM_APPL_ID IN NUMBER, -- Staging table related params
1740 p_PERSON_ID IN NUMBER,
1741 p_APPLICANT_FEE_TYPE IN NUMBER,
1742 p_APPLICANT_FEE_STATUS IN NUMBER,
1743 p_FEE_DATE IN DATE,
1744 p_FEE_PAYMENT_METHOD IN NUMBER,
1745 p_FEE_AMOUNT IN NUMBER,
1746 p_REFERENCE_NUM IN VARCHAR2
1747 ) AS
1748 /*************************************************************
1749 Created By : Pranay Fotedar
1750 Date Created By : 28-Apr-2006
1751 Purpose : Creation of Fee Records on submission
1752 Change History
1753 Who When What
1754 pfotedar 2006/05/05 Added Validation for Fee Payment Method
1755 (reverse chronological order - newest change first)
1756 ***************************************************************/
1757
1758 l_api_version CONSTANT NUMBER := '1.0';
1759 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_STG_FEE_REQ_DET';
1760 l_msg_index NUMBER;
1761 l_return_status VARCHAR2(1);
1762 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1763
1764 CURSOR C_FEE_TYPE(p_APPL_FEE_TYPE IGS_AD_CODE_CLASSES.CODE_ID%TYPE) IS
1765 SELECT CC.CODE_ID
1766 FROM IGS_AD_CODE_CLASSES CC
1767 WHERE CC.CODE_ID = p_APPL_FEE_TYPE
1768 AND CC.CLASS = 'SYS_FEE_TYPE'
1769 AND NVL(CLOSED_IND,'N') = 'N'
1770 AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
1771
1772 CURSOR C_FEE_STATUS(p_APPL_FEE_STATUS IGS_AD_CODE_CLASSES.CODE_ID%TYPE) IS
1773 SELECT CC.CODE_ID
1774 FROM IGS_AD_CODE_CLASSES CC
1775 WHERE CC.CODE_ID = p_APPL_FEE_STATUS
1776 AND CC.CLASS = 'SYS_FEE_STATUS'
1777 AND NVL(CLOSED_IND,'N') = 'N'
1778 AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
1779
1780
1781 CURSOR C_FEE_PAYMENT_METHOD(p_APPL_FEE_PAY_MET IGS_AD_CODE_CLASSES.CODE_ID%TYPE) IS
1782 SELECT CC.CODE_ID
1783 FROM IGS_AD_CODE_CLASSES CC
1784 WHERE CC.CODE_ID = p_APPL_FEE_PAY_MET
1785 AND CC.CLASS = 'SYS_FEE_PAY_METHOD'
1786 AND NVL(CLOSED_IND,'N') = 'N'
1787 AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
1788
1789 l_APPL_FEE_TYPE_REC C_FEE_TYPE%ROWTYPE;
1790 l_APPL_FEE_STATUS_REC C_FEE_STATUS%ROWTYPE;
1791 l_APPL_FEE_PAY_MET_REC C_FEE_PAYMENT_METHOD%ROWTYPE;
1792
1793
1794 l_SS_ADM_APPL_ID IGS_SS_APP_REQ_STG.SS_ADM_APPL_ID%TYPE;
1795 l_PERSON_ID IGS_SS_APP_REQ_STG.PERSON_ID%TYPE;
1796 l_APPLICANT_FEE_TYPE IGS_SS_APP_REQ_STG.APPLICANT_FEE_TYPE%TYPE;
1797 l_APPLICANT_FEE_STATUS IGS_SS_APP_REQ_STG.APPLICANT_FEE_STATUS%TYPE;
1798 l_FEE_DATE IGS_SS_APP_REQ_STG.FEE_DATE%TYPE;
1799 l_FEE_PAYMENT_METHOD IGS_SS_APP_REQ_STG.FEE_PAYMENT_METHOD%TYPE;
1800 l_FEE_AMOUNT IGS_SS_APP_REQ_STG.FEE_AMOUNT%TYPE;
1801 l_REFERENCE_NUM IGS_SS_APP_REQ_STG.REFERENCE_NUM%TYPE;
1802
1803 BEGIN
1804 -- Standard begin of API savepoint
1805 SAVEPOINT INSERT_STG_FEE_REQ_DET_PUB;
1806
1807 -- Initialize API return status to success
1808 x_return_status := FND_API.G_RET_STS_SUCCESS;
1809 l_msg_index := 0;
1810
1811 -- Standard call to check for call compatibility.
1812 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1813 THEN
1814 RAISE FND_API.G_EXC_ERROR;
1815 END IF;
1816
1817 -- Check p_init_msg_list
1818 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1819 FND_MSG_PUB.initialize;
1820 END IF;
1821 l_msg_index := igs_ge_msg_stack.count_msg;
1822
1823
1824 -----------------------
1825 -- Validate all the parameters for their length
1826 -----------------------
1827 -- p_SS_ADM_APPL_ID
1828 check_length('SS_ADM_APPL_ID', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_SS_ADM_APPL_ID)));
1829 -- p_PERSON_ID
1830 check_length('PERSON_ID', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_PERSON_ID)));
1831 -- p_APPLICANT_FEE_TYPE
1832 check_length('APPLICANT_FEE_TYPE', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_APPLICANT_FEE_TYPE)));
1833 -- p_APPLICANT_FEE_STATUS
1834 check_length('APPLICANT_FEE_STATUS', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_APPLICANT_FEE_STATUS)));
1835 -- p_FEE_PAYMENT_METHOD
1836 check_length('FEE_PAYMENT_METHOD', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_FEE_PAYMENT_METHOD)));
1837 -- p_FEE_AMOUNT
1838 check_length('FEE_AMOUNT', 'IGS_SS_APP_REQ_STG', length(TRUNC(p_FEE_AMOUNT)));
1839 -- p_REFERENCE_NUM
1840 check_length('REFERENCE_NUM', 'IGS_SS_APP_REQ_STG', length(p_REFERENCE_NUM));
1841 ------------------------
1842 -- End of parameter-length validations.
1843 ------------------------
1844
1845 ------------------------
1846 --Intialization of variables to handle G_MISS_CHAR/NUM/DATE
1847 ------------------------
1848
1849 IF p_SS_ADM_APPL_ID = FND_API.G_MISS_NUM OR p_SS_ADM_APPL_ID IS NULL THEN
1850 FND_MESSAGE.SET_NAME('IGS','IGS_AD_STG_FEE_PARAM_MISSING'); --error message
1851 IGS_GE_MSG_STACK.ADD;
1852 RAISE FND_API.G_EXC_ERROR;
1853 ELSE
1854 l_SS_ADM_APPL_ID := p_SS_ADM_APPL_ID;
1855 END IF;
1856
1857 IF p_PERSON_ID = FND_API.G_MISS_NUM OR p_PERSON_ID IS NULL THEN
1858 FND_MESSAGE.SET_NAME('IGS','IGS_AD_STG_FEE_PARAM_MISSING'); --error message
1859 IGS_GE_MSG_STACK.ADD;
1860 RAISE FND_API.G_EXC_ERROR;
1861 ELSE
1862 l_PERSON_ID := p_PERSON_ID;
1863 END IF;
1864
1865 IF p_APPLICANT_FEE_TYPE = FND_API.G_MISS_NUM THEN
1866 l_APPLICANT_FEE_TYPE := NULL;
1867 ELSE
1868 l_APPLICANT_FEE_TYPE := p_APPLICANT_FEE_TYPE;
1869 END IF;
1870
1871 IF p_APPLICANT_FEE_STATUS = FND_API.G_MISS_NUM THEN
1872 l_APPLICANT_FEE_STATUS := NULL;
1873 ELSE
1874 l_APPLICANT_FEE_STATUS := p_APPLICANT_FEE_STATUS;
1875 END IF;
1876
1877 IF p_FEE_DATE = FND_API.G_MISS_DATE THEN
1878 l_FEE_DATE := NULL;
1879 ELSE
1880 l_FEE_DATE := p_FEE_DATE;
1881 END IF;
1882
1883 IF p_FEE_PAYMENT_METHOD = FND_API.G_MISS_NUM THEN
1884 l_FEE_PAYMENT_METHOD := NULL;
1885 ELSE
1886 l_FEE_PAYMENT_METHOD := p_FEE_PAYMENT_METHOD;
1887 END IF;
1888
1889 IF p_FEE_AMOUNT = FND_API.G_MISS_NUM THEN
1890 l_FEE_AMOUNT := NULL;
1891 ELSE
1892 l_FEE_AMOUNT := p_FEE_AMOUNT;
1893 END IF;
1894
1895 IF p_REFERENCE_NUM = FND_API.G_MISS_CHAR THEN
1896 l_REFERENCE_NUM := NULL;
1897 ELSE
1898 l_REFERENCE_NUM := p_REFERENCE_NUM;
1899 END IF;
1900
1901 ------------------------
1902 -- End of intialization of variables to handle G_MISS_CHAR/NUM/DATE
1903 ------------------------
1904
1905 -- When fee type, fee status, fee date and fee amount are null
1906 IF l_APPLICANT_FEE_TYPE IS NULL OR l_APPLICANT_FEE_STATUS IS NULL
1907 OR l_FEE_DATE IS NULL OR l_FEE_AMOUNT IS NULL
1908 THEN
1909 FND_MESSAGE.SET_NAME('IGS','IGS_SS_AD_FEE_NTNULL'); --error message
1910 IGS_GE_MSG_STACK.ADD;
1911 RAISE FND_API.G_EXC_ERROR;
1912 END IF;
1913
1914 -- check for applicant_fee_type values
1915 IF l_APPLICANT_FEE_TYPE IS NOT NULL THEN
1916 OPEN C_FEE_TYPE(l_APPLICANT_FEE_TYPE);
1917 FETCH C_FEE_TYPE INTO l_APPL_FEE_TYPE_REC;
1918 IF (C_FEE_TYPE%NOTFOUND) THEN
1919 CLOSE C_FEE_TYPE;
1920 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1921 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPLICANT_FEE_TYPE'));
1922 IGS_GE_MSG_STACK.ADD;
1923 RAISE FND_API.G_EXC_ERROR; --error message
1924 END IF;
1925 CLOSE C_FEE_TYPE;
1926 END IF;
1927
1928 -- check for applicant_fee_status values
1929 IF l_APPLICANT_FEE_STATUS IS NOT NULL THEN
1930 OPEN C_FEE_STATUS(l_APPLICANT_FEE_STATUS);
1931 FETCH C_FEE_STATUS INTO l_APPL_FEE_STATUS_REC;
1932 IF (C_FEE_STATUS%NOTFOUND) THEN
1933 CLOSE C_FEE_STATUS;
1934 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1935 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPLICANT_FEE_STAT'));
1936 IGS_GE_MSG_STACK.ADD;
1937 RAISE FND_API.G_EXC_ERROR; --error message
1938 END IF;
1939 CLOSE C_FEE_STATUS;
1940 END IF;
1941
1942 -- check for fee_payment_method values
1943 IF l_FEE_PAYMENT_METHOD IS NOT NULL THEN
1944 OPEN C_FEE_PAYMENT_METHOD(l_FEE_PAYMENT_METHOD);
1945 FETCH C_FEE_PAYMENT_METHOD INTO l_APPL_FEE_PAY_MET_REC;
1946 IF (C_FEE_PAYMENT_METHOD%NOTFOUND) THEN
1947 CLOSE C_FEE_PAYMENT_METHOD;
1948 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
1949 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FEE_PAY_METHOD'));
1950 IGS_GE_MSG_STACK.ADD;
1951 RAISE FND_API.G_EXC_ERROR; --error message
1952 END IF;
1953 CLOSE C_FEE_PAYMENT_METHOD;
1954 END IF;
1955
1956 -- if Fee Amount is negative
1957 IF l_FEE_AMOUNT IS NOT NULL AND l_FEE_AMOUNT < 0 THEN
1958 FND_MESSAGE.SET_NAME('IGS','IGS_AD_FEE_AMT_NON_NEGATIVE'); --error message
1959 IGS_GE_MSG_STACK.ADD;
1960 RAISE FND_API.G_EXC_ERROR;
1961 END IF;
1962
1963 -- if Fee Date is greater than sysdate
1964 IF l_FEE_DATE IS NOT NULL AND l_FEE_DATE > SYSDATE THEN
1965 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DATE_SYSDATE'); --error message
1966 FND_MESSAGE.SET_TOKEN ('NAME',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FEE_DATE'));
1967 IGS_GE_MSG_STACK.ADD;
1968 RAISE FND_API.G_EXC_ERROR;
1969 END IF;
1970
1971 INSERT INTO IGS_SS_APP_REQ_STG (
1972 SS_APP_REQ_ID,
1973 SS_ADM_APPL_ID,
1974 PERSON_ID,
1975 APPLICANT_FEE_TYPE,
1976 APPLICANT_FEE_STATUS,
1977 FEE_DATE,
1978 FEE_PAYMENT_METHOD,
1979 FEE_AMOUNT,
1980 LAST_UPDATE_DATE,
1981 LAST_UPDATED_BY,
1982 CREATION_DATE,
1983 CREATED_BY,
1984 LAST_UPDATE_LOGIN,
1985 REFERENCE_NUM
1986 )
1987 VALUES
1988 (
1989 IGS_SS_APP_REQ_STG_S.NEXTVAL,
1990 l_SS_ADM_APPL_ID,
1991 l_PERSON_ID,
1992 l_APPLICANT_FEE_TYPE,
1993 l_APPLICANT_FEE_STATUS,
1994 l_FEE_DATE,
1995 l_FEE_PAYMENT_METHOD,
1996 l_FEE_AMOUNT,
1997 SYSDATE,
1998 FND_GLOBAL.USER_ID,
1999 SYSDATE,
2000 FND_GLOBAL.USER_ID,
2001 FND_GLOBAL.USER_ID,
2002 l_REFERENCE_NUM
2003 );
2004
2005 IF FND_API.To_Boolean( p_commit ) THEN
2006 COMMIT WORK;
2007 END IF;
2008 -- Get message count and if 1, return message data.
2009 FND_MSG_PUB.Count_And_Get
2010 ( p_count => x_msg_count ,
2011 p_data => x_msg_data
2012 );
2013
2014
2015 EXCEPTION
2016 WHEN FND_API.G_EXC_ERROR THEN
2017
2018 ROLLBACK TO INSERT_STG_FEE_REQ_DET_PUB;
2019 igs_ad_gen_016.extract_msg_from_stack (
2020 p_msg_at_index => l_msg_index,
2021 p_return_status => l_return_status,
2022 p_msg_count => x_msg_count,
2023 p_msg_data => x_msg_data,
2024 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2025
2026 IF l_hash_msg_name_text_type_tab(x_msg_count - 2).name <> 'ORA' THEN
2027 x_return_status := FND_API.G_RET_STS_ERROR;
2028 ELSE
2029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2030 END IF;
2031
2032 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
2033 x_msg_count := x_msg_count-1;
2034
2035 WHEN OTHERS THEN
2036
2037 ROLLBACK TO INSERT_STG_FEE_REQ_DET_PUB;
2038 igs_ad_gen_016.extract_msg_from_stack (
2039 p_msg_at_index => l_msg_index,
2040 p_return_status => l_return_status,
2041 p_msg_count => x_msg_count,
2042 p_msg_data => x_msg_data,
2043 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2044
2045 IF l_hash_msg_name_text_type_tab(x_msg_count - 1).name <> 'ORA' THEN
2046 x_return_status := FND_API.G_RET_STS_ERROR;
2047 ELSE
2048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2049 END IF;
2050
2051 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-1).text;
2052
2053 END INSERT_STG_FEE_REQ_DET;
2054
2055 END IGS_PRECREATE_APPL_PUB;