1 PACKAGE BODY IGS_AD_WRKFLOW_PKG AS
2 /* $Header: IGSADC6B.pls 120.3 2006/05/26 07:21:04 pfotedar ship $ */
3
4 PROCEDURE Extract_Applications
5 ( errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER ,
7 p_person_id IN hz_parties.party_id%TYPE,
8 p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
9 p_calendar_details IN VARCHAR2,
10 p_apc IN VARCHAR2,
11 p_appl_type IN VARCHAR2,
12 p_prog_code IN VARCHAR2,
13 p_location IN VARCHAR2,
14 p_att_type IN VARCHAR2,
15 p_att_mode IN VARCHAR2,
16 p_appl_no_calendar IN VARCHAR2,
17 p_appl_range IN VARCHAR2
18 ) IS
19
20 l_appl_exist VARCHAR2(1);
21 l_user_id NUMBER;
22 l_per_num hz_parties.party_number%type;
23 l_per_num1 hz_parties.party_number%type;
24 l_person_id NUMBER;
25 l_user_name VARCHAR2(100);
26 l_full_name VARCHAR2(1000);
27 l_count number := 0;
28
29 l_adm_cal varchar2(10) := RTRIM(SUBSTR(p_calendar_details, 23, 10));
30 l_acad_cal varchar2(10):= RTRIM(SUBSTR(p_calendar_details,1,10));
31 l_acad_cal_seq_num number := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_calendar_details,14,6));
32 l_adm_cal_seq_num number :=IGS_GE_NUMBER.TO_NUM(SUBSTR(p_calendar_details,37,6));
33 l_adm_cat VARCHAR2(10) := RTRIM(SUBSTR(p_apc,1, 10));
34 l_adm_proc_type varchar2(15) := RTRIM(SUBSTR(p_apc, 11, 30));
35 l_appl_type varchar2(30) := RTRIM(SUBSTR(p_appl_type, 1, 30));
36
37
38 /* No program parameter has been provided,include application without calendar is YES and calendar range is CURRENT */
39 Cursor c_incom_appl_nopgm_curr_anc IS
40 Select aa.person_id
41 From IGS_SS_ADM_APPL_STG aa
42 Where
43 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
44 AND (p_person_id_group IS NOT NULL AND
45 aa.person_id IN (SELECT person_id
46 FROM igs_pe_prsid_grp_mem pgm
47 WHERE pgm.group_id = nvl(p_person_id_group, pgm.group_id)
48 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
49 OR
50 (p_person_id_group is null))
51 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
52 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
53 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
54 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
55 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
56 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
57 Group BY aa.person_id
58 UNION
59 Select aa.person_id
60 From IGS_SS_ADM_APPL_STG aa
61 Where
62 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL)) AND
63 (p_person_id_group IS NOT NULL AND
64 aa.person_id IN (SELECT person_id
65 FROM igs_pe_prsid_grp_mem pgm
66 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
67 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
68 OR
69 (p_person_id_group is null)) AND
70 (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
71 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
72 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
73 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
74 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
75 Group BY aa.person_id;
76
77 /* No program parameter has been provided,include application without calendar is NO and calendar range is CURRENT */
78 Cursor c_incom_appl_nopgm_curr IS
79 Select aa.person_id
80 From IGS_SS_ADM_APPL_STG aa
81 Where ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
82 AND (p_person_id_group IS NOT NULL AND
83 aa.person_id IN (SELECT person_id
84 FROM igs_pe_prsid_grp_mem pgm
85 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
86 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
87 OR
88 (p_person_id_group is null))
89 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
90 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
91 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
92 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
93 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
94 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
95 Group BY aa.person_id;
96
97 /* Program parameter has been provided,include application without calendar is YES and calendar range is CURRENT */
98 Cursor c_incom_appl_pgm_curr_anc IS
99 Select aa.person_id
100 From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
101 Where
102 aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
103 And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
104 AND (p_person_id_group IS NOT NULL AND
105 aa.person_id IN (SELECT person_id
106 FROM igs_pe_prsid_grp_mem pgm
107 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
108 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
109 OR
110 (p_person_id_group is null))
111 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
112 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
113 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
114 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
115 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
116 AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
117 AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
118 AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
119 AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
120 AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
121 Group BY aa.person_id
122 UNION
123 Select aa.person_id
124 From IGS_SS_ADM_APPL_STG aa
125 Where
126 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
127 AND (p_person_id_group IS NOT NULL AND
128 aa.person_id IN (SELECT person_id
129 FROM igs_pe_prsid_grp_mem pgm
130 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
131 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
132 OR
133 (p_person_id_group is null))
134 AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
135 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
136 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
137 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
138 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
139 Group BY aa.person_id;
140
141 /* Program parameter has been provided,include application without calendar is NO and calendar range is CURRENT */
142 Cursor c_incom_appl_pgm_curr IS
143 Select aa.person_id
144 From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
145 Where aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
146 And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
147 AND (p_person_id_group IS NOT NULL AND
148 aa.person_id IN (SELECT person_id
149 FROM igs_pe_prsid_grp_mem pgm
150 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
151 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
152 OR
153 (p_person_id_group is null))
154 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
155 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
156 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
157 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
158 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
159 AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
160 AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
161 AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
162 AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
163 AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
164 Group BY aa.person_id;
165
166 /* No program parameter has been provided,include application without calendar is YES and calendar range is CURRFUTURE */
167 Cursor c_incom_appl_nopgm_cnf_anc IS
168 Select aa.person_id
169 From IGS_SS_ADM_APPL_STG aa
170 Where
171 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
172 AND (p_person_id_group IS NOT NULL
173 AND aa.person_id IN (SELECT person_id
174 FROM igs_pe_prsid_grp_mem pgm
175 WHERE pgm.group_id = p_person_id_group
176 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
177 OR
178 (p_person_id_group is null))
179 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
180 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
181 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
182 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
183 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
184 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
185 UNION
186 Select aa.person_id
187 From IGS_SS_ADM_APPL_STG aa
188 Where
189 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
190 AND (p_person_id_group IS NOT NULL
191 AND aa.person_id IN (SELECT person_id
192 FROM igs_pe_prsid_grp_mem pgm
193 WHERE pgm.group_id = p_person_id_group
194 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
195 OR
196 (p_person_id_group is null))
197 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
198 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
199 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
200 AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
201 AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
202 (
203 select
204 r.sup_cal_type,
205 r.sup_ci_sequence_number,
206 r.sub_cal_type,
207 r.sub_ci_sequence_number
208 from
209 igs_ca_inst_rel r
210 , igs_ca_type t1
211 , igs_ca_type t2
212 , igs_ca_inst acad
213 , igs_ca_inst adm
214 where
215 sup_cal_type = t1.cal_type
216 and t1.s_cal_cat = 'ACADEMIC'
217 and sub_cal_type = t2.cal_type
218 and t2.s_cal_cat = 'ADMISSION'
219 and sup_cal_type = acad.cal_type
220 and sup_ci_sequence_number = acad.sequence_number
221 and sub_cal_type = adm.cal_type
222 and sub_ci_sequence_number = adm.sequence_number
223 and acad.start_dt > ( select ci.start_dt
224 from igs_ca_inst ci
225 where ci.cal_type = l_acad_cal
226 and ci.sequence_number = l_acad_cal_seq_num)
227 and adm.start_dt > ( select ci.start_dt
228 from igs_ca_inst ci
229 where ci.cal_type = l_adm_cal
230 and ci.sequence_number = l_adm_cal_seq_num)
231 )
232 )
233 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
234 Group BY aa.person_id
235 UNION
236 Select aa.person_id
237 From IGS_SS_ADM_APPL_STG aa
238 Where
239 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
240 AND (p_person_id_group IS NOT NULL
241 AND aa.person_id IN (SELECT person_id
242 FROM igs_pe_prsid_grp_mem pgm
243 WHERE pgm.group_id = p_person_id_group
244 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
245 OR
246 (p_person_id_group is null))
247 AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
248 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
249 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
250 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
251 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
252 Group BY aa.person_id;
253
254 /* No program parameter has been provided,include application without calendar is NO and calendar range is CURRFUTURE */
255 Cursor c_incom_appl_nopgm_cnf IS
256 Select aa.person_id
257 From IGS_SS_ADM_APPL_STG aa
258 Where ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
259 AND (p_person_id_group IS NOT NULL AND
260 aa.person_id IN (SELECT person_id
261 FROM igs_pe_prsid_grp_mem pgm
262 WHERE pgm.group_id = p_person_id_group
263 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
264 OR
265 (p_person_id_group is null))
266 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
267 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
268 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
269 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
270 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
271 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
272 Group BY aa.person_id
273 UNION
274 Select aa.person_id
275 From IGS_SS_ADM_APPL_STG aa
276 Where
277 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
278 AND (p_person_id_group IS NOT NULL AND
279 aa.person_id IN (SELECT person_id
280 FROM igs_pe_prsid_grp_mem pgm
281 WHERE pgm.group_id = p_person_id_group
282 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
283 OR
284 (p_person_id_group is null))
285 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
286 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
287 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
288 AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
289 AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
290 (
291 select
292 r.sup_cal_type,
293 r.sup_ci_sequence_number,
294 r.sub_cal_type,
295 r.sub_ci_sequence_number
296 from
297 igs_ca_inst_rel r
298 , igs_ca_type t1
299 , igs_ca_type t2
300 , igs_ca_inst acad
301 , igs_ca_inst adm
302 where
303 sup_cal_type = t1.cal_type
304 and t1.s_cal_cat = 'ACADEMIC'
305 and sub_cal_type = t2.cal_type
306 and t2.s_cal_cat = 'ADMISSION'
307 and sup_cal_type = acad.cal_type
308 and sup_ci_sequence_number = acad.sequence_number
309 and sub_cal_type = adm.cal_type
310 and sub_ci_sequence_number = adm.sequence_number
311 and acad.start_dt > ( select ci.start_dt
312 from igs_ca_inst ci
313 where ci.cal_type = l_acad_cal
314 and ci.sequence_number = l_acad_cal_seq_num)
315 and adm.start_dt > ( select ci.start_dt
316 from igs_ca_inst ci
317 where ci.cal_type = l_adm_cal
318 and ci.sequence_number = l_adm_cal_seq_num)
319 )
320 )
321 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
322 Group BY aa.person_id;
323
324 /* Program parameter has been provided,include application without calendar is YES and calendar range is CURRFUTURE */
325 Cursor c_incom_appl_pgm_cnf_anc IS
326 Select aa.person_id
327 From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
328 Where
329 aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
330 And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
331 AND (p_person_id_group IS NOT NULL AND
332 aa.person_id IN (SELECT person_id
333 FROM igs_pe_prsid_grp_mem pgm
334 WHERE pgm.group_id = p_person_id_group
335 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
336 OR
337 (p_person_id_group is null))
338 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
339 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
340 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
341 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
342 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
343 AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
344 AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
345 AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
346 AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
347 AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
348 Group BY aa.person_id
349 UNION
350 Select aa.person_id
351 From IGS_SS_ADM_APPL_STG aa
352 Where
353 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
354 AND (p_person_id_group IS NOT NULL AND
355 aa.person_id IN (SELECT person_id
356 FROM igs_pe_prsid_grp_mem pgm
357 WHERE pgm.group_id = p_person_id_group
358 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
359 OR
360 (p_person_id_group is null))
361 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
362 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
363 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
364 AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
365 AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
366 (
367 select
368 r.sup_cal_type,
369 r.sup_ci_sequence_number,
370 r.sub_cal_type,
371 r.sub_ci_sequence_number
372 from
373 igs_ca_inst_rel r
374 , igs_ca_type t1
375 , igs_ca_type t2
376 , igs_ca_inst acad
377 , igs_ca_inst adm
378 where
379 sup_cal_type = t1.cal_type
380 and t1.s_cal_cat = 'ACADEMIC'
381 and sub_cal_type = t2.cal_type
382 and t2.s_cal_cat = 'ADMISSION'
383 and sup_cal_type = acad.cal_type
384 and sup_ci_sequence_number = acad.sequence_number
385 and sub_cal_type = adm.cal_type
386 and sub_ci_sequence_number = adm.sequence_number
387 and acad.start_dt > ( select ci.start_dt
388 from igs_ca_inst ci
389 where ci.cal_type = l_acad_cal
390 and ci.sequence_number = l_acad_cal_seq_num)
391 and adm.start_dt > ( select ci.start_dt
392 from igs_ca_inst ci
393 where ci.cal_type = l_adm_cal
394 and ci.sequence_number = l_adm_cal_seq_num)
395 )
396 )
397 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
398 Group BY aa.person_id
399 UNION
400 Select aa.person_id
401 From IGS_SS_ADM_APPL_STG aa
402 Where
403 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
404 AND (p_person_id_group IS NOT NULL AND
405 aa.person_id IN (SELECT person_id
406 FROM igs_pe_prsid_grp_mem pgm
407 WHERE pgm.group_id = p_person_id_group
408 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
409 OR
410 (p_person_id_group is null))
411 AND (aa.ACAD_CAL_TYPE IS NULL And aa.ACAD_CAL_SEQ_NUMBER IS NULL And aa.ADM_CAL_TYPE IS NULL And aa.ADM_CAL_SEQ_NUMBER IS NULL)
412 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
413 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
414 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
415 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
416 Group BY aa.person_id;
417
418 /* Program parameter has been provided,include application without calendar is YES and calendar range is CURRFUTURE */
419 Cursor c_incom_appl_pgm_cnf IS
420 Select aa.person_id
421 From IGS_SS_ADM_APPL_STG aa, IGS_SS_APP_PGM_STG aap
422 Where aap.SS_ADM_APPL_ID = aa.SS_ADM_APPL_ID(+)
423 And ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
424 AND (p_person_id_group IS NOT NULL AND
425 aa.person_id IN (SELECT person_id
426 FROM igs_pe_prsid_grp_mem pgm
427 WHERE pgm.group_id = p_person_id_group
428 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
429 OR
430 (p_person_id_group is null))
431 AND ((aa.ACAD_CAL_TYPE= l_acad_cal) AND (aa.ACAD_CAL_SEQ_NUMBER= l_acad_cal_seq_num)
432 AND (aa.ADM_CAL_TYPE= l_adm_cal) AND (aa.ADM_CAL_SEQ_NUMBER= l_adm_cal_seq_num))
433 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
434 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
435 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
436 AND ((aap.NOMINATED_COURSE_CD = p_prog_code AND p_prog_code IS NOT NULL ) OR (p_prog_code IS NULL))
437 AND ((aap.LOCATION_CD = p_location AND p_location IS NOT NULL ) OR (p_location IS NULL))
438 AND ((aap.ATTENDANCE_TYPE = p_att_type AND p_att_type IS NOT NULL ) OR (p_att_type IS NULL))
439 AND ((aap.ATTENDANCE_MODE = p_att_mode AND p_att_mode IS NOT NULL ) OR (p_att_mode IS NULL))
440 AND aa.APP_SOURCE_ID IN (select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
441 UNION
442 Select aa.person_id
443 From IGS_SS_ADM_APPL_STG aa
444 Where
445 ((aa.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
446 AND (p_person_id_group IS NOT NULL AND
447 aa.person_id IN (SELECT person_id
448 FROM igs_pe_prsid_grp_mem pgm
449 WHERE pgm.group_id = p_person_id_group
450 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
451 OR
452 (p_person_id_group is null))
453 AND (((aa.ADMISSION_CAT = l_adm_cat) AND (aa.S_ADM_PROCESS_TYPE = l_adm_proc_type)
454 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
455 AND ((aa.ADMISSION_APPLICATION_TYPE = p_appl_type
456 AND p_appl_type IS NOT NULL ) OR (p_appl_type IS NULL))
457 AND ((aa.acad_cal_type,aa.acad_cal_seq_number,aa.adm_cal_type, aa.adm_cal_seq_number) in
458 (
459 select
460 r.sup_cal_type,
461 r.sup_ci_sequence_number,
462 r.sub_cal_type,
463 r.sub_ci_sequence_number
464 from
465 igs_ca_inst_rel r
466 , igs_ca_type t1
467 , igs_ca_type t2
468 , igs_ca_inst acad
469 , igs_ca_inst adm
470 where
471 sup_cal_type = t1.cal_type
472 and t1.s_cal_cat = 'ACADEMIC'
473 and sub_cal_type = t2.cal_type
474 and t2.s_cal_cat = 'ADMISSION'
475 and sup_cal_type = acad.cal_type
476 and sup_ci_sequence_number = acad.sequence_number
477 and sub_cal_type = adm.cal_type
478 and sub_ci_sequence_number = adm.sequence_number
479 and acad.start_dt > ( select ci.start_dt
480 from igs_ca_inst ci
481 where ci.cal_type = l_acad_cal
482 and ci.sequence_number = l_acad_cal_seq_num)
483 and adm.start_dt > ( select ci.start_dt
484 from igs_ca_inst ci
485 where ci.cal_type = l_adm_cal
486 and ci.sequence_number = l_adm_cal_seq_num)
487 )
488 )
489 AND aa.APP_SOURCE_ID IN ( select code_id from igs_ad_code_classes where CLASS = 'SYS_APPL_SOURCE' and system_status = 'WEB_APPL' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
490 Group BY aa.person_id;
491
492
493 CURSOR cur_user(cp_person_id igs_ss_adm_appl_stg.person_id%TYPE) IS
494 SELECT user_id, user_name, description
495 FROM FND_USER
496 WHERE person_party_id = cp_person_id ;
497
498 CURSOR c_per_num IS
499 SELECT party_number
500 FROM HZ_PARTIES
501 WHERE party_id = p_person_id ;
502
503 CURSOR c_per_num1(cp_person_id igs_ss_adm_appl_stg.person_id%TYPE) IS
504 SELECT party_number
505 FROM HZ_PARTIES
506 WHERE party_id = cp_person_id ;
507
508 BEGIN
509
510 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
511 igs_ge_gen_003.set_org_id(null);
512
513 RETCODE := 0;
514 ERRBUF := NULL;
515
516 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_APPNTF_PRMS');
517 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
518
519 OPEN c_per_num;
520 FETCH c_per_num INTO l_per_num;
521 CLOSE c_per_num;
522
523 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_PNUM');
524 FND_MESSAGE.SET_TOKEN ('PNUM', l_per_num);
525 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
526
527 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_PID_GRP');
528 FND_MESSAGE.SET_TOKEN ('PGPID', p_person_id_group);
529 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
530
531 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CL_DTLS');
532 FND_MESSAGE.SET_TOKEN('CLDTLS', p_calendar_details);
533 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
534
535 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_APC');
536 FND_MESSAGE.SET_TOKEN ('APC', p_apc);
537 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
538
539 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_APPL_TYPE');
540 FND_MESSAGE.SET_TOKEN ('APPLTYPE', p_appl_type);
541 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
542
543 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_CRCD');
544 FND_MESSAGE.SET_TOKEN ('CRCD', p_prog_code);
545 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
546
547 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_LOC');
548 FND_MESSAGE.SET_TOKEN ('LOC', p_location);
549 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
550
551 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_ATT_TYPE');
552 FND_MESSAGE.SET_TOKEN ('ATTTYPE', p_att_type);
553 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
554
555 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_ATT_MODE');
556 FND_MESSAGE.SET_TOKEN ('ATTMODE', p_att_mode);
557 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
558
559 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_APPL_NC');
560 FND_MESSAGE.SET_TOKEN ('APPLNOCAL', p_appl_no_calendar);
561 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
562
563 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_APPL_RANGE');
564 FND_MESSAGE.SET_TOKEN ('APPLRNGE', p_appl_range);
565 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
566 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
567 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
568
569 IF (p_person_id IS NOT NULL AND p_person_id_group IS NOT NULL ) THEN
570
571 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NO_PERID_PERIDGRP');
572 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
573
574 ELSIF NVL( p_appl_range, 'CURRENT') = 'CURRFUTURE' THEN
575
576 IF p_prog_code IS NULL THEN
577
578 IF NVL(p_appl_no_calendar,'N') = 'Y' THEN
579
580 l_count := 0;
581
582 OPEN c_incom_appl_nopgm_cnf_anc;
583 LOOP
584 FETCH c_incom_appl_nopgm_cnf_anc INTO l_person_id;
585 EXIT WHEN c_incom_appl_nopgm_cnf_anc%NOTFOUND;
586
587 IF c_incom_appl_nopgm_cnf_anc%FOUND THEN
588 l_count := l_count+1;
589
590 OPEN cur_user(l_person_id);
591 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
592
593 IF cur_user%FOUND THEN
594
595 OPEN c_per_num1(l_person_id);
596 FETCH c_per_num1 INTO l_per_num1;
597 CLOSE c_per_num1;
598
599 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
600 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
601 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
602
603 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
604 ELSE
605 -- write in the log that no user_id exists in the FND_USER table for this person_id
606 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
607 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
608 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
609
610 END IF;
611
612 CLOSE cur_user;
613
614 END IF; /* c_incom_appl_nopgm_cnf_anc */
615 END LOOP;
616
617 IF l_count = 0 THEN
618 -- write in the log file that no record exists
619 -- Invalid parameters entered. Valid combinations for parameters to be entered
620 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
621 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
622 END IF;
623
624 CLOSE c_incom_appl_nopgm_cnf_anc;
625
626 ELSIF NVL(p_appl_no_calendar,'N') = 'N' THEN
627 l_count := 0;
628 OPEN c_incom_appl_nopgm_cnf;
629 LOOP
630 FETCH c_incom_appl_nopgm_cnf INTO l_person_id;
631 EXIT WHEN c_incom_appl_nopgm_cnf%NOTFOUND;
632
633 IF c_incom_appl_nopgm_cnf%FOUND THEN
634 l_count := l_count+1;
635
636 OPEN cur_user(l_person_id);
637 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
638
639 IF cur_user%FOUND THEN
640
641 OPEN c_per_num1(l_person_id);
642 FETCH c_per_num1 INTO l_per_num1;
643 CLOSE c_per_num1;
644
645 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
646 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
647 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
648
649 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
650 ELSE
651 -- write in the log that no user_id exists in the FND_USER table for this person_id
652 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
653 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
654 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
655
656 END IF; /* cur_user */
657
658 CLOSE cur_user;
659
660 END IF; /* c_incom_appl_nopgm_cnf */
661 END LOOP;
662
663 IF l_count = 0 THEN
664 -- write in the log file that no record exists
665 -- Invalid parameters entered. Valid combinations for parameters to be entered
666 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
667 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
668 END IF;
669
670 CLOSE c_incom_appl_nopgm_cnf;
671
672 END IF; /* p_appl_no_calendar */
673
674 ELSIF p_prog_code IS NOT NULL THEN -- i.e.program parameter has been supplied
675
676 IF NVL(p_appl_no_calendar,'N') = 'Y' THEN
677
678 l_count := 0;
679 OPEN c_incom_appl_pgm_cnf_anc;
680 LOOP
681 FETCH c_incom_appl_pgm_cnf_anc INTO l_person_id;
682 EXIT WHEN c_incom_appl_pgm_cnf_anc%NOTFOUND;
683
684 IF c_incom_appl_pgm_cnf_anc%FOUND THEN
685 l_count := l_count+1;
686
687 OPEN cur_user(l_person_id);
688
689 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
690
691 IF cur_user%FOUND THEN
692
693 OPEN c_per_num1(l_person_id);
694 FETCH c_per_num1 INTO l_per_num1;
695 CLOSE c_per_num1;
696
697 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
698 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
699 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
700
701 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
702 ELSE
703 -- write in the log that no user_id exists in the FND_USER table for this person_id
704 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
705 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
706 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
707
708 END IF; /* cur_user */
709
710 CLOSE cur_user;
711
712 END IF; /* c_incom_appl_pgm_cnf_anc */
713 END LOOP;
714
715 IF l_count = 0 THEN
716 -- write in the log file that no record exists
717 -- Invalid parameters entered. Valid combinations for parameters to be entered
718 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
719 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
720 END IF;
721
722 CLOSE c_incom_appl_pgm_cnf_anc;
723
724 ELSIF NVL(p_appl_no_calendar,'N') = 'N' THEN
725
726 l_count := 0;
727 OPEN c_incom_appl_pgm_cnf;
728 LOOP
729
730 FETCH c_incom_appl_pgm_cnf INTO l_person_id;
731 EXIT WHEN c_incom_appl_pgm_cnf%NOTFOUND;
732
733 IF c_incom_appl_pgm_cnf%FOUND THEN
734 l_count := l_count+1;
735
736 OPEN cur_user(l_person_id);
737
738 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
739
740 IF cur_user%FOUND THEN
741
742 OPEN c_per_num1(l_person_id);
743 FETCH c_per_num1 INTO l_per_num1;
744 CLOSE c_per_num1;
745
746 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
747 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
748 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
749
750 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
751 ELSE
752 -- write in the log that no user_id exists in the FND_USER table for this person_id
753 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
754 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
755 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
756
757 END IF; /* cur_user */
758
759 CLOSE cur_user;
760
761 END IF; /* c_incom_appl_pgm_cnf */
762 END LOOP;
763
764 IF l_count = 0 THEN
765 -- write in the log file that no record exists
766 -- Invalid parameters entered. Valid combinations for parameters to be entered
767 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
768 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
769 END IF;
770
771 CLOSE c_incom_appl_pgm_cnf;
772
773 END IF; /* p_appl_no_calendar */
774
775 END IF; /* p_prog_code */
776
777 ELSIF NVL(p_appl_range, 'CURRENT') = 'CURRENT' THEN
778
779 IF p_prog_code IS NULL THEN
780
781 IF NVL(p_appl_no_calendar,'N') = 'Y' THEN
782 l_count := 0;
783 OPEN c_incom_appl_nopgm_curr_anc;
784
785 LOOP
786
787 FETCH c_incom_appl_nopgm_curr_anc INTO l_person_id;
788 EXIT WHEN c_incom_appl_nopgm_curr_anc%NOTFOUND;
789
790 IF c_incom_appl_nopgm_curr_anc%FOUND THEN
791 l_count := l_count+1;
792
793 OPEN cur_user(l_person_id);
794 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
795
796 IF cur_user%FOUND THEN
797
798 OPEN c_per_num1(l_person_id);
799 FETCH c_per_num1 INTO l_per_num1;
800 CLOSE c_per_num1;
801
802 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
803 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
804 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
805
806 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
807 ELSE
808 -- write in the log that no user_id exists in the FND_USER table for this person_id
809 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
810 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
811 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
812
813 END IF;
814
815 CLOSE cur_user;
816 END IF; /* c_incom_appl_nopgm_curr_anc */
817 END LOOP;
818 IF l_count = 0 THEN
819 -- write in the log file that no record exists
820 -- Invalid parameters entered. Valid combinations for parameters to be entered
821 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
822 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
823
824 END IF;
825
826
827 CLOSE c_incom_appl_nopgm_curr_anc;
828
829 ELSIF NVL(p_appl_no_calendar,'N') = 'N' THEN
830 l_count := 0;
831 OPEN c_incom_appl_nopgm_curr;
832 LOOP
833 FETCH c_incom_appl_nopgm_curr INTO l_person_id;
834 EXIT WHEN c_incom_appl_nopgm_curr%NOTFOUND;
835
836 IF c_incom_appl_nopgm_curr%FOUND THEN
837 l_count := l_count +1;
838
839 OPEN cur_user(l_person_id);
840 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
841
842 IF cur_user%FOUND THEN
843
844 OPEN c_per_num1(l_person_id);
845 FETCH c_per_num1 INTO l_per_num1;
846 CLOSE c_per_num1;
847
848 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
849 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
850 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
851
852 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
853
854 ELSE
855 -- write in the log that no user_id exists in the FND_USER table for this person_id
856 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
857 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
858 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
859
860 END IF; /* cur_user */
861
862 CLOSE cur_user;
863 END IF; /* c_incom_appl_nopgm_curr */
864 END LOOP;
865 IF l_count = 0 THEN
866 -- write in the log file that no record exists
867 -- Invalid parameters entered. Valid combinations for parameters to be entered
868 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
869 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
870 END IF;
871
872 CLOSE c_incom_appl_nopgm_curr;
873
874 END IF; /* p_appl_no_calendar */
875
876 ELSIF p_prog_code IS NOT NULL THEN -- i.e.program parameter has been supplied
877
878 IF NVL(p_appl_no_calendar,'N') = 'Y' THEN
879 l_count := 0;
880
881 OPEN c_incom_appl_pgm_curr_anc;
882 LOOP
883 FETCH c_incom_appl_pgm_curr_anc INTO l_person_id;
884 EXIT WHEN c_incom_appl_pgm_curr_anc%NOTFOUND;
885
886 IF c_incom_appl_pgm_curr_anc%FOUND THEN
887 l_count := l_count +1;
888
889 OPEN cur_user(l_person_id);
890
891 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
892
893 IF cur_user%FOUND THEN
894
895 OPEN c_per_num1(l_person_id);
896 FETCH c_per_num1 INTO l_per_num1;
897 CLOSE c_per_num1;
898
899 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
900 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
901 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
902
903 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
904 ELSE
905 -- write in the log that no user_id exists in the FND_USER table for this person_id
906 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
907 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
908 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
909
910 END IF; /* cur_user */
911
912 CLOSE cur_user;
913
914 END IF; /* c_incom_appl_pgm_curr_anc */
915 END LOOP;
916
917 IF l_count = 0 THEN
918 -- write in the log file that no record exists
919 -- Invalid parameters entered. Valid combinations for parameters to be entered
920 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
921 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
922 END IF;
923
924 CLOSE c_incom_appl_pgm_curr_anc;
925
926 ELSIF NVL(p_appl_no_calendar,'N') = 'N' THEN
927 l_count := 0;
928 OPEN c_incom_appl_pgm_curr;
929
930 LOOP
931 FETCH c_incom_appl_pgm_curr INTO l_person_id;
932 EXIT WHEN c_incom_appl_pgm_curr%NOTFOUND;
933
934 IF c_incom_appl_pgm_curr%FOUND THEN
935 l_count := l_count+1;
936
937 OPEN cur_user(l_person_id);
938
939 FETCH cur_user INTO l_user_id, l_user_name, l_full_name;
940
941 IF cur_user%FOUND THEN
942
943 OPEN c_per_num1(l_person_id);
944 FETCH c_per_num1 INTO l_per_num1;
945 CLOSE c_per_num1;
946
947 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF1');
948 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
949 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
950
951 WF_Inform_Applicant_INAP(l_user_id, l_user_name, l_full_name);
952 ELSE
953 -- write in the log that no user_id exists in the FND_USER table for this person_id
954 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_USR_PER_INV_COMB');
955 FND_MESSAGE.SET_TOKEN ('PERSONNUM', l_per_num1);
956 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
957
958 END IF; /* cur_user */
959
960 CLOSE cur_user;
961
962 END IF; /* c_incom_appl_pgm_curr */
963 END LOOP;
964 IF l_count = 0 THEN
965 -- write in the log file that no record exists
966 -- Invalid parameters entered. Valid combinations for parameters to be entered
967 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
968 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
969 END IF;
970
971 CLOSE c_incom_appl_pgm_curr;
972
973 END IF; /* p_appl_no_calendar */
974
975 END IF; /* p_prog_code */
976
977 END IF; /* p_appl_range */
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 RETCODE := 2;
982 ERRBUF := fnd_message.get_string( 'IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
983 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
984
985 IF c_incom_appl_nopgm_curr_anc%ISOPEN THEN
986 CLOSE c_incom_appl_nopgm_curr_anc;
987 END IF;
988 IF c_incom_appl_nopgm_curr%ISOPEN THEN
989 CLOSE c_incom_appl_nopgm_curr;
990 END IF;
991 IF c_incom_appl_pgm_curr_anc%ISOPEN THEN
992 CLOSE c_incom_appl_pgm_curr_anc;
993 END IF;
994 IF c_incom_appl_pgm_curr%ISOPEN THEN
995 CLOSE c_incom_appl_pgm_curr;
996 END IF;
997 IF c_incom_appl_nopgm_cnf_anc%ISOPEN THEN
998 CLOSE c_incom_appl_nopgm_cnf_anc;
999 END IF;
1000 IF c_incom_appl_nopgm_cnf%ISOPEN THEN
1001 CLOSE c_incom_appl_nopgm_cnf;
1002 END IF;
1003 IF c_incom_appl_pgm_cnf_anc%ISOPEN THEN
1004 CLOSE c_incom_appl_pgm_cnf_anc;
1005 END IF;
1006 IF c_incom_appl_pgm_cnf%ISOPEN THEN
1007 CLOSE c_incom_appl_pgm_cnf;
1008 END IF;
1009 IF c_per_num%ISOPEN THEN
1010 CLOSE c_per_num;
1011 END IF;
1012 IF c_per_num1%ISOPEN THEN
1013 CLOSE c_per_num1;
1014 END IF;
1015
1016
1017 END Extract_Applications;
1018
1019 /* *************************************************************************************/
1020 -- This Procedure raises an event when there incomplete application concurrent job is submitted.
1021 /* *************************************************************************************/
1022
1023 PROCEDURE Wf_Inform_Applicant_INAP
1024 ( p_applicant_id IN NUMBER,
1025 p_applicant_name IN VARCHAR2,
1026 p_applicant_full_name IN VARCHAR2
1027 )
1028 IS
1029 l_event_t wf_event_t;
1030 l_parameter_list_t wf_parameter_list_t;
1031 l_itemKey varchar2(100);
1032 l_incomplt_appl_url varchar2(1000);
1033
1034
1035 CURSOR cur_seq IS
1036 SELECT IGS_AD_WF_INAPPL_S.NEXTVAL
1037 FROM dual;
1038
1039
1040 BEGIN
1041
1042 -- initialize the wf_event_t object
1043 --
1044 wf_event_t.Initialize(l_event_t);
1045
1046 OPEN cur_seq ;
1047 FETCH cur_seq INTO l_itemKey ;
1048 CLOSE cur_seq ;
1049
1050
1051
1052 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_ID',p_Value => p_applicant_id, p_parameterlist=>l_parameter_list_t);
1053 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_NAME',p_Value => p_applicant_name, p_parameterlist=>l_parameter_list_t);
1054 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_FULL_NAME',p_Value => p_applicant_full_name, p_parameterlist=>l_parameter_list_t);
1055 -- wf_event.AddParameterToList ( p_Name => 'IA_INCOM_APPL_URL',p_Value => l_incomplt_appl_url, p_parameterlist=>l_parameter_list_t);
1056
1057 -- raise the event
1058
1059 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.appl.incmpl_appl',
1060 p_event_key => l_itemKey,
1061 p_parameters => l_parameter_list_t);
1062
1063 l_parameter_list_t.delete;
1064
1065 END Wf_Inform_Applicant_INAP ;
1066
1067
1068 PROCEDURE wf_set_url_inap (itemtype IN VARCHAR2 ,
1069 itemkey IN VARCHAR2 ,
1070 actid IN NUMBER ,
1071 funcmode IN VARCHAR2 ,
1072 resultout OUT NOCOPY VARCHAR2
1073 ) AS
1074
1075 l_date_prod VARCHAR2(30);
1076 l_doc_type VARCHAR2(30);
1077 l_role_name VARCHAR2(320);
1078 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSAS006';
1079 l_person_id_sep VARCHAR2(4000);
1080 l_person_id VARCHAR2(30);
1081 -- l_profile_value VARCHAR2(200) := FND_PROFILE.VALUE('APPS_JSP_AGENT');
1082
1083
1084 l_url varchar2(4000) := 'http://qapache.us.oracle.com:16526/OA_HTML/OA.jsp?akRegionCode=IGS_AD_APPL_INCOMPLETE_PAGE&akRegionApplicationId=8405';
1085 l_value varchar2(100);
1086
1087 BEGIN
1088 IF (funcmode = 'RUN') THEN
1089
1090 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1091 ItemKey => itemkey,
1092 aname => 'IA_INCOM_APPL_URL',
1093 avalue => l_url
1094 );
1095
1096 l_value := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_INCOM_APPL_URL');
1097 Resultout:= 'COMPLETE:';
1098 RETURN;
1099 END IF;
1100 END wf_set_url_inap;
1101
1102
1103 PROCEDURE Adm_Application_Req
1104 ( errbuf OUT NOCOPY VARCHAR2,
1105 retcode OUT NOCOPY NUMBER ,
1106 p_person_id IN hz_parties.party_id%TYPE,
1107 p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
1108 p_appl_id IN igs_ad_appl.application_id%Type,
1109 p_calendar_details IN VARCHAR2,
1110 p_tracking_type IN VARCHAR2,
1111 p_apc IN VARCHAR2,
1112 p_appl_type IN VARCHAR2,
1113 p_prog_code IN VARCHAR2,
1114 p_location IN VARCHAR2,
1115 p_att_type IN VARCHAR2,
1116 p_att_mode IN VARCHAR2
1117 ) IS
1118
1119 l_appl_exist VARCHAR2(1);
1120 l_user_id NUMBER;
1121 l_person_id NUMBER;
1122 l_per_num hz_parties.party_number%type;
1123 lv_person_id VARCHAR2(300);
1124 l_person_name VARCHAR2(320);
1125 l_display_name VARCHAR2(360);
1126 l_user_name VARCHAR2(100);
1127 l_full_name VARCHAR2(1000);
1128 l_count number := 0;
1129
1130 l_adm_cal igs_ca_inst_all.cal_type%TYPE := RTRIM(SUBSTR(p_calendar_details, 23, 10));
1131 l_acad_cal igs_ca_inst_all.cal_type%TYPE := RTRIM(SUBSTR(p_calendar_details,1,10));
1132 l_acad_cal_seq_num igs_ca_inst_all.sequence_number%TYPE := IGS_GE_NUMBER.TO_NUM(SUBSTR(p_calendar_details,14,6));
1133 l_adm_cal_seq_num igs_ca_inst_all.sequence_number%TYPE :=IGS_GE_NUMBER.TO_NUM(SUBSTR(p_calendar_details,37,6));
1134 l_adm_cat VARCHAR2(10) := RTRIM(SUBSTR(p_apc,1, 10));
1135 l_adm_proc_type varchar2(15) := RTRIM(SUBSTR(p_apc, 11, 30));
1136
1137 l_alt_code_acad igs_ca_inst.alternate_code%TYPE;
1138 l_alt_code_adm igs_ca_inst.alternate_code%TYPE;
1139
1140 Cursor c_per_adm_req IS
1141 select a.person_id
1142 from igs_ad_appl_all a,
1143 igs_ad_ps_appl_inst_all i,
1144 igs_ad_aplins_admreq r,
1145 igs_tr_item_all t,
1146 igs_tr_type_all ty
1147 where a.person_id = i.person_id
1148 and (p_person_id_group IS NOT NULL and
1149 a.person_id IN (SELECT person_id
1150 FROM igs_pe_prsid_grp_mem pgm
1151 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
1152 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
1153 OR
1154 (p_person_id_group is null))
1155 and a.admission_appl_number = i.admission_appl_number
1156 and i.person_id = r.person_id
1157 and i.nominated_course_cd = r.course_cd
1158 and i.admission_appl_number = r.admission_appl_number
1159 and i.sequence_number = r.sequence_number
1160 and r.tracking_id = t.tracking_id
1161 and t.tracking_status in (select tracking_status from igs_tr_status where s_tracking_status = 'ACTIVE')
1162 AND ((a.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
1163 AND ((a.ACAD_CAL_TYPE= l_acad_cal) AND (a.ACAD_CI_SEQUENCE_NUMBER= l_acad_cal_seq_num))
1164 AND ((a.ADM_CAL_TYPE= l_adm_cal) AND (a.ADM_CI_SEQUENCE_NUMBER= l_adm_cal_seq_num))
1165 AND (((a.ADMISSION_CAT = l_adm_cat) AND (a.S_ADMISSION_PROCESS_TYPE = l_adm_proc_type)
1166 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
1167 AND ((a.application_id = p_appl_id and p_appl_id IS NOT NULL) OR (p_appl_id is NULL))
1168 AND ((a.application_type = p_appl_type and p_appl_type IS NOT NULL) OR (p_appl_type is NULL))
1169 AND ((i.nominated_course_cd = p_prog_code and p_prog_code IS NOT NULL) OR (p_prog_code is NULL))
1170 AND ((i.location_cd = p_location and p_location IS NOT NULL) OR (p_location is NULL))
1171 AND ((i.attendance_mode = p_att_mode and p_att_mode IS NOT NULL) OR (p_att_mode is NULL))
1172 AND ((i.attendance_type = p_att_type and p_att_type IS NOT NULL) OR (p_att_type is NULL))
1173 and t.tracking_type = ty.tracking_type
1174 and ty.s_tracking_type = 'ADM_PROCESSING'
1175 Group by a.person_id;
1176
1177 Cursor c_per_post_adm_req IS
1178 select a.person_id
1179 from igs_ad_appl_all a,
1180 igs_ad_ps_appl_inst_all i,
1181 igs_ad_aplins_admreq r,
1182 igs_tr_item_all t,
1183 igs_tr_type_all ty
1184 where a.person_id = i.person_id
1185 and (p_person_id_group IS NOT NULL and
1186 a.person_id IN (SELECT person_id
1187 FROM igs_pe_prsid_grp_mem pgm
1188 WHERE pgm.group_id = nvl(p_person_id_group,pgm.group_id)
1189 And SYSDATE BETWEEN nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE))
1190 OR
1191 (p_person_id_group is null))
1192 and a.admission_appl_number = i.admission_appl_number
1193 and i.person_id = r.person_id
1194 and i.nominated_course_cd = r.course_cd
1195 and i.admission_appl_number = r.admission_appl_number
1196 and i.sequence_number = r.sequence_number
1197 and r.tracking_id = t.tracking_id
1198 and t.tracking_status in (select tracking_status from igs_tr_status where s_tracking_status = 'ACTIVE')
1199 AND ((a.person_id = p_person_id AND p_person_id IS NOT NULL) OR (p_person_id is NULL))
1200 AND ((a.ACAD_CAL_TYPE = l_acad_cal) AND (a.ACAD_CI_SEQUENCE_NUMBER = l_acad_cal_seq_num))
1201 AND ((a.ADM_CAL_TYPE = l_adm_cal) AND (a.ADM_CI_SEQUENCE_NUMBER = l_adm_cal_seq_num))
1202 AND (((a.ADMISSION_CAT = l_adm_cat) AND (a.S_ADMISSION_PROCESS_TYPE = l_adm_proc_type)
1203 AND (p_apc IS NOT NULL)) OR (p_apc is NULL))
1204 AND ((a.application_id = p_appl_id and p_appl_id IS NOT NULL) OR (p_appl_id is NULL))
1205 AND ((a.application_type = p_appl_type and p_appl_type IS NOT NULL) OR (p_appl_type is NULL))
1206 AND ((i.nominated_course_cd = p_prog_code and p_prog_code IS NOT NULL) OR (p_prog_code is NULL))
1207 AND ((i.location_cd = p_location and p_location IS NOT NULL) OR (p_location is NULL))
1208 AND ((i.attendance_mode = p_att_mode and p_att_mode IS NOT NULL) OR (p_att_mode is NULL))
1209 AND ((i.attendance_type = p_att_type and p_att_type IS NOT NULL) OR (p_att_type is NULL))
1210 and t.tracking_type = ty.tracking_type
1211 and ty.s_tracking_type = 'POST_ADMISSION'
1212 group by a.person_id;
1213
1214 CURSOR c_get_alt_code(cp_cal_type igs_ca_inst.cal_type%TYPE,cp_seq_no igs_ca_inst.sequence_number%TYPE) IS
1215 SELECT alternate_code
1216 FROM IGS_CA_INST
1217 WHERE cal_type = cp_cal_type AND
1218 sequence_number = cp_seq_no;
1219
1220 CURSOR cur_user(cp_person_id igs_ad_appl_all.person_id%TYPE) IS
1221 SELECT user_name, description
1222 FROM FND_USER
1223 WHERE person_party_id = cp_person_id ;
1224
1225 CURSOR c_per_num IS
1226 SELECT party_number
1227 FROM HZ_PARTIES
1228 WHERE party_id = p_person_id ;
1229
1230 BEGIN
1231
1232 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
1233 igs_ge_gen_003.set_org_id(null);
1234
1235 RETCODE := 0;
1236 ERRBUF := NULL;
1237
1238 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_APPNTF_PRMS');
1239 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1240
1241 OPEN c_per_num;
1242 FETCH c_per_num INTO l_per_num;
1243 CLOSE c_per_num;
1244
1245 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_PNUM');
1246 FND_MESSAGE.SET_TOKEN ('PNUM', l_per_num);
1247 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1248
1249 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_PID_GRP');
1250 FND_MESSAGE.SET_TOKEN ('PGPID', p_person_id_group);
1251 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1252
1253 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_APPLID');
1254 FND_MESSAGE.SET_TOKEN ('APPLID', p_appl_id);
1255 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1256
1257 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CL_DTLS');
1258 FND_MESSAGE.SET_TOKEN('CLDTLS', p_calendar_details);
1259 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
1260
1261 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_TRKTYP');
1262 FND_MESSAGE.SET_TOKEN ('TRKTYP', p_tracking_type);
1263 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1264
1265 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_APC');
1266 FND_MESSAGE.SET_TOKEN ('APC', p_apc);
1267 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1268
1269 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_APPL_TYPE');
1270 FND_MESSAGE.SET_TOKEN ('APPLTYPE', p_appl_type);
1271 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1272
1273 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_CRCD');
1274 FND_MESSAGE.SET_TOKEN ('CRCD', p_prog_code);
1275 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1276
1277 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_LOC');
1278 FND_MESSAGE.SET_TOKEN ('LOC', p_location);
1279 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1280
1281 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_ATT_TYPE');
1282 FND_MESSAGE.SET_TOKEN ('ATTTYPE', p_att_type);
1283 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1284
1285 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_LG_INAP_ATT_MODE');
1286 FND_MESSAGE.SET_TOKEN ('ATTMODE', p_att_mode);
1287 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1288
1289 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1290 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1291
1292 IF (p_person_id IS NOT NULL AND p_person_id_group IS NOT NULL ) THEN
1293
1294 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NO_PERID_PERIDGRP');
1295 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1296
1297 ELSIF NVL(p_tracking_type, 'ADM_PROCESSING') = 'ADM_PROCESSING' THEN
1298
1299 l_count := 0;
1300
1301 OPEN c_per_adm_req;
1302
1303 LOOP
1304
1305 FETCH c_per_adm_req INTO l_person_id;
1306
1307 EXIT WHEN c_per_adm_req%NOTFOUND;
1308
1309 IF c_per_adm_req%FOUND THEN
1310
1311 l_count := l_count+1;
1312 lv_person_id := IGS_GE_NUMBER.TO_CANN(l_person_id);
1313
1314 -- The following code should be uncommented once the TCA HZ.J patch is applied
1315 /* Wf_Directory.GetRoleName('HZ_PARTY', lv_person_id, l_person_name, l_display_name);
1316
1317 IF l_person_name IS NOT NULL THEN
1318
1319 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF2');
1320 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1321
1322 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1323 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1324 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1325 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1326
1327 ELSE
1328 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
1329 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1330 END IF; */
1331 ---------------------------------------------------------------------------------
1332 --Get Alternate Code
1333 ---------------------------------------------------------------------------------
1334 OPEN c_get_alt_code(l_acad_cal,l_acad_cal_seq_num);
1335 FETCH c_get_alt_code INTO l_alt_code_acad;
1336 CLOSE c_get_alt_code;
1337
1338 OPEN c_get_alt_code(l_adm_cal,l_adm_cal_seq_num);
1339 FETCH c_get_alt_code INTO l_alt_code_adm;
1340 CLOSE c_get_alt_code;
1341
1342 OPEN cur_user(l_person_id);
1343 FETCH cur_user INTO l_person_name, l_display_name;
1344
1345 IF cur_user%FOUND THEN
1346
1347 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF2');
1348 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1349
1350 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1351 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1352 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1353 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1354
1355 Wf_Admission_Req (l_person_id, l_person_name, l_display_name, l_alt_code_acad, l_alt_code_adm);
1356
1357 ELSE
1358 Wf_Directory.GetRoleName('HZ_PARTY', lv_person_id, l_person_name, l_display_name);
1359
1360 IF l_person_name IS NOT NULL THEN
1361
1362 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF2');
1363 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1364
1365 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1366 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1367 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1368 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1369
1370 Wf_Admission_Req (l_person_id, l_person_name, l_display_name, l_alt_code_acad, l_alt_code_adm);
1371
1372 ELSE
1373 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
1374 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1375
1376 END IF ; /* l_person_name */
1377
1378 END IF; /* cur_user */
1379 CLOSE cur_user;
1380
1381 END IF; /* c_per_adm_req */
1382
1383 END LOOP;
1384
1385 IF l_count = 0 THEN
1386 -- write in the log file that no record exists
1387 -- Invalid parameters entered. Valid combinations for parameters to be entered
1388 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
1389 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
1390 END IF;
1391
1392 CLOSE c_per_adm_req;
1393
1394
1395 ELSIF NVL(p_tracking_type, 'ADM_PROCESSING') = 'POST_ADMISSION' THEN
1396
1397 l_count := 0;
1398
1399 OPEN c_per_post_adm_req;
1400
1401 LOOP
1402
1403 FETCH c_per_post_adm_req INTO l_person_id;
1404
1405 EXIT WHEN c_per_post_adm_req%NOTFOUND;
1406
1407 IF c_per_post_adm_req%FOUND THEN
1408
1409 l_count := l_count+1;
1410
1411 lv_person_id := IGS_GE_NUMBER.TO_CANN(l_person_id);
1412
1413 -- The following code should be uncommented once the TCA HZ.J patch is applied
1414 /* Wf_Directory.GetRoleName('HZ_PARTY', l_person_id, l_person_name, l_display_name);
1415
1416 IF l_person_name IS NOT NULL THEN
1417
1418 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF3');
1419 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1420
1421 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1422 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1423 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1424 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1425
1426 ELSE
1427 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
1428 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1429
1430 END IF; */
1431
1432 ---------------------------------------------------------------------------------
1433 --Get Alternate Code
1434 ---------------------------------------------------------------------------------
1435 OPEN c_get_alt_code(l_acad_cal,l_acad_cal_seq_num);
1436 FETCH c_get_alt_code INTO l_alt_code_acad;
1437 CLOSE c_get_alt_code;
1438
1439 OPEN c_get_alt_code(l_adm_cal,l_adm_cal_seq_num);
1440 FETCH c_get_alt_code INTO l_alt_code_adm;
1441 CLOSE c_get_alt_code;
1442
1443 OPEN cur_user(l_person_id);
1444 FETCH cur_user INTO l_person_name, l_display_name;
1445
1446 IF cur_user%FOUND THEN
1447
1448 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF2');
1449 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1450
1451 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1452 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1453 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1454 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1455
1456 Wf_Post_Adm_Req (l_person_id, l_person_name, l_display_name, l_alt_code_acad, l_alt_code_adm);
1457
1458 ELSE
1459 Wf_Directory.GetRoleName('HZ_PARTY', lv_person_id, l_person_name, l_display_name);
1460
1461 IF l_person_name IS NOT NULL THEN
1462
1463 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF2');
1464 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1465
1466 FND_FILE.PUT_LINE (FND_FILE.LOG, '');
1467 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF6');
1468 FND_MESSAGE.SET_TOKEN ('PNAME', l_display_name);
1469 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1470
1471 Wf_Post_Adm_Req (l_person_id, l_person_name, l_display_name, l_alt_code_acad, l_alt_code_adm);
1472
1473 ELSE
1474 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF4');
1475 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET());
1476
1477 END IF ; /* l_person_name */
1478
1479 END IF; /* cur_user */
1480 CLOSE cur_user;
1481
1482 END IF; /* c_per_post_adm_req */
1483
1484 END LOOP;
1485
1486 IF l_count = 0 THEN
1487 -- write in the log file that no record exists
1488 -- Invalid parameters entered. Valid combinations for parameters to be entered
1489 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APNTF_INV_PRM_COMB');
1490 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
1491 END IF;
1492
1493 CLOSE c_per_post_adm_req;
1494
1495 END IF; /* p_tracking_type */
1496
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 RETCODE := 2;
1500 ERRBUF := fnd_message.get_string( 'IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1501 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1502
1503 IF c_per_adm_req%ISOPEN THEN
1504 CLOSE c_per_adm_req;
1505 END IF;
1506 IF c_per_post_adm_req%ISOPEN THEN
1507 CLOSE c_per_post_adm_req;
1508 END IF;
1509 IF c_get_alt_code%ISOPEN THEN
1510 CLOSE c_get_alt_code;
1511 END IF;
1512 IF c_per_num%ISOPEN THEN
1513 CLOSE c_per_num;
1514 END IF;
1515 IF cur_user%ISOPEN THEN
1516 CLOSE cur_user;
1517 END IF;
1518
1519
1520 END Adm_Application_Req;
1521
1522 /* *************************************************************************************/
1523 -- This Procedure raises an event when the admission requirement concurrent job is submitted.
1524 /* *************************************************************************************/
1525
1526 PROCEDURE Wf_Admission_Req
1527 ( p_applicant_id IN NUMBER,
1528 p_applicant_name IN VARCHAR2,
1529 p_applicant_display_name IN VARCHAR2,
1530 p_alt_code_acad IN VARCHAR2,
1531 p_alt_code_adm IN VARCHAR2
1532 )
1533 IS
1534 l_event_t wf_event_t;
1535 l_parameter_list_t wf_parameter_list_t;
1536 l_itemKey varchar2(100);
1537 l_incomplt_appl_url varchar2(1000);
1538
1539
1540 CURSOR cur_seq IS
1541 SELECT IGS_AD_WF_ADREQ_S.NEXTVAL
1542 FROM dual;
1543
1544
1545 BEGIN
1546
1547 -- initialize the wf_event_t object
1548 --
1549 wf_event_t.Initialize(l_event_t);
1550
1551 OPEN cur_seq ;
1552 FETCH cur_seq INTO l_itemKey ;
1553 CLOSE cur_seq ;
1554
1555
1556
1557 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_ID',p_Value => p_applicant_id, p_parameterlist=>l_parameter_list_t);
1558 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_NAME',p_Value => p_applicant_name, p_parameterlist=>l_parameter_list_t);
1559 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_FULL_NAME',p_Value => p_applicant_display_name, p_parameterlist=>l_parameter_list_t);
1560
1561 wf_event.AddParameterToList ( p_Name => 'IA_ACAD_CALENDAR',p_Value => p_alt_code_acad, p_parameterlist=>l_parameter_list_t);
1562 wf_event.AddParameterToList ( p_Name => 'IA_ADM_CALENDAR',p_Value => p_alt_code_adm, p_parameterlist=>l_parameter_list_t);
1563
1564 --
1565 -- raise the event
1566 --
1567 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.appl.adm_req',
1568 p_event_key => l_itemKey,
1569 p_parameters => l_parameter_list_t);
1570
1571 l_parameter_list_t.delete;
1572
1573 END Wf_Admission_Req ;
1574
1575 /* *************************************************************************************/
1576 -- This Procedure raises an event when the post-admission requirement concurrent job is submitted.
1577 /* *************************************************************************************/
1578
1579 PROCEDURE Wf_Post_Adm_Req
1580 ( p_applicant_id IN NUMBER,
1581 p_applicant_name IN VARCHAR2,
1582 p_applicant_display_name IN VARCHAR2,
1583 p_alt_code_acad IN VARCHAR2,
1584 p_alt_code_adm IN VARCHAR2
1585 )
1586 IS
1587 l_event_t wf_event_t;
1588 l_parameter_list_t wf_parameter_list_t;
1589 l_itemKey varchar2(100);
1590 l_incomplt_appl_url varchar2(1000);
1591
1592
1593 CURSOR cur_seq IS
1594 SELECT IGS_AD_WF_POSTREQ_S.NEXTVAL
1595 FROM dual;
1596
1597
1598 BEGIN
1599
1600 -- initialize the wf_event_t object
1601 --
1602 wf_event_t.Initialize(l_event_t);
1603
1604 OPEN cur_seq ;
1605 FETCH cur_seq INTO l_itemKey ;
1606 CLOSE cur_seq ;
1607
1608
1609 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_ID',p_Value => p_applicant_id, p_parameterlist=>l_parameter_list_t);
1610 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_NAME',p_Value => p_applicant_name, p_parameterlist=>l_parameter_list_t);
1611 wf_event.AddParameterToList ( p_Name => 'IA_PERSON_FULL_NAME',p_Value => p_applicant_display_name, p_parameterlist=>l_parameter_list_t);
1612
1613 wf_event.AddParameterToList ( p_Name => 'IA_ACAD_CALENDAR',p_Value => p_alt_code_acad, p_parameterlist=>l_parameter_list_t);
1614 wf_event.AddParameterToList ( p_Name => 'IA_ADM_CALENDAR',p_Value => p_alt_code_adm, p_parameterlist=>l_parameter_list_t);
1615
1616 --
1617 -- raise the event
1618 --
1619
1620 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.ad.appl.post_adm_req',
1621 p_event_key => l_itemKey,
1622 p_parameters => l_parameter_list_t);
1623
1624 l_parameter_list_t.delete;
1625
1626 END Wf_Post_Adm_Req ;
1627
1628 END IGS_AD_WRKFLOW_PKG;
[Home] [Help]