[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_ASSIGN_REVIEW_GRP
Source
1 PACKAGE BODY igs_ad_assign_review_grp AS
2 /* $Header: IGSADB3B.pls 120.4 2006/02/23 06:21:21 arvsrini noship $ */
3
4 -- declaring constants for the inclusion and exclusion indicator
5 g_incl_ind CONSTANT VARCHAR2(1) := 'I';
6 g_excl_ind CONSTANT VARCHAR2(1) := 'E';
7
8 PROCEDURE assign_review_group(
9 ERRBUF OUT NOCOPY VARCHAR2,
10 RETCODE OUT NOCOPY NUMBER,
11 P_APPL_REV_PROFILE_ID IN NUMBER,
12 P_ENTRY_STAT_ID IN NUMBER,
13 P_NOMINATED_COURSE_CD IN VARCHAR2,
14 P_PERSON_ID IN NUMBER,
15 P_UNIT_SET_CD IN VARCHAR2,
16 P_CALENDAR_DETAILS IN VARCHAR2,
17 P_ADMISSION_PROCESS_CATEGORY IN VARCHAR2,
18 P_ORG_ID IN NUMBER)
19 AS
20 /*************************************************************
21 Created By :samaresh
22 Date : 09-NOV-2001
23 Created By : Sandhya.Amaresh
24 Purpose : This Procedure Assigns Review Groups to Applications
25 that havent been assigned Review Groups
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29 ***************************************************************/
30
31 -- Cursor to fetch the Review Profile Grouping Code given the
32 -- Review Profile Id
33 CURSOR c_appl_revprof_group_cd(cp_appl_rev_profile_id NUMBER) IS
34 SELECT appl_rev_profile_gr_cd,site_use_code,review_profile_name
35 FROM igs_ad_apl_rev_prf_all
36 WHERE appl_rev_profile_id = cp_appl_rev_profile_id;
37
38 -- Cursor to fetch the Review Group Id, Review Group Code, given
39 -- the Review Profile Id
40 CURSOR c_appl_revprof_revgr(cp_appl_rev_profile_id NUMBER) IS
41 SELECT appl_revprof_revgr_id,revprof_revgr_cd,revprof_revgr_name
42 FROM igs_ad_apl_rprf_rgr
43 WHERE appl_rev_profile_id = cp_appl_rev_profile_id
44 ORDER BY appl_revprof_revgr_id;
45
46 -- Cursor to fetch the Inclusion and Exclusion Values given
47 -- the Review Group Id and the Inclusion Exclusion Indicator
48 CURSOR c_revgr_incl_excl(cp_appl_revprof_revgr_id NUMBER,cp_incl_excl_ind VARCHAR2) IS
49 SELECT *
50 FROM igs_ad_rvgr_inc_exc
51 WHERE appl_revprof_revgr_id = cp_appl_revprof_revgr_id
52 AND incl_excl_ind = cp_incl_excl_ind;
53
54 c_revgr_incl_excl_rec c_revgr_incl_excl%ROWTYPE;
55
56 -- Cursor to fetch the Inclusion and Exclusion Values given
57 -- the Review Group Id For the Address
58 CURSOR c_revgr_addr(cp_appl_revprof_revgr_id NUMBER) IS
59 SELECT *
60 FROM igs_ad_rvgr_inc_exc
61 WHERE appl_revprof_revgr_id = cp_appl_revprof_revgr_id ;
62
63 c_revgr_addr_rec c_revgr_addr%ROWTYPE;
64
65 CURSOR c_arp_rec_found (cp_person_id NUMBER,cp_admission_appl_number NUMBER,
66 cp_nominated_course_cd VARCHAR2,cp_sequence_number NUMBER) IS
67 SELECT rowid,arp.*
68 FROM igs_ad_appl_arp arp
69 WHERE person_id = cp_person_id
70 AND admission_appl_number = cp_admission_appl_number
71 AND nominated_course_cd = cp_nominated_course_cd
72 AND sequence_number = cp_sequence_number;
73
74 TYPE rec_persondetail IS RECORD (
75 person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
76 admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
77 nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
78 sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
79
80 TYPE rec_persondet_addr IS RECORD (
81 person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
82 admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
83 nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
84 sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
85 country igs_or_inst_addr.country%TYPE,
86 state igs_or_inst_addr.state%TYPE,
87 postal_code igs_or_inst_addr.postal_code%TYPE);
88
89 TYPE c_ref IS REF CURSOR ;
90
91 l_include_where VARCHAR2(2000) DEFAULT NULL;
92 l_surname_include_where VARCHAR2(2000) DEFAULT NULL;
93 l_curr_inst_addr_include_where VARCHAR2(2000) DEFAULT NULL;
94 l_exclude_where VARCHAR2(2000) DEFAULT NULL;
95 l_surname_exclude_where VARCHAR2(2000) DEFAULT NULL;
96 l_curr_inst_addr_exclude_where VARCHAR2(2000) DEFAULT NULL;
97 l_applicant_addr_exclude_where VARCHAR2(2000) DEFAULT NULL;
98 l_applicant_addr_include_where VARCHAR2(2000) DEFAULT NULL;
99 l_market_code_exclude_where VARCHAR2(2000) DEFAULT NULL;
100 l_market_code_include_where VARCHAR2(2000) DEFAULT NULL;
101 l_prog_of_study_include_where VARCHAR2(2000)DEFAULT NULL;
102 l_organization_include_where VARCHAR2(2000)DEFAULT NULL;
103 l_cal_detls_include_where VARCHAR2(2000) DEFAULT NULL;
104 l_cur_statement VARCHAR2(4000);
105 l_satisfied CONSTANT VARCHAR2(9) := 'SATISFIED';
106 l_pending CONSTANT VARCHAR2(7) := 'PENDING';
107 l_count_incl_excl NUMBER :=0;
108 l_percentage_symbol CONSTANT VARCHAR2(1) :='%';
109 l_cursor_id NUMBER(15);
110 l_num_of_rows NUMBER(10);
111 l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
112 l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
113 l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
114 l_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE;
115 l_debug VARCHAR2(4000);
116
117
118 l_admission_cat igs_ad_appl_all.admission_cat%TYPE;
119 l_s_admission_process_type igs_ad_appl_all.s_admission_process_type%TYPE;
120 l_acad_cal_type igs_ca_inst_all.cal_type%TYPE;
121 l_acad_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
122 l_adm_cal_type igs_ca_inst_all.cal_type%TYPE;
123 l_adm_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
124 l_country igs_or_inst_addr.country%TYPE;
125 l_state igs_or_inst_addr.state%TYPE;
126 l_postal_code igs_or_inst_addr.postal_code%TYPE;
127 l_pe_country igs_pe_addr_v.country_cd%TYPE;
128 l_pe_state igs_pe_addr_v.state%TYPE;
129 l_pe_postal_code igs_pe_addr_v.postal_code%TYPE;
130
131
132 l_addr_include_ind BOOLEAN;
133 l_addr_excluded_ind BOOLEAN;
134
135 c_appl_revprof_group_cd_rec c_appl_revprof_group_cd%ROWTYPE;
136
137 c_incl_excl c_ref;
138 c_appl_addr_rec rec_persondet_addr;
139 c_appl_rec rec_persondetail;
140
141 c_arp_rec_found_rec c_arp_rec_found%ROWTYPE;
142 lv_rowid VARCHAR2(25);
143 lv_appl_arp_id NUMBER(15);
144
145 BEGIN
146 IGS_GE_GEN_003.Set_org_id(p_org_id);
147 -- Check the Review Grouping Name
148 -- If the Grouping is Alpabetical By Surname
149 OPEN c_appl_revprof_group_cd(p_appl_rev_profile_id);
150 FETCH c_appl_revprof_group_cd INTO c_appl_revprof_group_cd_rec;
151 CLOSE c_appl_revprof_group_cd;
152
153 -- Write the Profile Name to the log file
154 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
155 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS',
156 'IGS_AD_APPL_PROF_NAME_PROC'),'27',' ')
157 ||' '|| c_appl_revprof_group_cd_rec.review_profile_name);
158 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
159
160 -- Write the Calander Details to the log file
161 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CL_DTLS');
162 FND_MESSAGE.SET_TOKEN('CLDTLS', p_calendar_details);
163 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
164
165 -- Write the Admission Process Cagtegory Details to the log file
166 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_APC');
167 FND_MESSAGE.SET_TOKEN('APC', p_admission_process_category);
168 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
169
170 IF p_admission_process_category IS NULL THEN
171 l_admission_cat := NULL;
172 l_s_admission_process_type := NULL;
173 ELSE
174 l_admission_cat := RTRIM ( SUBSTR ( p_admission_process_category, 1, 10));
175 l_s_admission_process_type := TRIM ( SUBSTR ( p_admission_process_category, 11));
176 END IF;
177
178
179 IF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'ALPHA_SUR_NAME' THEN
180 -- Open the cursor to fetch the Review Group Ids which belong to this
181 -- particular Review Profile Id
182 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
183
184 -- Write the Group Code to the log file
185 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
186 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS',
187 'IGS_AD_APPL_GRP_CD_PROC'),'27',' ') ||
188 ' '||c_appl_revprof_revgr_rec.revprof_revgr_name);
189 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
190 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS',
191 'IGS_AD_PERSON_ID'),'20',' ')||LPAD(FND_MESSAGE.GET_STRING('IGS',
192 'IGS_AD_APPL_NO'),'20',' ')
193 ||LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
194 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
195
196
197 l_cur_statement := NULL;
198
199 -- Check if there are any include or exclude records for the Group id.
200 OPEN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
201 g_incl_ind);
202 FETCH c_revgr_incl_excl INTO c_revgr_incl_excl_rec;
203 IF c_revgr_incl_excl%FOUND THEN
204 CLOSE c_revgr_incl_excl;
205
206 fnd_dsql.init;
207 fnd_dsql.add_text('SELECT apl.person_id,apl.admission_appl_number,apl.nominated_course_cd,apl.sequence_number ');
208 fnd_dsql.add_text('FROM hz_parties pe, igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app,igs_ad_doc_stat doc, igs_ad_ou_stat ou ');
209 fnd_dsql.add_text('WHERE pe.party_id = apl.person_id AND doc.s_adm_doc_status = ');
210 fnd_dsql.add_bind(l_satisfied);
211 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ');
212 fnd_dsql.add_bind(l_pending);
213 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status ');
214 fnd_dsql.add_text('AND ou.adm_outcome_status = apl.adm_outcome_status AND ((');
215
216 fnd_dsql.add_bind(p_entry_stat_id);
217 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ');
218 fnd_dsql.add_bind(p_entry_stat_id);
219 fnd_dsql.add_text(' ) OR ');
220 fnd_dsql.add_bind(p_entry_stat_id);
221 fnd_dsql.add_text(' IS NULL ) AND ((');
222
223 fnd_dsql.add_bind(p_nominated_course_cd);
224 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ');
225 fnd_dsql.add_bind(p_nominated_course_cd);
226 fnd_dsql.add_text(' ) OR ');
227 fnd_dsql.add_bind(p_nominated_course_cd);
228 fnd_dsql.add_text(' IS NULL ) AND ((');
229
230 fnd_dsql.add_bind(p_person_id);
231 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ');
232 fnd_dsql.add_bind(p_person_id);
233 fnd_dsql.add_text(' ) OR ');
234 fnd_dsql.add_bind(p_person_id);
235 fnd_dsql.add_text(' IS NULL ) AND ((');
236
237 fnd_dsql.add_bind(p_unit_set_cd);
238 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ');
239 fnd_dsql.add_bind(p_unit_set_cd);
240 fnd_dsql.add_text(' ) OR ');
241 fnd_dsql.add_bind(p_unit_set_cd);
242 fnd_dsql.add_text(' IS NULL ) AND ((');
243
244 fnd_dsql.add_bind(l_admission_cat);
245 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ');
246 fnd_dsql.add_bind(l_admission_cat);
247 fnd_dsql.add_text(' ) OR ');
248 fnd_dsql.add_bind(l_admission_cat);
249 fnd_dsql.add_text(' IS NULL ) AND ((');
250
251 fnd_dsql.add_bind(l_s_admission_process_type);
252 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ');
253 fnd_dsql.add_bind(l_s_admission_process_type);
254 fnd_dsql.add_text(' ) OR ');
255 fnd_dsql.add_bind(l_s_admission_process_type);
256 fnd_dsql.add_text(' IS NULL ) AND ');
257
258 fnd_dsql.add_text('apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number');
259
260 IF p_calendar_details IS NOT NULL THEN
261 -- Get the Academic Calander details form the Academic Calender Parameter
262 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
263 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
264
265 -- Get the Admission Calander details form the Admission Calender Parameter
266 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
267 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
268
269
270 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
271 fnd_dsql.add_text(' 1=2 ' );
272 ELSE
273 fnd_dsql.add_text(' AND app.acad_cal_type = ');
274 fnd_dsql.add_bind(l_acad_cal_type);
275
276 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
277 fnd_dsql.add_bind(l_acad_ci_sequence_number);
278
279 fnd_dsql.add_text(' AND app.adm_cal_type = ');
280 fnd_dsql.add_bind(l_adm_cal_type);
281
282 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
283 fnd_dsql.add_bind(l_adm_ci_sequence_number);
284
285 END IF;
286 END IF;
287
288 OPEN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
289 g_excl_ind);
290 FETCH c_revgr_incl_excl INTO c_revgr_incl_excl_rec;
291 IF c_revgr_incl_excl%FOUND THEN
292 CLOSE c_revgr_incl_excl;
293
294 fnd_dsql.add_text(' AND pe.person_last_name IN ( ( SELECT person_last_name ');
295 fnd_dsql.add_text(' FROM hz_parties WHERE ');
296
297 l_count_incl_excl:=0;
298
299 -- Open a cursor to fetch all the include Records and combine them to form a where clause
300 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
301 g_incl_ind) LOOP
302 IF (l_count_incl_excl > 0) THEN
303 fnd_dsql.add_text(' OR ');
304 END IF;
305
306 fnd_dsql.add_text(' ((UPPER(person_last_name) BETWEEN NVL(UPPER( ');
307 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
308 fnd_dsql.add_text(' ),');
309 fnd_dsql.add_bind(l_percentage_symbol);
310 fnd_dsql.add_text(' ) AND NVL(UPPER(');
311 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
312 fnd_dsql.add_text(' ),');
313 fnd_dsql.add_bind(l_percentage_symbol);
314 fnd_dsql.add_text(' ) ) OR UPPER(person_last_name) LIKE NVL(UPPER(');
315 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
316 fnd_dsql.add_text('),');
317 fnd_dsql.add_bind(l_percentage_symbol);
318 fnd_dsql.add_text(' ))');
319
320 l_count_incl_excl:=l_count_incl_excl+1;
321
322 END LOOP;
323
324
325 fnd_dsql.add_text(' )');
326
327 l_count_incl_excl:=0;
328 fnd_dsql.add_text(' MINUS ( SELECT person_last_name FROM hz_parties WHERE ');
329
330 -- Open a cursor to fetch all the exclusion Records and combine them to form a where clause
331 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
332 g_excl_ind) LOOP
333
334 IF (l_count_incl_excl > 0) THEN
335 fnd_dsql.add_text(' OR ');
336 END IF;
337
338 fnd_dsql.add_text(' ((UPPER(person_last_name) BETWEEN NVL(UPPER( ');
339 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
340 fnd_dsql.add_text(' ),');
341 fnd_dsql.add_bind(l_percentage_symbol);
342 fnd_dsql.add_text(' ) AND NVL(UPPER(');
343 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
344 fnd_dsql.add_text(' ),');
345 fnd_dsql.add_bind(l_percentage_symbol);
346 fnd_dsql.add_text(' ) ) OR UPPER(person_last_name) LIKE NVL(UPPER(');
347 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
348 fnd_dsql.add_text(' ),');
349 fnd_dsql.add_bind(l_percentage_symbol);
350 fnd_dsql.add_text(' ))');
351
352 l_count_incl_excl:=l_count_incl_excl+1;
353 END LOOP;
354
355 fnd_dsql.add_text(' ))');
356 l_count_incl_excl:=0;
357
358
359 ELSE
360 CLOSE c_revgr_incl_excl;
361 fnd_dsql.add_text(' AND pe.person_last_name IN ( SELECT person_last_name ');
362 fnd_dsql.add_text(' FROM hz_parties WHERE ');
363
364 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
365 g_incl_ind) LOOP
366 IF (l_count_incl_excl > 0) THEN
367 fnd_dsql.add_text(' OR ');
368 END IF;
369 fnd_dsql.add_text(' ((UPPER(person_last_name) BETWEEN NVL(UPPER( ');
370 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
371 fnd_dsql.add_text(' ),');
372 fnd_dsql.add_bind(l_percentage_symbol);
373 fnd_dsql.add_text(' ) AND NVL(UPPER(');
374 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
375 fnd_dsql.add_text(' ),');
376 fnd_dsql.add_bind(l_percentage_symbol);
377 fnd_dsql.add_text(' ) ) OR UPPER(person_last_name) LIKE NVL(UPPER(');
378 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
379 fnd_dsql.add_text(' ),');
380 fnd_dsql.add_bind(l_percentage_symbol);
381 fnd_dsql.add_text(' ))');
382
383 l_count_incl_excl:=l_count_incl_excl+1;
384
385 END LOOP;
386
387 l_count_incl_excl:=0;
388 fnd_dsql.add_text(' )');
389
390 END IF; -- End of checking presence of exclude records
391
392 l_cur_statement := fnd_dsql.get_text(FALSE);
393 l_cursor_id := dbms_sql.open_cursor;
394 fnd_dsql.set_cursor(l_cursor_id);
395
396 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
397 fnd_dsql.do_binds;
398
399 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
400 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
401 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
402 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
403
404
405 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
406
407 LOOP
408 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
409 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
410 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
411 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
412 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
413
414 OPEN c_arp_rec_found(l_person_id, l_admission_appl_number, l_nominated_course_cd, l_sequence_number);
415 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
416 IF c_arp_rec_found%NOTFOUND THEN
417 -- Insert Using TableHandler
418 lv_rowid := NULL;
419 lv_appl_arp_id := NULL;
420 igs_ad_appl_arp_pkg.insert_row (
421 x_rowid => lv_rowid,
422 x_appl_arp_id => lv_appl_arp_id,
423 x_person_id => l_person_id,
424 x_admission_appl_number => l_admission_appl_number,
425 x_nominated_course_cd => l_nominated_course_cd,
426 x_sequence_number => l_sequence_number,
427 x_appl_rev_profile_id => p_appl_rev_profile_id,
428 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
429 x_mode => 'R');
430
431 -- Write the Application Instance which got Assigned to a
432 -- Particular Review Code to the Log file
433 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
434 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
435 LPAD(l_nominated_course_cd,'15',' ')||
436 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
437 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
438 -- Update Using Table Handler
439 igs_ad_appl_arp_pkg.update_row (
440 x_rowid => c_arp_rec_found_rec.rowid,
441 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
442 x_person_id => c_arp_rec_found_rec.person_id,
443 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
444 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
445 x_sequence_number => c_arp_rec_found_rec.sequence_number,
446 x_appl_rev_profile_id => p_appl_rev_profile_id,
447 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
448 x_mode => 'R');
449
450 -- Write the Application Instance which got Assigned to a
451 --Particular Review Code to the Log file
452 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
453 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
454 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
455 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
456 END IF;
457 CLOSE c_arp_rec_found;
458
459
460 END LOOP;
461 dbms_sql.close_cursor(l_cursor_id);
462
463 -- No include or exclude Records for this Group Code
464 ELSE
465 CLOSE c_revgr_incl_excl;
466 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
467 END IF;
468 -- Loop through the next Review Group Code for the Profile ID
469 END LOOP;
470
471
472
473
474
475
476 -- Check if the Review Group Code is Geographical by Insitution Address
477 ELSIF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'GEO_BY_INSTITUTION_ADDR' THEN
478 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
479 -- Write the Group Code to the log file
480 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
481 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_GRP_CD_PROC'),'27',' ') ||
482 ' ' ||c_appl_revprof_revgr_rec.revprof_revgr_name);
483 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
484 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'),'20',' ')||
485 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'),'20',' ')
486 ||LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
487 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
488
489 l_cur_statement := NULL;
490
491 -- Check if there are any include or exclude records for the Group id.
492 OPEN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id);
493 FETCH c_revgr_addr INTO c_revgr_addr_rec;
494 IF c_revgr_addr%FOUND THEN
495 CLOSE c_revgr_addr;
496
497 fnd_dsql.init;
498 fnd_dsql.add_text('SELECT apl.person_id,apl.admission_appl_number, apl.nominated_course_cd, apl.sequence_number, addr.country, addr.state ,addr.postal_code');
499 fnd_dsql.add_text(' FROM igs_pe_hz_parties hp, igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app, igs_ad_doc_stat doc, igs_ad_ou_stat ou, igs_or_inst_addr addr, igs_ad_acad_history_v aah ');
500 fnd_dsql.add_text(' WHERE hp.party_id = apl.person_id AND hp.party_id = aah.person_id AND aah.CURRENT_INST = ');
501 fnd_dsql.add_bind('Y');
502 fnd_dsql.add_text(' AND aah.institution_code = addr.institution_cd AND addr.addr_type = ' );
503 fnd_dsql.add_bind(c_appl_revprof_group_cd_rec.site_use_code);
504 fnd_dsql.add_text(' AND doc.s_adm_doc_status = ');
505 fnd_dsql.add_bind(l_satisfied);
506 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ');
507 fnd_dsql.add_bind(l_pending);
508 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status AND ou.adm_outcome_status = apl.adm_outcome_status AND (( ');
509
510 fnd_dsql.add_bind(p_entry_stat_id);
511 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ' );
512 fnd_dsql.add_bind(p_entry_stat_id);
513 fnd_dsql.add_text(' ) OR ' );
514 fnd_dsql.add_bind(p_entry_stat_id );
515 fnd_dsql.add_text(' IS NULL ) AND (( ');
516
517 fnd_dsql.add_bind(p_nominated_course_cd );
518 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ');
519 fnd_dsql.add_bind(p_nominated_course_cd);
520 fnd_dsql.add_text(' ) OR ' );
521 fnd_dsql.add_bind(p_nominated_course_cd );
522 fnd_dsql.add_text(' IS NULL ) AND (( ');
523
524 fnd_dsql.add_bind(p_person_id );
525 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ' );
526 fnd_dsql.add_bind(p_person_id );
527 fnd_dsql.add_text(' ) OR ' );
528 fnd_dsql.add_bind(p_person_id );
529 fnd_dsql.add_text(' IS NULL ) AND (( ');
530
531 fnd_dsql.add_bind(p_unit_set_cd );
532 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ');
533 fnd_dsql.add_bind(p_unit_set_cd);
534 fnd_dsql.add_text(' ) OR ' );
535 fnd_dsql.add_bind(p_unit_set_cd );
536 fnd_dsql.add_text(' IS NULL ) AND (( ');
537
538 fnd_dsql.add_bind(l_admission_cat );
539 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ' );
540 fnd_dsql.add_bind(l_admission_cat );
541 fnd_dsql.add_text(' ) OR ' );
542 fnd_dsql.add_bind(l_admission_cat );
543 fnd_dsql.add_text(' IS NULL ) AND (( ');
544
545 fnd_dsql.add_bind(l_s_admission_process_type );
546 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ' );
547 fnd_dsql.add_bind(l_s_admission_process_type);
548 fnd_dsql.add_text(' ) OR ' );
549 fnd_dsql.add_bind(l_s_admission_process_type );
550
551 fnd_dsql.add_text(' IS NULL ) AND apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number ');
552
553 IF p_calendar_details IS NOT NULL THEN
554 -- Get the Academic Calander details form the Academic Calender Parameter
555 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
556 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
557
558 -- Get the Admission Calander details form the Admission Calender Parameter
559 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
560 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
561
562
563 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
564 fnd_dsql.add_text(' 1=2 ' );
565 ELSE
566 fnd_dsql.add_text(' AND app.acad_cal_type = ');
567 fnd_dsql.add_bind(l_acad_cal_type);
568
569 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
570 fnd_dsql.add_bind(l_acad_ci_sequence_number);
571
572 fnd_dsql.add_text(' AND app.adm_cal_type = ');
573 fnd_dsql.add_bind(l_adm_cal_type);
574
575 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
576 fnd_dsql.add_bind(l_adm_ci_sequence_number);
577
578 END IF;
579 END IF;
580
581 l_addr_include_ind := FALSE;
582 l_addr_excluded_ind := FALSE;
583
584 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
585 LOOP
586 IF c_revgr_addr_rec.incl_excl_ind IS NULL OR c_revgr_addr_rec.incl_excl_ind = 'I' THEN
587 l_addr_include_ind := TRUE;
588 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
589 l_addr_excluded_ind := TRUE;
590 END IF;
591
592 END LOOP;
593
594 IF (l_addr_include_ind) /*IS TRUE */THEN
595
596 fnd_dsql.add_text(' AND (');
597 l_count_incl_excl:=0;
598
599 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
600 LOOP
601 -- If the Current Record had include for the State
602 IF c_revgr_addr_rec.incl_excl_ind IS NULL THEN
603
604 IF (l_count_incl_excl > 0) THEN
605 fnd_dsql.add_text(' OR ');
606 END IF;
607
608 fnd_dsql.add_text(' (');
609
610 fnd_dsql.add_text(' addr.country = ');
611 fnd_dsql.add_bind(c_revgr_addr_rec.country);
612
613 -- If the Current Record had include for the Postal Code
614 IF c_revgr_addr_rec.postal_incl_excl_ind = 'I' THEN
615 fnd_dsql.add_text(' AND ( UPPER(addr.postal_code) BETWEEN NVL(UPPER(');
616 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
617 fnd_dsql.add_text(' ),');
618 fnd_dsql.add_bind(l_percentage_symbol);
619 fnd_dsql.add_text(' ) AND NVL( UPPER(');
620 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
621 fnd_dsql.add_text(' ),');
622 fnd_dsql.add_bind(l_percentage_symbol);
623 fnd_dsql.add_text(' ) OR addr.postal_code LIKE ');
624 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
625 fnd_dsql.add_text(' )');
626
627
628 -- If the Current Recode had exclude for the Postal Code
629 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
630 fnd_dsql.add_text(' AND (UPPER(addr.postal_code) NOT BETWEEN NVL(UPPER(');
631 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
632 fnd_dsql.add_text(' ),');
633 fnd_dsql.add_bind(l_percentage_symbol);
634 fnd_dsql.add_text(' ) AND NVL(UPPER(');
635 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
636 fnd_dsql.add_text(' ),');
637 fnd_dsql.add_bind(l_percentage_symbol);
638 fnd_dsql.add_text(' ) AND addr.postal_code NOT LIKE ');
639 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
640 fnd_dsql.add_text(' )');
641 END IF;
642
643 fnd_dsql.add_text(' )');
644 l_count_incl_excl:=l_count_incl_excl+1;
645
646
647 ELSIF c_revgr_addr_rec.incl_excl_ind = 'I' THEN
648
649 IF (l_count_incl_excl > 0) THEN
650 fnd_dsql.add_text(' OR ');
651 END IF;
652
653 fnd_dsql.add_text(' (');
654
655 fnd_dsql.add_text(' addr.country = ');
656 fnd_dsql.add_bind(c_revgr_addr_rec.country);
657 fnd_dsql.add_text(' AND ( UPPER(addr.state) BETWEEN NVL(UPPER(');
658 fnd_dsql.add_bind(c_revgr_addr_rec.start_value);
659 fnd_dsql.add_text(' ),');
660 fnd_dsql.add_bind(l_percentage_symbol);
661 fnd_dsql.add_text(' ) AND NVL(UPPER(');
662 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
663 fnd_dsql.add_text(' ),');
664 fnd_dsql.add_bind(l_percentage_symbol);
665 fnd_dsql.add_text(' ) OR addr.state LIKE ');
666 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
667 fnd_dsql.add_text(' )');
668
669 -- If the Current Record had include for the Postal Code
670 IF c_revgr_addr_rec.postal_incl_excl_ind = 'I' THEN
671 fnd_dsql.add_text(' AND ( UPPER(addr.postal_code) BETWEEN NVL(UPPER(');
672 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
673 fnd_dsql.add_text(' ),');
674 fnd_dsql.add_bind(l_percentage_symbol);
675 fnd_dsql.add_text(' ) AND NVL(UPPER(');
676 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
677 fnd_dsql.add_text(' ),');
678 fnd_dsql.add_bind(l_percentage_symbol);
679 fnd_dsql.add_text(' ) OR addr.postal_code LIKE ');
680 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
681 fnd_dsql.add_text(' )') ;
682 -- If the Current Recode had exclude for the Postal Code
683 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
684 fnd_dsql.add_text(' AND (UPPER(addr.postal_code) NOT BETWEEN NVL(UPPER(');
685 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
686 fnd_dsql.add_text(' ),');
687 fnd_dsql.add_bind(l_percentage_symbol);
688 fnd_dsql.add_text(' ) AND NVL(UPPER(');
689 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
690 fnd_dsql.add_text(' ),');
691 fnd_dsql.add_bind(l_percentage_symbol);
692 fnd_dsql.add_text(' ) AND addr.postal_code NOT LIKE ');
693 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
694 fnd_dsql.add_text(' )' );
695 END IF;
696
697 fnd_dsql.add_text(' )');
698 l_count_incl_excl:=l_count_incl_excl+1;
699
700
701 END IF;
702 END LOOP;
703
704 l_count_incl_excl:=0;
705 fnd_dsql.add_text(' )');
706
707 IF (l_addr_excluded_ind ) /*IS TRUE*/ THEN
708 --logic for removing excludes
709 fnd_dsql.add_text(' AND NOT (');
710 l_count_incl_excl:=0;
711
712 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
713 LOOP
714 IF c_revgr_addr_rec.incl_excl_ind = 'E' THEN
715 IF (l_count_incl_excl > 0) THEN
716 fnd_dsql.add_text(' OR ');
717 END IF;
718
719 fnd_dsql.add_text(' (');
720
721 fnd_dsql.add_text(' addr.country = ');
722 fnd_dsql.add_bind(c_revgr_addr_rec.country);
723 fnd_dsql.add_text(' AND ( UPPER(addr.state) BETWEEN NVL(UPPER(');
724 fnd_dsql.add_bind(c_revgr_addr_rec.start_value);
725 fnd_dsql.add_text(' ),');
726 fnd_dsql.add_bind(l_percentage_symbol);
727 fnd_dsql.add_text(' ) AND NVL(UPPER(');
728 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
729 fnd_dsql.add_text(' ),');
730 fnd_dsql.add_bind(l_percentage_symbol);
731 fnd_dsql.add_text(' ) OR addr.state LIKE ');
732 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
733 fnd_dsql.add_text(' )');
734
735 fnd_dsql.add_text(' )');
736 l_count_incl_excl:=l_count_incl_excl+1;
737
738 END IF;
739 END LOOP;
740
741 l_count_incl_excl:=0;
742 fnd_dsql.add_text(' )');
743 END IF;
744
745 END IF;
746
747 l_cur_statement := fnd_dsql.get_text(FALSE);
748 l_debug := fnd_dsql.get_text(TRUE);
749
750
751 l_cursor_id := dbms_sql.open_cursor;
752 fnd_dsql.set_cursor(l_cursor_id);
753
754 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
755 fnd_dsql.do_binds;
756
757 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
758 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
759 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
760 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
761 dbms_sql.define_column(l_cursor_id, 5, l_country, 60);
762 dbms_sql.define_column(l_cursor_id, 6, l_state, 60);
763 dbms_sql.define_column(l_cursor_id, 7, l_postal_code, 60);
764
765
766
767 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
768
769 /*for debugging*/
770 l_debug := fnd_dsql.get_text(TRUE);
771
772
773 LOOP
774 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
775 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
776 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
777 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
778 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
779 dbms_sql.column_value(l_cursor_id, 5, l_country);
780 dbms_sql.column_value(l_cursor_id, 6, l_state);
781 dbms_sql.column_value(l_cursor_id, 7, l_postal_code);
782
783
784 OPEN c_arp_rec_found(l_person_id,l_admission_appl_number,
785 l_nominated_course_cd,l_sequence_number);
786 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
787 IF c_arp_rec_found%NOTFOUND THEN
788 -- Insert Using TableHandler
789 lv_rowid := NULL;
790 lv_appl_arp_id := NULL;
791 igs_ad_appl_arp_pkg.insert_row (
792 x_rowid => lv_rowid,
793 x_appl_arp_id => lv_appl_arp_id,
794 x_person_id => l_person_id,
795 x_admission_appl_number => l_admission_appl_number,
796 x_nominated_course_cd => l_nominated_course_cd,
797 x_sequence_number => l_sequence_number,
798 x_appl_rev_profile_id => p_appl_rev_profile_id,
799 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
800 x_mode => 'R');
801
802 -- Write the Application Instance
803 --which got Assigned to a Particular Review Code to the Log file
804 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
805 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
806 LPAD(l_nominated_course_cd,'15',' ')||
807 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
808
809 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
810 -- Update Using Table Handler
811 igs_ad_appl_arp_pkg.update_row (
812 x_rowid => c_arp_rec_found_rec.rowid,
813 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
814 x_person_id => c_arp_rec_found_rec.person_id,
815 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
816 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
817 x_sequence_number => c_arp_rec_found_rec.sequence_number,
818 x_appl_rev_profile_id => p_appl_rev_profile_id,
819 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
820 x_mode => 'R');
821
822 -- Write the Application Instance which got Assigned to a
823 -- Particular Review Code to the Log file
824 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
825 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
826 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
827 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
828
829 END IF;
830 CLOSE c_arp_rec_found;
831 END LOOP;
832 dbms_sql.close_cursor(l_cursor_id);
833
834 -- No include or exclude Records for this Group Code
835 ELSE
836 CLOSE c_revgr_addr;
837 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
838 END IF;
839 -- Loop through the next Review Group Code for the Profile ID
840 END LOOP;
841
842
843
844
845
846
847
848 -- Check if the Review Group Code is Geographical by Applicant Address
849 ELSIF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'GEO_BY_APPLICANT_ADDR' THEN
850 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
851 -- Write the Group Code to the log file
852 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
853 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_GRP_CD_PROC'),'27',' ')
854 ||' '||c_appl_revprof_revgr_rec.revprof_revgr_name);
855 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
856 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'),'20',' ')||
857 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'),'20',' ')||
858 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
859 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
860 l_cur_statement := NULL;
861
862 -- Check if there are any include or exclude records for the Group id.
863 OPEN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id);
864 FETCH c_revgr_addr INTO c_revgr_addr_rec;
865 IF c_revgr_addr%FOUND THEN
866 CLOSE c_revgr_addr;
867
868
869 fnd_dsql.init;
870 fnd_dsql.add_text(' SELECT apl.person_id,apl.admission_appl_number, apl.nominated_course_cd, apl.sequence_number , addr.country_cd, addr.state, addr.postal_code ');
871 fnd_dsql.add_text(' FROM hz_parties hp, hz_party_site_uses psu, igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app, igs_ad_doc_stat doc, igs_ad_ou_stat ou, igs_pe_addr_v addr ');
872 fnd_dsql.add_text(' WHERE hp.party_id = apl.person_id AND hp.party_id = addr.person_id AND addr.party_site_id = psu.party_site_id AND psu.site_use_type = ');
873 fnd_dsql.add_bind(c_appl_revprof_group_cd_rec.site_use_code);
874 fnd_dsql.add_text(' AND doc.s_adm_doc_status = ' );
875 fnd_dsql.add_bind(l_satisfied);
876 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ' );
877 fnd_dsql.add_bind(l_pending);
878 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status AND ou.adm_outcome_status = apl.adm_outcome_status AND (( ');
879
880 fnd_dsql.add_bind(p_entry_stat_id);
881 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ');
882 fnd_dsql.add_bind(p_entry_stat_id );
883 fnd_dsql.add_text(' ) OR ' );
884 fnd_dsql.add_bind(p_entry_stat_id );
885 fnd_dsql.add_text(' IS NULL ) AND (( ');
886
887 fnd_dsql.add_bind(p_nominated_course_cd);
888 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ' );
889 fnd_dsql.add_bind(p_nominated_course_cd );
890 fnd_dsql.add_text(' ) OR ' );
891 fnd_dsql.add_bind(p_nominated_course_cd);
892 fnd_dsql.add_text(' IS NULL ) AND (( ');
893
894 fnd_dsql.add_bind(p_person_id );
895 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ' );
896 fnd_dsql.add_bind(p_person_id );
897 fnd_dsql.add_text(' ) OR ' );
898 fnd_dsql.add_bind(p_person_id );
899 fnd_dsql.add_text(' IS NULL ) AND (( ');
900
901 fnd_dsql.add_bind(p_unit_set_cd );
902 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ');
903 fnd_dsql.add_bind(p_unit_set_cd );
904 fnd_dsql.add_text(' ) OR ' );
905 fnd_dsql.add_bind(p_unit_set_cd );
906 fnd_dsql.add_text(' IS NULL ) AND (( ');
907
908 fnd_dsql.add_bind(l_admission_cat );
909 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ' );
910 fnd_dsql.add_bind(l_admission_cat );
911 fnd_dsql.add_text(' ) OR ' );
912 fnd_dsql.add_bind(l_admission_cat );
913 fnd_dsql.add_text(' IS NULL ) AND (( ');
914
915 fnd_dsql.add_bind(l_s_admission_process_type );
916 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ' );
917 fnd_dsql.add_bind(l_s_admission_process_type );
918 fnd_dsql.add_text(' ) OR ' );
919 fnd_dsql.add_bind(l_s_admission_process_type );
920 fnd_dsql.add_text(' IS NULL ) AND apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number ');
921
922
923 IF p_calendar_details IS NOT NULL THEN
924 -- Get the Academic Calander details form the Academic Calender Parameter
925 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
926 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
927
928 -- Get the Admission Calander details form the Admission Calender Parameter
929 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
930 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
931
932
933 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
934 fnd_dsql.add_text(' 1=2 ' );
935 ELSE
936 fnd_dsql.add_text(' AND app.acad_cal_type = ');
937 fnd_dsql.add_bind(l_acad_cal_type);
938
939 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
940 fnd_dsql.add_bind(l_acad_ci_sequence_number);
941
942 fnd_dsql.add_text(' AND app.adm_cal_type = ');
943 fnd_dsql.add_bind(l_adm_cal_type);
944
945 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
946 fnd_dsql.add_bind(l_adm_ci_sequence_number);
947
948 END IF;
949 END IF;
950
951
952 l_addr_include_ind := FALSE;
953 l_addr_excluded_ind := FALSE;
954
955 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
956 LOOP
957 IF c_revgr_addr_rec.incl_excl_ind IS NULL OR c_revgr_addr_rec.incl_excl_ind = 'I' THEN
958 l_addr_include_ind := TRUE;
959 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
960 l_addr_excluded_ind := TRUE;
961 END IF;
962
963 END LOOP;
964 IF l_addr_include_ind /*IS TRUE */THEN
965
966 fnd_dsql.add_text(' AND (');
967 l_count_incl_excl:=0;
968
969 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
970 LOOP
971 -- If the Current Record had include for the State
972 IF c_revgr_addr_rec.incl_excl_ind IS NULL THEN
973
974 IF (l_count_incl_excl > 0) THEN
975 fnd_dsql.add_text(' OR ');
976 END IF;
977
978 fnd_dsql.add_text(' (');
979
980 fnd_dsql.add_text(' addr.country_cd =');
981 fnd_dsql.add_bind(c_revgr_addr_rec.country);
982
983 -- If the Current Record had include for the Postal Code
984 IF c_revgr_addr_rec.postal_incl_excl_ind = 'I' THEN
985 fnd_dsql.add_text(' AND ( UPPER(addr.postal_code) BETWEEN NVL(UPPER(');
986 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
987 fnd_dsql.add_text(' ),');
988 fnd_dsql.add_bind(l_percentage_symbol);
989 fnd_dsql.add_text(' ) AND NVL( UPPER(');
990 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
991 fnd_dsql.add_text(' ),');
992 fnd_dsql.add_bind(l_percentage_symbol);
993 fnd_dsql.add_text(' ) OR addr.postal_code LIKE ');
994 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
995 fnd_dsql.add_text(' )');
996
997 -- If the Current Recode had exclude for the Postal Code
998 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
999 fnd_dsql.add_text(' AND (UPPER(addr.postal_code) NOT BETWEEN NVL(UPPER(');
1000 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
1001 fnd_dsql.add_text(' ),');
1002 fnd_dsql.add_bind(l_percentage_symbol);
1003 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1004 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
1005 fnd_dsql.add_text(' ),');
1006 fnd_dsql.add_bind(l_percentage_symbol);
1007 fnd_dsql.add_text(' ) AND addr.postal_code NOT LIKE ');
1008 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
1009 fnd_dsql.add_text(' )');
1010 END IF;
1011
1012 fnd_dsql.add_text(' )');
1013 l_count_incl_excl:=l_count_incl_excl+1;
1014
1015
1016 ELSIF c_revgr_addr_rec.incl_excl_ind = 'I' THEN
1017
1018 IF (l_count_incl_excl > 0) THEN
1019 fnd_dsql.add_text(' OR ');
1020 END IF;
1021
1022 fnd_dsql.add_text(' (');
1023
1024 fnd_dsql.add_text(' addr.country_cd =');
1025 fnd_dsql.add_bind(c_revgr_addr_rec.country);
1026 fnd_dsql.add_text(' AND ( UPPER(addr.state) BETWEEN NVL(UPPER(');
1027 fnd_dsql.add_bind(c_revgr_addr_rec.start_value);
1028 fnd_dsql.add_text(' ),');
1029 fnd_dsql.add_bind(l_percentage_symbol);
1030 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1031 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
1032 fnd_dsql.add_text(' ),');
1033 fnd_dsql.add_bind(l_percentage_symbol);
1034 fnd_dsql.add_text(' ) OR addr.state LIKE ');
1035 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
1036 fnd_dsql.add_text(' )');
1037
1038
1039 -- If the Current Record had include for the Postal Code
1040 IF c_revgr_addr_rec.postal_incl_excl_ind = 'I' THEN
1041 fnd_dsql.add_text(' AND ( UPPER(addr.postal_code) BETWEEN NVL(UPPER(');
1042 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
1043 fnd_dsql.add_text(' ),');
1044 fnd_dsql.add_bind(l_percentage_symbol);
1045 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1046 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
1047 fnd_dsql.add_text(' ),');
1048 fnd_dsql.add_bind(l_percentage_symbol);
1049 fnd_dsql.add_text(' ) OR addr.postal_code LIKE ');
1050 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
1051 fnd_dsql.add_text(' )') ;
1052
1053
1054 -- If the Current Recode had exclude for the Postal Code
1055 ELSIF c_revgr_addr_rec.postal_incl_excl_ind = 'E' THEN
1056 fnd_dsql.add_text(' AND (UPPER(addr.postal_code) NOT BETWEEN NVL(UPPER(');
1057 fnd_dsql.add_bind(c_revgr_addr_rec.postal_start_value);
1058 fnd_dsql.add_text(' ),');
1059 fnd_dsql.add_bind(l_percentage_symbol);
1060 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1061 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value );
1062 fnd_dsql.add_text(' ),');
1063 fnd_dsql.add_bind(l_percentage_symbol);
1064 fnd_dsql.add_text(' ) AND addr.postal_code NOT LIKE ');
1065 fnd_dsql.add_bind(c_revgr_addr_rec.postal_end_value);
1066 fnd_dsql.add_text(' )' );
1067 END IF;
1068
1069
1070 fnd_dsql.add_text(' )');
1071 l_count_incl_excl:=l_count_incl_excl+1;
1072
1073
1074 END IF;
1075 END LOOP;
1076
1077 l_count_incl_excl:=0;
1078 fnd_dsql.add_text(' )');
1079
1080 IF l_addr_excluded_ind /*IS TRUE*/ THEN
1081 --logic for removing excludes
1082 fnd_dsql.add_text(' AND NOT (');
1083 l_count_incl_excl:=0;
1084
1085 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
1086 LOOP
1087 IF c_revgr_addr_rec.incl_excl_ind = 'E' THEN
1088 IF (l_count_incl_excl > 0) THEN
1089 fnd_dsql.add_text(' OR ');
1090 END IF;
1091
1092 fnd_dsql.add_text(' (');
1093
1094 fnd_dsql.add_text(' addr.country_cd = ');
1095 fnd_dsql.add_bind(c_revgr_addr_rec.country);
1096 fnd_dsql.add_text(' AND ( UPPER(addr.state) BETWEEN NVL(UPPER(');
1097 fnd_dsql.add_bind(c_revgr_addr_rec.start_value);
1098 fnd_dsql.add_text(' ),');
1099 fnd_dsql.add_bind(l_percentage_symbol);
1100 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1101 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
1102 fnd_dsql.add_text(' ),');
1103 fnd_dsql.add_bind(l_percentage_symbol);
1104 fnd_dsql.add_text(' ) OR addr.state LIKE ');
1105 fnd_dsql.add_bind(c_revgr_addr_rec.end_value);
1106 fnd_dsql.add_text(' )');
1107
1108
1109 fnd_dsql.add_text(' )');
1110 l_count_incl_excl:=l_count_incl_excl+1;
1111
1112 END IF;
1113 END LOOP;
1114
1115 l_count_incl_excl:=0;
1116 fnd_dsql.add_text(' )');
1117 END IF;
1118
1119 END IF;
1120
1121
1122 l_cur_statement := fnd_dsql.get_text(FALSE);
1123
1124 l_cursor_id := dbms_sql.open_cursor;
1125 fnd_dsql.set_cursor(l_cursor_id);
1126
1127 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
1128 fnd_dsql.do_binds;
1129
1130 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
1131 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
1132 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
1133 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
1134 dbms_sql.define_column(l_cursor_id, 5, l_pe_country, 60);
1135 dbms_sql.define_column(l_cursor_id, 6, l_pe_state, 60);
1136 dbms_sql.define_column(l_cursor_id, 7, l_pe_postal_code, 60);
1137
1138
1139 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
1140
1141 /*for debugging*/
1142 l_debug := fnd_dsql.get_text(TRUE);
1143
1144
1145 LOOP
1146 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
1147 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
1148 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
1149 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
1150 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
1151 dbms_sql.column_value(l_cursor_id, 5, l_pe_country);
1152 dbms_sql.column_value(l_cursor_id, 6, l_pe_state);
1153 dbms_sql.column_value(l_cursor_id, 7, l_pe_postal_code);
1154
1155
1156 OPEN c_arp_rec_found(l_person_id,l_admission_appl_number,
1157 l_nominated_course_cd,l_sequence_number);
1158 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
1159 IF c_arp_rec_found%NOTFOUND THEN
1160 -- Insert Using TableHandler
1161 lv_rowid := NULL;
1162 lv_appl_arp_id := NULL;
1163 igs_ad_appl_arp_pkg.insert_row (
1164 x_rowid => lv_rowid,
1165 x_appl_arp_id => lv_appl_arp_id,
1166 x_person_id => l_person_id,
1167 x_admission_appl_number => l_admission_appl_number,
1168 x_nominated_course_cd => l_nominated_course_cd,
1169 x_sequence_number => l_sequence_number,
1170 x_appl_rev_profile_id => p_appl_rev_profile_id,
1171 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1172 x_mode => 'R');
1173
1174 -- Write the Application Instance which got
1175 --Assigned to a Particular Review Code to the Log file
1176 FND_FILE.PUT_LINE(FND_FILE.LOG,
1177 LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
1178 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
1179 LPAD(l_nominated_course_cd,'15',' ')||
1180 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
1181
1182 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
1183 -- Update Using Table Handler
1184 igs_ad_appl_arp_pkg.update_row (
1185 x_rowid => c_arp_rec_found_rec.rowid,
1186 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
1187 x_person_id => c_arp_rec_found_rec.person_id,
1188 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
1189 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
1190 x_sequence_number => c_arp_rec_found_rec.sequence_number,
1191 x_appl_rev_profile_id => p_appl_rev_profile_id,
1192 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1193 x_mode => 'R');
1194
1195 -- Write the Application Instance which got
1196 --Assigned to a Particular Review Code to the Log file
1197 FND_FILE.PUT_LINE(FND_FILE.LOG,
1198 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
1199 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
1200 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
1201 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
1202
1203 END IF;
1204 CLOSE c_arp_rec_found;
1205 END LOOP;
1206 dbms_sql.close_cursor(l_cursor_id);
1207
1208 -- No include or exclude Records for this Group Code
1209 ELSE
1210 CLOSE c_revgr_addr;
1211 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
1212 END IF;
1213 END LOOP;
1214
1215
1216
1217
1218
1219 -- Check if the Review Group Code is Geographical by Market Code
1220 ELSIF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'GEO_BY_MARKET_CODE' THEN
1221 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
1222 -- Write the Group Code to the log file
1223 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1224 FND_FILE.PUT_LINE(FND_FILE.LOG,
1225 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_GRP_CD_PROC'),'27',' ') ||
1226 ' '||c_appl_revprof_revgr_rec.revprof_revgr_name);
1227 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1228 FND_FILE.PUT_LINE(FND_FILE.LOG,
1229 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'),'20',' ')||
1230 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'),'20',' ')||
1231 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
1232 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
1233
1234 l_cur_statement := NULL;
1235 -- Check if there are any include or exclude records for the Group id.
1236 OPEN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1237 g_incl_ind);
1238 FETCH c_revgr_incl_excl INTO c_revgr_incl_excl_rec;
1239 IF c_revgr_incl_excl%FOUND THEN
1240 CLOSE c_revgr_incl_excl;
1241
1242
1243 fnd_dsql.init;
1244 fnd_dsql.add_text(' SELECT apl.person_id,apl.admission_appl_number, apl.nominated_course_cd, apl.sequence_number ');
1245 fnd_dsql.add_text(' FROM hz_parties hp, igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app, igs_ad_doc_stat doc, igs_ad_ou_stat ou, igs_pe_hz_parties php, igs_ad_acad_history_v aah ');
1246 fnd_dsql.add_text(' WHERE aah.person_id = apl.person_id AND aah.CURRENT_INST = ');
1247 fnd_dsql.add_bind('Y');
1248 fnd_dsql.add_text(' AND aah.institution_code = php.oss_org_unit_cd AND doc.s_adm_doc_status = ' );
1249 fnd_dsql.add_bind(l_satisfied);
1250 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ' );
1251 fnd_dsql.add_bind(l_pending);
1252 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status AND ou.adm_outcome_status = apl.adm_outcome_status AND (( ');
1253
1254 fnd_dsql.add_bind(p_entry_stat_id);
1255 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ' );
1256 fnd_dsql.add_bind(p_entry_stat_id);
1257 fnd_dsql.add_text(' ) OR ' );
1258 fnd_dsql.add_bind(p_entry_stat_id );
1259 fnd_dsql.add_text(' IS NULL ) AND (( ');
1260
1261 fnd_dsql.add_bind(p_nominated_course_cd);
1262 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ');
1263 fnd_dsql.add_bind(p_nominated_course_cd);
1264 fnd_dsql.add_text(' ) OR ' );
1265 fnd_dsql.add_bind(p_nominated_course_cd );
1266 fnd_dsql.add_text(' IS NULL ) AND (( ');
1267
1268 fnd_dsql.add_bind(p_person_id);
1269 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ' );
1270 fnd_dsql.add_bind(p_person_id);
1271 fnd_dsql.add_text(' ) OR ' );
1272 fnd_dsql.add_bind(p_person_id );
1273 fnd_dsql.add_text(' IS NULL ) AND (( ');
1274
1275 fnd_dsql.add_bind(p_unit_set_cd);
1276 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ');
1277 fnd_dsql.add_bind(p_unit_set_cd);
1278 fnd_dsql.add_text(' ) OR ' );
1279 fnd_dsql.add_bind(p_unit_set_cd);
1280 fnd_dsql.add_text(' IS NULL ) AND (( ');
1281
1282 fnd_dsql.add_bind(l_admission_cat);
1283 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ' );
1284 fnd_dsql.add_bind(l_admission_cat );
1285 fnd_dsql.add_text(' ) OR ' );
1286 fnd_dsql.add_bind(l_admission_cat );
1287 fnd_dsql.add_text(' IS NULL ) AND (( ');
1288
1289 fnd_dsql.add_bind(l_s_admission_process_type);
1290 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ');
1291 fnd_dsql.add_bind(l_s_admission_process_type );
1292 fnd_dsql.add_text(' ) OR ' );
1293 fnd_dsql.add_bind(l_s_admission_process_type );
1294 fnd_dsql.add_text(' IS NULL ) AND apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number ');
1295
1296
1297 IF p_calendar_details IS NOT NULL THEN
1298 -- Get the Academic Calander details form the Academic Calender Parameter
1299 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
1300 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
1301
1302 -- Get the Admission Calander details form the Admission Calender Parameter
1303 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
1304 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
1305
1306
1307 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
1308 fnd_dsql.add_text(' 1=2 ' );
1309 ELSE
1310 fnd_dsql.add_text(' AND app.acad_cal_type = ');
1311 fnd_dsql.add_bind(l_acad_cal_type);
1312
1313 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
1314 fnd_dsql.add_bind(l_acad_ci_sequence_number);
1315
1316 fnd_dsql.add_text(' AND app.adm_cal_type = ');
1317 fnd_dsql.add_bind(l_adm_cal_type);
1318
1319 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
1320 fnd_dsql.add_bind(l_adm_ci_sequence_number);
1321
1322 END IF;
1323 END IF;
1324
1325 OPEN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1326 g_excl_ind);
1327 FETCH c_revgr_incl_excl INTO c_revgr_incl_excl_rec;
1328 IF c_revgr_incl_excl%FOUND THEN
1329 CLOSE c_revgr_incl_excl;
1330
1331 fnd_dsql.add_text(' AND php.inst_eps_code IN ( ( SELECT inst_eps_code ');
1332 fnd_dsql.add_text(' FROM igs_pe_hz_parties WHERE ');
1333
1334 -- Open a cursor to fetch all the include Records and combine them to form a where clause
1335 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1336 g_incl_ind) LOOP
1337 IF (l_count_incl_excl > 0) THEN
1338 fnd_dsql.add_text(' OR ');
1339 END IF;
1340
1341
1342 fnd_dsql.add_text(' ((UPPER(inst_eps_code) BETWEEN NVL(UPPER(');
1343 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
1344 fnd_dsql.add_text(' ),');
1345 fnd_dsql.add_bind(l_percentage_symbol);
1346 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1347 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1348 fnd_dsql.add_text(' ),');
1349 fnd_dsql.add_bind(l_percentage_symbol);
1350 fnd_dsql.add_text(' )) OR UPPER(inst_eps_code) LIKE NVL(UPPER(');
1351 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1352 fnd_dsql.add_text(' ),');
1353 fnd_dsql.add_bind(l_percentage_symbol);
1354 fnd_dsql.add_text(' ))');
1355
1356
1357 l_count_incl_excl:=l_count_incl_excl+1;
1358
1359
1360 END LOOP;
1361
1362 fnd_dsql.add_text(' )');
1363
1364 l_count_incl_excl:=0;
1365 fnd_dsql.add_text(' MINUS ( SELECT inst_eps_code FROM igs_pe_hz_parties WHERE ');
1366
1367 -- Open a cursor to fetch all the exclusion Records and combine them to form a where clause
1368 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1369 g_excl_ind) LOOP
1370
1371 IF (l_count_incl_excl > 0) THEN
1372 fnd_dsql.add_text(' OR ');
1373 END IF;
1374
1375 fnd_dsql.add_text(' ((UPPER(inst_eps_code) BETWEEN NVL(UPPER(');
1376 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
1377 fnd_dsql.add_text(' ),');
1378 fnd_dsql.add_bind(l_percentage_symbol);
1379 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1380 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1381 fnd_dsql.add_text(' ),');
1382 fnd_dsql.add_bind(l_percentage_symbol);
1383 fnd_dsql.add_text(' )) OR UPPER(inst_eps_code) LIKE NVL(UPPER(');
1384 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1385 fnd_dsql.add_text(' ),');
1386 fnd_dsql.add_bind(l_percentage_symbol);
1387 fnd_dsql.add_text(' ))');
1388
1389 l_count_incl_excl:=l_count_incl_excl+1;
1390 END LOOP;
1391
1392 fnd_dsql.add_text(' ))');
1393 l_count_incl_excl := 0;
1394
1395
1396 ELSE
1397 CLOSE c_revgr_incl_excl;
1398 fnd_dsql.add_text(' AND php.inst_eps_code IN ( SELECT inst_eps_code ');
1399 fnd_dsql.add_text(' FROM igs_pe_hz_parties WHERE ');
1400
1401 FOR c_revgr_incl_excl_rec IN c_revgr_incl_excl(c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1402 g_incl_ind) LOOP
1403 IF (l_count_incl_excl > 0) THEN
1404 fnd_dsql.add_text(' OR ');
1405 END IF;
1406
1407 fnd_dsql.add_text(' ((UPPER(inst_eps_code) BETWEEN NVL(UPPER(');
1408 fnd_dsql.add_bind(c_revgr_incl_excl_rec.start_value);
1409 fnd_dsql.add_text(' ),');
1410 fnd_dsql.add_bind(l_percentage_symbol);
1411 fnd_dsql.add_text(' ) AND NVL(UPPER(');
1412 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1413 fnd_dsql.add_text(' ),');
1414 fnd_dsql.add_bind(l_percentage_symbol);
1415 fnd_dsql.add_text(' )) OR UPPER(inst_eps_code) LIKE NVL(UPPER(');
1416 fnd_dsql.add_bind(c_revgr_incl_excl_rec.end_value);
1417 fnd_dsql.add_text(' ),');
1418 fnd_dsql.add_bind(l_percentage_symbol);
1419 fnd_dsql.add_text(' ))');
1420
1421 l_count_incl_excl:=l_count_incl_excl+1;
1422
1423
1424 END LOOP;
1425
1426 l_count_incl_excl:=0;
1427 fnd_dsql.add_text(' )');
1428
1429 END IF; -- End of checking presence of exclude records
1430
1431 l_cur_statement := fnd_dsql.get_text(FALSE);
1432
1433 l_cursor_id := dbms_sql.open_cursor;
1434 fnd_dsql.set_cursor(l_cursor_id);
1435
1436 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
1437 fnd_dsql.do_binds;
1438
1439 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
1440 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
1441 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
1442 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
1443
1444 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
1445
1446 /*for debugging*/
1447 l_debug := fnd_dsql.get_text(TRUE);
1448
1449
1450 LOOP
1451 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
1452 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
1453 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
1454 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
1455 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
1456
1457 OPEN c_arp_rec_found(l_person_id,l_admission_appl_number,
1458 l_nominated_course_cd,l_sequence_number);
1459 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
1460 IF c_arp_rec_found%NOTFOUND THEN
1461 -- Insert Using TableHandler
1462 lv_rowid := NULL;
1463 lv_appl_arp_id := NULL;
1464 igs_ad_appl_arp_pkg.insert_row (
1465 x_rowid => lv_rowid,
1466 x_appl_arp_id => lv_appl_arp_id,
1467 x_person_id => l_person_id,
1468 x_admission_appl_number => l_admission_appl_number,
1469 x_nominated_course_cd => l_nominated_course_cd,
1470 x_sequence_number => l_sequence_number,
1471 x_appl_rev_profile_id => p_appl_rev_profile_id,
1472 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1473 x_mode => 'R');
1474
1475 -- Write the Application Instance which got
1476 --Assigned to a Particular Review Code to the Log file
1477 FND_FILE.PUT_LINE(FND_FILE.LOG,LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
1478 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
1479 LPAD(l_nominated_course_cd,'15',' ')||
1480 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
1481
1482 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
1483 -- Update Using Table Handler
1484 igs_ad_appl_arp_pkg.update_row (
1485 x_rowid => c_arp_rec_found_rec.rowid,
1486 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
1487 x_person_id => c_arp_rec_found_rec.person_id,
1488 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
1489 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
1490 x_sequence_number => c_arp_rec_found_rec.sequence_number,
1491 x_appl_rev_profile_id => p_appl_rev_profile_id,
1492 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1493 x_mode => 'R');
1494
1495 -- Write the Application Instance which got
1496 --Assigned to a Particular Review Code to the Log file
1497 FND_FILE.PUT_LINE(FND_FILE.LOG,
1498 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
1499 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
1500 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
1501 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
1502
1503 END IF;
1504 CLOSE c_arp_rec_found;
1505
1506 END LOOP;
1507 dbms_sql.close_cursor(l_cursor_id);
1508
1509 -- No include or exclude Records for this Group Code
1510 ELSE
1511 CLOSE c_revgr_incl_excl;
1512 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
1513 END IF;
1514 -- Loop through the next Review Group Code for the Profile ID
1515 END LOOP;
1516
1517
1518
1519
1520
1521 -- Check if the Review Group Code is Program of Study
1522 ELSIF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'PROG_OF_STUDY' THEN
1523 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
1524 -- Write the Group Code to the log file
1525 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1526 FND_FILE.PUT_LINE(FND_FILE.LOG,
1527 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_GRP_CD_PROC'),'27',' ')
1528 ||' '||c_appl_revprof_revgr_rec.revprof_revgr_name);
1529 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1530 FND_FILE.PUT_LINE(FND_FILE.LOG,
1531 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'),'20',' ')||
1532 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'),'20',' ')||
1533 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
1534 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
1535
1536 l_cur_statement := NULL;
1537
1538 -- Check if there are any include or exclude records for the Group id.
1539 OPEN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id);
1540 FETCH c_revgr_addr INTO c_revgr_addr_rec;
1541 IF c_revgr_addr%FOUND THEN
1542 CLOSE c_revgr_addr;
1543
1544
1545 fnd_dsql.init;
1546 fnd_dsql.add_text(' SELECT apl.person_id,apl.admission_appl_number, apl.nominated_course_cd, apl.sequence_number ');
1547 fnd_dsql.add_text(' FROM igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app, igs_ad_doc_stat doc, igs_ad_ou_stat ou ');
1548 fnd_dsql.add_text(' WHERE doc.s_adm_doc_status = ');
1549 fnd_dsql.add_bind(l_satisfied);
1550 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ');
1551 fnd_dsql.add_bind(l_pending);
1552 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status AND ou.adm_outcome_status = apl.adm_outcome_status AND (( ');
1553
1554 fnd_dsql.add_bind(p_entry_stat_id );
1555 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ' );
1556 fnd_dsql.add_bind(p_entry_stat_id);
1557 fnd_dsql.add_text(' ) OR ' );
1558 fnd_dsql.add_bind(p_entry_stat_id );
1559 fnd_dsql.add_text(' IS NULL ) AND (( ');
1560
1561 fnd_dsql.add_bind(p_nominated_course_cd);
1562 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ');
1563 fnd_dsql.add_bind(p_nominated_course_cd );
1564 fnd_dsql.add_text(' ) OR ' );
1565 fnd_dsql.add_bind(p_nominated_course_cd );
1566 fnd_dsql.add_text(' IS NULL ) AND (( ');
1567
1568 fnd_dsql.add_bind(p_person_id);
1569 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ' );
1570 fnd_dsql.add_bind(p_person_id);
1571 fnd_dsql.add_text(' ) OR ' );
1572 fnd_dsql.add_bind(p_person_id );
1573 fnd_dsql.add_text(' IS NULL ) AND (( ');
1574
1575 fnd_dsql.add_bind(p_unit_set_cd);
1576 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ' );
1577 fnd_dsql.add_bind(p_unit_set_cd );
1578 fnd_dsql.add_text(' ) OR ' );
1579 fnd_dsql.add_bind(p_unit_set_cd );
1580 fnd_dsql.add_text(' IS NULL ) AND (( ');
1581
1582 fnd_dsql.add_bind(l_admission_cat );
1583 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ' );
1584 fnd_dsql.add_bind(l_admission_cat );
1585 fnd_dsql.add_text(' ) OR ' );
1586 fnd_dsql.add_bind(l_admission_cat );
1587 fnd_dsql.add_text(' IS NULL ) AND (( ');
1588
1589 fnd_dsql.add_bind(l_s_admission_process_type );
1590 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ');
1591 fnd_dsql.add_bind(l_s_admission_process_type );
1592 fnd_dsql.add_text(' ) OR ' );
1593 fnd_dsql.add_bind(l_s_admission_process_type );
1594 fnd_dsql.add_text(' IS NULL ) AND apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number ');
1595
1596
1597 IF p_calendar_details IS NOT NULL THEN
1598 -- Get the Academic Calander details form the Academic Calender Parameter
1599 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
1600 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
1601
1602 -- Get the Admission Calander details form the Admission Calender Parameter
1603 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
1604 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
1605
1606
1607 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
1608 fnd_dsql.add_text(' 1=2 ' );
1609 ELSE
1610 fnd_dsql.add_text(' AND app.acad_cal_type = ');
1611 fnd_dsql.add_bind(l_acad_cal_type);
1612
1613 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
1614 fnd_dsql.add_bind(l_acad_ci_sequence_number);
1615
1616 fnd_dsql.add_text(' AND app.adm_cal_type = ');
1617 fnd_dsql.add_bind(l_adm_cal_type);
1618
1619 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
1620 fnd_dsql.add_bind(l_adm_ci_sequence_number);
1621
1622 END IF;
1623 END IF;
1624
1625
1626 l_count_incl_excl:= 0;
1627 fnd_dsql.add_text(' AND (');
1628 -- Open a cursor to fetch all the include Records and combine them to form a where clause
1629 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
1630 LOOP
1631 IF (l_count_incl_excl > 0) THEN
1632 fnd_dsql.add_text(' OR ');
1633 END IF;
1634
1635
1636 fnd_dsql.add_text(' ( apl.nominated_course_cd = NVL(');
1637 fnd_dsql.add_bind(c_revgr_addr_rec.start_value );
1638 fnd_dsql.add_text(' ,');
1639 fnd_dsql.add_bind(l_percentage_symbol);
1640 fnd_dsql.add_text(' ) AND apl.crv_version_number = NVL(');
1641 fnd_dsql.add_bind(c_revgr_addr_rec.version_number);
1642 fnd_dsql.add_text(' ,');
1643 fnd_dsql.add_bind(l_percentage_symbol);
1644 fnd_dsql.add_text(' ))');
1645
1646 l_count_incl_excl:=l_count_incl_excl+1;
1647
1648 END LOOP;
1649
1650 l_count_incl_excl:=0;
1651 fnd_dsql.add_text(' )');
1652
1653
1654 l_cur_statement := fnd_dsql.get_text(FALSE);
1655
1656 l_cursor_id := dbms_sql.open_cursor;
1657 fnd_dsql.set_cursor(l_cursor_id);
1658
1659 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
1660 fnd_dsql.do_binds;
1661
1662 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
1663 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
1664 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
1665 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
1666
1667 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
1668
1669 /*for debugging*/
1670 l_debug := fnd_dsql.get_text(TRUE);
1671
1672
1673 LOOP
1674 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
1675 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
1676 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
1677 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
1678 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
1679
1680 OPEN c_arp_rec_found(l_person_id,l_admission_appl_number,
1681 l_nominated_course_cd,l_sequence_number);
1682 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
1683 IF c_arp_rec_found%NOTFOUND THEN
1684 -- Insert Using TableHandler
1685 lv_rowid := NULL;
1686 lv_appl_arp_id := NULL;
1687 igs_ad_appl_arp_pkg.insert_row (
1688 x_rowid => lv_rowid,
1689 x_appl_arp_id => lv_appl_arp_id,
1690 x_person_id => l_person_id,
1691 x_admission_appl_number => l_admission_appl_number,
1692 x_nominated_course_cd => l_nominated_course_cd,
1693 x_sequence_number => l_sequence_number,
1694 x_appl_rev_profile_id => p_appl_rev_profile_id,
1695 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1696 x_mode => 'R');
1697
1698 -- Write the Application Instance which got
1699 -- Assigned to a Particular Review Code to the Log file
1700 FND_FILE.PUT_LINE(FND_FILE.LOG,
1701 LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
1702 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
1703 LPAD(l_nominated_course_cd,'15',' ')||
1704 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
1705
1706 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
1707 -- Update Using Table Handler
1708 igs_ad_appl_arp_pkg.update_row (
1709 x_rowid => c_arp_rec_found_rec.rowid,
1710 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
1711 x_person_id => c_arp_rec_found_rec.person_id,
1712 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
1713 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
1714 x_sequence_number => c_arp_rec_found_rec.sequence_number,
1715 x_appl_rev_profile_id => p_appl_rev_profile_id,
1716 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1717 x_mode => 'R');
1718
1719 -- Write the Application Instance which got
1720 -- Assigned to a Particular Review Code to the Log file
1721 FND_FILE.PUT_LINE(FND_FILE.LOG,
1722 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
1723 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
1724 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
1725 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
1726
1727 END IF;
1728 CLOSE c_arp_rec_found;
1729
1730 END LOOP;
1731 dbms_sql.close_cursor(l_cursor_id);
1732
1733 -- No include or exclude Records for this Group Code
1734 ELSE
1735 CLOSE c_revgr_addr;
1736 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
1737 END IF;
1738 -- Loop through the next Review Group Code for the Profile ID
1739 END LOOP;
1740
1741
1742
1743
1744
1745
1746 -- Check if the Review Group Code is Organization
1747 ELSIF c_appl_revprof_group_cd_rec.appl_rev_profile_gr_cd = 'ORGANIZATION' THEN
1748 FOR c_appl_revprof_revgr_rec IN c_appl_revprof_revgr(p_appl_rev_profile_id) LOOP
1749 -- Write the Group Code to the log file
1750 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1751 FND_FILE.PUT_LINE(FND_FILE.LOG,
1752 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_GRP_CD_PROC'),'27',' ') ||
1753 ' '|| c_appl_revprof_revgr_rec.revprof_revgr_name);
1754 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1755 FND_FILE.PUT_LINE(FND_FILE.LOG,
1756 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON_ID'),'20',' ')||
1757 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_NO'),'20',' ')||
1758 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD'),'20',' ')||
1759 LPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQ_NUM'),'20',' '));
1760
1761 l_cur_statement := NULL;
1762
1763 -- Check if there are any include or exclude records for the Group id.
1764 OPEN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id);
1765 FETCH c_revgr_addr INTO c_revgr_addr_rec;
1766 IF c_revgr_addr%FOUND THEN
1767 CLOSE c_revgr_addr;
1768
1769
1770 fnd_dsql.init;
1771 fnd_dsql.add_text('SELECT apl.person_id,apl.admission_appl_number, apl.nominated_course_cd, apl.sequence_number ' );
1772 fnd_dsql.add_text(' FROM igs_ps_ver_all pva, igs_ad_ps_appl_inst_all apl, igs_ad_appl_all app, igs_ad_doc_stat doc, igs_ad_ou_stat ou ');
1773 fnd_dsql.add_text(' WHERE pva.course_cd = apl.nominated_course_cd AND doc.s_adm_doc_status = ' );
1774 fnd_dsql.add_bind(l_satisfied);
1775 fnd_dsql.add_text(' AND ou.s_adm_outcome_status = ' );
1776 fnd_dsql.add_bind(l_pending);
1777 fnd_dsql.add_text(' AND doc.adm_doc_status = apl.adm_doc_status AND ou.adm_outcome_status = apl.adm_outcome_status AND (( ');
1778
1779 fnd_dsql.add_bind(p_entry_stat_id );
1780 fnd_dsql.add_text(' IS NOT NULL AND apl.entry_status = ');
1781 fnd_dsql.add_bind(p_entry_stat_id );
1782 fnd_dsql.add_text(' ) OR ' );
1783 fnd_dsql.add_bind(p_entry_stat_id );
1784 fnd_dsql.add_text(' IS NULL ) AND (( ');
1785
1786 fnd_dsql.add_bind(p_nominated_course_cd );
1787 fnd_dsql.add_text(' IS NOT NULL AND apl.nominated_course_cd = ');
1788 fnd_dsql.add_bind(p_nominated_course_cd );
1789 fnd_dsql.add_text(' ) OR ' );
1790 fnd_dsql.add_bind(p_nominated_course_cd );
1791 fnd_dsql.add_text(' IS NULL ) AND (( ');
1792
1793 fnd_dsql.add_bind(p_person_id );
1794 fnd_dsql.add_text(' IS NOT NULL AND apl.person_id = ' );
1795 fnd_dsql.add_bind(p_person_id );
1796 fnd_dsql.add_text(' ) OR ' );
1797 fnd_dsql.add_bind(p_person_id );
1798 fnd_dsql.add_text(' IS NULL ) AND (( ');
1799
1800 fnd_dsql.add_bind(p_unit_set_cd );
1801 fnd_dsql.add_text(' IS NOT NULL AND apl.unit_set_cd = ');
1802 fnd_dsql.add_bind(p_unit_set_cd);
1803 fnd_dsql.add_text(' ) OR ' );
1804 fnd_dsql.add_bind(p_unit_set_cd );
1805 fnd_dsql.add_text(' IS NULL ) AND (( ');
1806
1807 fnd_dsql.add_bind(l_admission_cat );
1808 fnd_dsql.add_text(' IS NOT NULL AND app.admission_cat = ' );
1809 fnd_dsql.add_bind(l_admission_cat );
1810 fnd_dsql.add_text(' ) OR ' );
1811 fnd_dsql.add_bind(l_admission_cat );
1812 fnd_dsql.add_text(' IS NULL ) AND (( ');
1813
1814 fnd_dsql.add_bind(l_s_admission_process_type );
1815 fnd_dsql.add_text(' IS NOT NULL AND app.s_admission_process_type = ');
1816 fnd_dsql.add_bind(l_s_admission_process_type );
1817 fnd_dsql.add_text(' ) OR ' );
1818 fnd_dsql.add_bind(l_s_admission_process_type );
1819 fnd_dsql.add_text(' IS NULL ) AND apl.person_id = app.person_id AND apl.admission_appl_number = app.admission_appl_number ');
1820
1821 IF p_calendar_details IS NOT NULL THEN
1822 -- Get the Academic Calander details form the Academic Calender Parameter
1823 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
1824 l_acad_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
1825
1826 -- Get the Admission Calander details form the Admission Calender Parameter
1827 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
1828 l_adm_ci_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
1829
1830
1831 IF l_acad_cal_type IS NULL OR l_acad_ci_sequence_number IS NULL OR l_adm_cal_type IS NULL OR l_adm_ci_sequence_number IS NULL THEN
1832 fnd_dsql.add_text(' 1=2 ' );
1833 ELSE
1834 fnd_dsql.add_text(' AND app.acad_cal_type = ');
1835 fnd_dsql.add_bind(l_acad_cal_type);
1836
1837 fnd_dsql.add_text(' AND app.acad_ci_sequence_number = ');
1838 fnd_dsql.add_bind(l_acad_ci_sequence_number);
1839
1840 fnd_dsql.add_text(' AND app.adm_cal_type = ');
1841 fnd_dsql.add_bind(l_adm_cal_type);
1842
1843 fnd_dsql.add_text(' AND app.adm_ci_sequence_number = ');
1844 fnd_dsql.add_bind(l_adm_ci_sequence_number);
1845
1846 END IF;
1847 END IF;
1848
1849
1850 fnd_dsql.add_text(' AND (');
1851 -- Open a cursor to fetch all the include Records and combine them to form a where clause
1852 FOR c_revgr_addr_rec IN c_revgr_addr(c_appl_revprof_revgr_rec.appl_revprof_revgr_id)
1853 LOOP
1854 IF (l_count_incl_excl > 0) THEN
1855 fnd_dsql.add_text(' OR ');
1856 END IF;
1857
1858 fnd_dsql.add_text(' ( pva.responsible_org_unit_cd = NVL(' );
1859 fnd_dsql.add_bind(c_revgr_addr_rec.start_value);
1860 fnd_dsql.add_text(' ,');
1861 fnd_dsql.add_bind(l_percentage_symbol);
1862 fnd_dsql.add_text(' ))');
1863
1864
1865 l_count_incl_excl:=l_count_incl_excl+1;
1866
1867 END LOOP;
1868
1869 l_count_incl_excl:=0;
1870 fnd_dsql.add_text(' )');
1871
1872
1873 l_cur_statement := fnd_dsql.get_text(FALSE);
1874
1875 l_cursor_id := dbms_sql.open_cursor;
1876 fnd_dsql.set_cursor(l_cursor_id);
1877
1878 dbms_sql.parse(l_cursor_id, l_cur_statement, dbms_sql.native);
1879 fnd_dsql.do_binds;
1880
1881 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
1882 dbms_sql.define_column(l_cursor_id, 2, l_admission_appl_number);
1883 dbms_sql.define_column(l_cursor_id, 3, l_nominated_course_cd, 6);
1884 dbms_sql.define_column(l_cursor_id, 4, l_sequence_number);
1885
1886 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
1887
1888 /*for debugging*/
1889 l_debug := fnd_dsql.get_text(TRUE);
1890
1891
1892 LOOP
1893 EXIT WHEN dbms_sql.FETCH_ROWS(l_cursor_id) = 0;
1894 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
1895 dbms_sql.column_value(l_cursor_id, 2, l_admission_appl_number);
1896 dbms_sql.column_value(l_cursor_id, 3, l_nominated_course_cd);
1897 dbms_sql.column_value(l_cursor_id, 4, l_sequence_number);
1898
1899 OPEN c_arp_rec_found(l_person_id,l_admission_appl_number,
1900 l_nominated_course_cd,l_sequence_number);
1901 FETCH c_arp_rec_found INTO c_arp_rec_found_rec;
1902 IF c_arp_rec_found%NOTFOUND THEN
1903 -- Insert Using TableHandler
1904 lv_rowid := NULL;
1905 lv_appl_arp_id := NULL;
1906 igs_ad_appl_arp_pkg.insert_row (
1907 x_rowid => lv_rowid,
1908 x_appl_arp_id => lv_appl_arp_id,
1909 x_person_id => l_person_id,
1910 x_admission_appl_number => l_admission_appl_number,
1911 x_nominated_course_cd => l_nominated_course_cd,
1912 x_sequence_number => l_sequence_number,
1913 x_appl_rev_profile_id => p_appl_rev_profile_id,
1914 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1915 x_mode => 'R');
1916
1917 -- Write the Application Instance which got
1918 --Assigned to a Particular Review Code to the Log file
1919 FND_FILE.PUT_LINE(FND_FILE.LOG,
1920 LPAD(IGS_GE_NUMBER.TO_CANN(l_person_id),'20',' ')||
1921 LPAD(IGS_GE_NUMBER.TO_CANN(l_admission_appl_number),'20',' ')||
1922 LPAD(l_nominated_course_cd,'15',' ')||
1923 LPAD(IGS_GE_NUMBER.TO_CANN(l_sequence_number),'20',' '));
1924
1925 ELSIF c_arp_rec_found_rec.appl_revprof_revgr_id IS NULL THEN
1926 -- Update Using Table Handler
1927 igs_ad_appl_arp_pkg.update_row (
1928 x_rowid => c_arp_rec_found_rec.rowid,
1929 x_appl_arp_id => c_arp_rec_found_rec.appl_arp_id,
1930 x_person_id => c_arp_rec_found_rec.person_id,
1931 x_admission_appl_number => c_arp_rec_found_rec.admission_appl_number,
1932 x_nominated_course_cd => c_arp_rec_found_rec.nominated_course_cd,
1933 x_sequence_number => c_arp_rec_found_rec.sequence_number,
1934 x_appl_rev_profile_id => p_appl_rev_profile_id,
1935 x_appl_revprof_revgr_id => c_appl_revprof_revgr_rec.appl_revprof_revgr_id,
1936 x_mode => 'R');
1937
1938 -- Write the Application Instance which got Assigned to a
1939 --Particular Review Code to the Log file
1940 FND_FILE.PUT_LINE(FND_FILE.LOG,
1941 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.person_id),'20',' ')||
1942 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.admission_appl_number),'20',' ')||
1943 LPAD(c_arp_rec_found_rec.nominated_course_cd,'20',' ')||
1944 LPAD(IGS_GE_NUMBER.TO_CANN(c_arp_rec_found_rec.sequence_number),'20',' '));
1945
1946 END IF;
1947 CLOSE c_arp_rec_found;
1948
1949 END LOOP;
1950 dbms_sql.close_cursor(l_cursor_id);
1951
1952 -- No include or exclude Records for this Group Code
1953 ELSE
1954 CLOSE c_revgr_addr;
1955 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_NO_REC_FOUND'));
1956 END IF;
1957 -- Loop through the next Review Group Code for the Profile ID
1958 END LOOP;
1959 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1960 END IF;
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 retcode := 2;
1964 IGS_GE_MSG_STACK.Conc_Exception_Hndl;
1965 END assign_review_group;
1966 END igs_ad_assign_review_grp;