1 PACKAGE BODY igs_ad_adm_req AS
2 /* $Header: IGSADA2B.pls 120.10 2006/07/31 10:16:11 apadegal ship $ */
3
4 --
5 -- Forward declarations
6 --
7 TYPE TRK_TYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8
9 FUNCTION crt_adm_trk_itm(
10 p_person_id IN NUMBER,
11 p_admission_appl_number IN NUMBER,
12 p_program_code IN VARCHAR2,
13 p_sequence_number IN NUMBER,
14 p_requirements_type IN VARCHAR2,
15 p_originator_person IN NUMBER,
16 p_tracking_status IN VARCHAR2,
17 p_message_name OUT NOCOPY VARCHAR2
18 ) RETURN BOOLEAN;
19
20 FUNCTION admp_get_trk_types(
21 p_admission_cat IN VARCHAR2,
22 p_admission_process_type IN VARCHAR2,
23 p_adm_rule_type IN VARCHAR2,
24 p_person_id IN NUMBER,
25 p_admission_appl_number IN NUMBER,
26 p_adm_sequence_number IN NUMBER,
27 p_nominated_prg_cd IN VARCHAR2,
28 p_nominated_prg_version IN NUMBER
29 ) RETURN TRK_TYPE;
30
31 PROCEDURE log_detail(p_log_text IN VARCHAR2) AS
32 BEGIN
33 FND_FILE.PUT_LINE( FND_FILE.LOG, p_log_text);
34 -- DBMS_OUTPUT.PUT_LINE(p_log_text);
35 END log_detail;
36
37 PROCEDURE ini_adm_trk_itm(
38 errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY NUMBER ,
40 p_person_id IN NUMBER,
41 p_calendar_details IN VARCHAR2,
42 p_admission_process_category IN VARCHAR2,
43 p_admission_appl_number IN NUMBER,
44 p_program_code IN VARCHAR2,
45 p_sequence_number IN NUMBER,
46 p_person_id_group IN VARCHAR2,
47 p_requirements_type IN VARCHAR2,
48 p_originator_person IN NUMBER,
49 p_org_id IN NUMBER
50 ) AS
51
52 l_tracking_id igs_tr_item.tracking_id%TYPE;
53 l_aplins_admreq_id igs_ad_aplins_admreq.aplins_admreq_id%TYPE;
54 l_message_name VARCHAR2(30);
55 l_found BOOLEAN;
56 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
57 l_acad_sequence_number igs_ca_inst.sequence_number%TYPE;
58 l_adm_cal_type igs_ca_inst.cal_type%TYPE;
59 l_adm_sequence_number igs_ca_inst.sequence_number%TYPE;
60 l_admission_cat igs_ad_cat.admission_cat%TYPE;
61 l_s_adm_process_typ igs_ad_prd_ad_prc_ca.s_admission_process_type%TYPE;
62 --
63 -- Get eligible application instances for ADM_PROCESSING
64 --
65 CURSOR assn_adm_pro_cur IS
66 SELECT DISTINCT
67 apai.person_id,
68 apai.admission_appl_number,
69 apai.nominated_course_cd,
70 apai.sequence_number
71 FROM
72 igs_ad_ps_appl_inst apai,
73 igs_ad_ou_stat aos,
74 igs_ad_doc_stat ads,
75 igs_ad_appl aa,
76 igs_ad_appl_stat aps
77 WHERE
78 -- known person
79 apai.person_id = nvl(p_person_id,apai.person_id) AND
80 -- known person id group
81 (p_person_id_group IS NOT NULL AND
82 apai.person_id IN (SELECT person_id
83 FROM igs_pe_prsid_grp_mem pgm
84 WHERE pgm.group_id = NVL(p_person_id_group,pgm.group_id) AND
85 NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
86 NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
87 or
88 (p_person_id_group is null)) AND
89 -- known academic/admission calendar period
90 aa.acad_cal_type = nvl(l_acad_cal_type,aa.acad_cal_type) AND
91 aa.acad_ci_sequence_number = nvl(l_acad_sequence_number,aa.acad_ci_sequence_number) AND
92 aa.adm_cal_type = nvl(l_adm_cal_type,aa.adm_cal_type) AND
93 aa.adm_ci_sequence_number = nvl(l_adm_sequence_number,aa.adm_ci_sequence_number) AND
94 -- known admission process category
95 aa.admission_cat = nvl(l_admission_cat,aa.admission_cat) AND
96 aa.s_admission_process_type = nvl(l_s_adm_process_typ,aa.s_admission_process_type) AND
97 -- known application instance
98 apai.nominated_course_cd = nvl(p_program_code,apai.nominated_course_cd) AND
99 apai.admission_appl_number = nvl(p_admission_appl_number,apai.admission_appl_number) AND
100 apai.sequence_number = nvl(p_sequence_number,apai.sequence_number) AND
101 -- regular joins
102 aos.s_adm_outcome_status = 'PENDING' AND
103 apai.adm_outcome_status = aos.adm_outcome_status AND
104 ads.s_adm_doc_status = 'PENDING' AND
105 apai.adm_doc_status = ads.adm_doc_status AND
106 aa.person_id=apai.person_id AND
107 aa.admission_appl_number = apai.admission_appl_number AND
108 NVL(apai.appl_inst_status,aps.adm_appl_status) = aps.adm_appl_status AND
109 aps.s_adm_appl_status <> 'WITHDRAWN' AND -- igsm arvsrini instance withdrawn
110 aps.closed_ind = 'N';
111 --
112 -- Get eligible application instances for POST_ADMISSION
113 --
114 CURSOR assn_post_adm_cur IS
115 SELECT DISTINCT
116 apai.person_id,
117 apai.admission_appl_number,
118 apai.nominated_course_cd,
119 apai.sequence_number
120 FROM
121 igs_ad_ps_appl_inst apai,
122 igs_ad_ou_stat aos,
123 igs_ad_doc_stat ads,
124 igs_ad_appl aa,
125 igs_ad_appl_stat aps
126 WHERE
127 -- known person
128 apai.person_id = nvl(p_person_id,apai.person_id) AND
129 -- known person id group
130 (p_person_id_group IS NOT NULL AND
131 apai.person_id IN (SELECT person_id
132 FROM igs_pe_prsid_grp_mem pgm
133 WHERE pgm.group_id = NVL(p_person_id_group,pgm.group_id) AND
134 NVL(TRUNC(pgm.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
135 NVL(TRUNC(pgm.end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
136 or
137 (p_person_id_group is null)) AND
138 -- known academic/admission calendar period
139 aa.acad_cal_type = nvl(l_acad_cal_type,aa.acad_cal_type) AND
140 aa.acad_ci_sequence_number = nvl(l_acad_sequence_number,aa.acad_ci_sequence_number) AND
141 aa.adm_cal_type = nvl(l_adm_cal_type,aa.adm_cal_type) AND
142 aa.adm_ci_sequence_number = nvl(l_adm_sequence_number,aa.adm_ci_sequence_number) AND
143 -- known admission process category
144 aa.admission_cat = nvl(l_admission_cat,aa.admission_cat) AND
145 aa.s_admission_process_type = nvl(l_s_adm_process_typ,aa.s_admission_process_type) AND
146 -- known application instance
147 apai.nominated_course_cd = nvl(p_program_code,apai.nominated_course_cd) AND
148 apai.admission_appl_number = nvl(p_admission_appl_number,apai.admission_appl_number) AND
149 apai.sequence_number = nvl(p_sequence_number,apai.sequence_number) AND
150 -- regular joins
151 aos.s_adm_outcome_status IN ( 'OFFER', 'PENDING', 'COND-OFFER') AND
152 apai.adm_outcome_status = aos.adm_outcome_status AND
153 ads.s_adm_doc_status IN ( 'INCOMPLETE', 'NOT-APPLIC', 'PENDING', 'SATISFIED') AND
154 apai.adm_doc_status = ads.adm_doc_status AND
155 aa.person_id=apai.person_id AND
156 aa.admission_appl_number = apai.admission_appl_number AND
157 NVL(apai.appl_inst_status,aps.adm_appl_status) = aps.adm_appl_status AND
158 aps.s_adm_appl_status <> 'WITHDRAWN' AND -- igsm arvsrini instance withdrawn
159 aps.closed_ind = 'N'
160 AND igs_ad_gen_002.check_adm_appl_inst_stat(apai.person_id,apai.admission_appl_number,apai.nominated_course_cd,
161 apai.sequence_number,'Y')='Y';
162
163 CURSOR get_dflt_active_track_stat_cur IS
164 SELECT tracking_status
165 FROM igs_tr_status
166 WHERE s_tracking_status = 'ACTIVE'
167 AND NVL(default_ind,'N') = 'Y'
168 AND closed_ind = 'N';
169
170 get_dflt_active_track_stat_rec get_dflt_active_track_stat_cur%ROWTYPE;
171
172 CURSOR get_meaning_req_type_cur IS
173 SELECT meaning
174 FROM igs_lookup_values
175 WHERE lookup_type = 'TRACKING_TYPE'
176 AND lookup_code = p_requirements_type;
177
178 get_meaning_req_type_rec get_meaning_req_type_cur%ROWTYPE;
179
180 CURSOR c_get_person_number (cp_person_id hz_parties.party_id%TYPE) IS
181 SELECT party_number person_number
182 FROM hz_parties
183 WHERE party_id = cp_person_id;
184
185 l_get_person_number hz_parties.party_number%TYPE;
186
187
188 BEGIN
189 -- Initialize message stack
190 igs_ge_msg_stack.initialize;
191 --
192 -- To populate org_id
193 igs_ge_gen_003.set_org_id(p_org_id);
194 --
195 retcode := 0;
196 errbuf := NULL;
197
198 OPEN get_dflt_active_track_stat_cur;
199 FETCH get_dflt_active_track_stat_cur INTO get_dflt_active_track_stat_rec;
200 CLOSE get_dflt_active_track_stat_cur;
201
202 OPEN get_meaning_req_type_cur;
203 FETCH get_meaning_req_type_cur INTO get_meaning_req_type_rec;
204 CLOSE get_meaning_req_type_cur;
205
206 IF get_dflt_active_track_stat_rec.tracking_status IS NULL THEN
207 log_detail('This process requires a user defined tracking status with default indicator set');
208 log_detail('and not marked as closed to be mapped to the system tracking status of ACTIVE');
209 log_detail('Please re run the process with the above setup');
210
211 ELSE
212
213 -- Log the Initial parameters into the LOG file.
214 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_TR_PRMS');
215 log_detail(FND_MESSAGE.GET());
216
217 OPEN c_get_person_number(p_person_id);
218 FETCH c_get_person_number INTO l_get_person_number;
219 CLOSE c_get_person_number;
220
221 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_PNUM');
222 FND_MESSAGE.SET_TOKEN('PNUM', l_get_person_number);
223 log_detail(FND_MESSAGE.GET());
224
225 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_PID_GRP');
226 FND_MESSAGE.SET_TOKEN('PGPID', p_person_id_group);
227 log_detail(FND_MESSAGE.GET());
228
229 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_ADM_APLNO');
230 FND_MESSAGE.SET_TOKEN('APLNO', p_admission_appl_number);
231 log_detail(FND_MESSAGE.GET());
232
233 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CRCD');
234 FND_MESSAGE.SET_TOKEN('CRCD', p_program_code);
235 log_detail(FND_MESSAGE.GET());
236
237 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_APP_SEQNO');
238 FND_MESSAGE.SET_TOKEN('SEQNO', p_sequence_number);
239 log_detail(FND_MESSAGE.GET());
240
241 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_CL_DTLS');
242 FND_MESSAGE.SET_TOKEN('CLDTLS', p_calendar_details);
243 log_detail(FND_MESSAGE.GET());
244
245 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_APC');
246 FND_MESSAGE.SET_TOKEN('APC', p_admission_process_category);
247 log_detail(FND_MESSAGE.GET());
248
249 OPEN c_get_person_number(p_originator_person);
250 FETCH c_get_person_number INTO l_get_person_number;
251 CLOSE c_get_person_number;
252
253 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_OPNUM');
254 FND_MESSAGE.SET_TOKEN('OPNUM', l_get_person_number);
255 log_detail(FND_MESSAGE.GET());
256
257 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APP_LG_REQ_TYPE');
258 FND_MESSAGE.SET_TOKEN('RTYP', get_meaning_req_type_rec.meaning);
259 log_detail(FND_MESSAGE.GET());
260
261 IF p_person_id IS NULL AND
262 p_person_id_group IS NULL AND
263 p_calendar_details IS NULL AND
264 p_admission_process_category IS NULL THEN
265 --Message One of the following Parameters Person Id, Person Id Group, Calendar Details
266 --or Admission Process Category is mandatory.
267 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_MANDATORY_PRM');
268 log_detail(FND_MESSAGE.GET());
269 ELSE
270 SAVEPOINT ini_adm_trk_prcs;
271 --
272 -- Split the calendar details and the admission process category into seperate variables
273 --
274 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
275 l_acad_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
276 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
277 l_adm_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
278
279 -- Do not change SUBSTR position for APC param in code and SEED
280 -- since the same value set is used in value set IGS_SRS_AD_PERSON_ID_COMPLETE
281 l_admission_cat := RTRIM ( SUBSTR ( p_admission_process_category, 1, 10));
282 l_s_adm_process_typ := RTRIM ( SUBSTR ( p_admission_process_category, 11, 30));
283
284 -- Flag to indicate whether any admission application instance records could be fetched
285 -- based on the parameters passed to the procedure
286 l_found := FALSE;
287
288 IF p_requirements_type = 'ADM_PROCESSING' THEN
289 FOR assn_adm_pro_rec IN assn_adm_pro_cur LOOP
290 --
291 -- Set the found flag to TRUE
292 --
293 l_found := TRUE;
294 --
295 -- For Each Record Selected Do The Following
296 --
297
298 OPEN c_get_person_number(IGS_GE_NUMBER.TO_CANN (assn_adm_pro_rec.person_id));
299 FETCH c_get_person_number INTO l_get_person_number;
300 CLOSE c_get_person_number;
301
302 log_detail( FND_GLOBAL.NEWLINE );
303 log_detail( ' Application Instance Information ' );
304 log_detail( ' Person Number :: ' || RPAD(l_get_person_number,30) ||
305 ' Admission Application Number :: ' || IGS_GE_NUMBER.TO_CANN (assn_adm_pro_rec.admission_appl_number ) );
306 log_detail( ' Program Code :: ' || RPAD(assn_adm_pro_rec.nominated_course_cd,30) ||
307 ' Sequence Number :: ' || IGS_GE_NUMBER.TO_CANN ( assn_adm_pro_rec.sequence_number ) );
308 IF NOT crt_adm_trk_itm(
309 p_person_id => assn_adm_pro_rec.person_id,
310 p_admission_appl_number => assn_adm_pro_rec.admission_appl_number,
311 p_program_code => assn_adm_pro_rec.nominated_course_cd,
312 p_sequence_number => assn_adm_pro_rec.sequence_number,
313 p_requirements_type => p_requirements_type,
314 p_originator_person => p_originator_person,
315 p_tracking_status => get_dflt_active_track_stat_rec.tracking_status,
316 p_message_name => l_message_name
317 ) THEN
318 IF l_message_name IS NOT NULL THEN
319 log_detail(FND_MESSAGE.GET_STRING('IGS', l_message_name));
320 END IF;
321 END IF;
322 END LOOP;
323 ELSIF p_requirements_type = 'POST_ADMISSION' THEN
324 FOR assn_post_adm_rec IN assn_post_adm_cur LOOP
325 --
326 -- Set the found flag to TRUE
327 --
328 l_found := TRUE;
329 --
330 -- For Each Record Selected Do The Following
331 --
332
333 OPEN c_get_person_number(IGS_GE_NUMBER.TO_CANN (assn_post_adm_rec.person_id));
334 FETCH c_get_person_number INTO l_get_person_number;
335 CLOSE c_get_person_number;
336
337
338 log_detail( FND_GLOBAL.NEWLINE );
339 log_detail( ' Application Instance Information ' );
343 ' Sequence Number :: ' || IGS_GE_NUMBER.TO_CANN ( assn_post_adm_rec.sequence_number ) );
340 log_detail( ' Person Number :: ' || RPAD(l_get_person_number,30) ||
341 ' Admission Application Number :: ' || IGS_GE_NUMBER.TO_CANN (assn_post_adm_rec.admission_appl_number ) );
342 log_detail( ' Program Code :: ' || RPAD(assn_post_adm_rec.nominated_course_cd,30) ||
344 IF NOT crt_adm_trk_itm(
345 p_person_id => assn_post_adm_rec.person_id,
346 p_admission_appl_number => assn_post_adm_rec.admission_appl_number,
347 p_program_code => assn_post_adm_rec.nominated_course_cd,
348 p_sequence_number => assn_post_adm_rec.sequence_number,
349 p_requirements_type => p_requirements_type,
350 p_originator_person => p_originator_person,
351 p_tracking_status => get_dflt_active_track_stat_rec.tracking_status,
352 p_message_name => l_message_name
353 ) THEN
354 IF l_message_name IS NOT NULL THEN
355 log_detail(FND_MESSAGE.GET_STRING('IGS', l_message_name));
356 END IF;
357 END IF;
358 END LOOP;
359 END IF;
360 --
361 -- If no rows selected then abort the process with the message
362 -- Requirements cannot be assigned for selected applications
363 --
364 IF l_found THEN
365 igs_ad_wf_001.ASSIGN_REQUIRMENT_DONE_EVENT
366 (
367 p_person_id => p_person_id,
368 p_admission_appl_number => p_admission_appl_number,
369 p_nominated_course_cd => p_program_code,
370 p_sequence_number => p_sequence_number
371 );
372 END IF;
373
374 IF NOT l_found THEN
375 IF p_requirements_type = 'ADM_PROCESSING' THEN
376 log_detail( FND_GLOBAL.NEWLINE );
377 log_detail('Requirements cannot be assigned as applications do not exist for parameters');
378 log_detail('entered or applications do not have application processing status of Received');
379 log_detail('or application completion status of Pending');
380 log_detail('or application outcome status of Pending');
381 ELSIF p_requirements_type = 'POST_ADMISSION' THEN
382 log_detail( FND_GLOBAL.NEWLINE );
383 log_detail('Requirements cannot be assigned as applications do not exist for parameters');
384 log_detail('entered or applications do not have application processing status of');
385 log_detail('Received/Completed or application completion status of');
386 log_detail('Not Applicable/Pending/Incomplete/Satisfied or application outcome status of');
387 log_detail('Pending/Conditional Offer/Offer');
388 END IF;
389 END IF;
390 END IF;
391 END IF;
392 EXCEPTION
393 WHEN OTHERS THEN
394 log_detail( FND_GLOBAL.NEWLINE );
395 FND_MESSAGE.SET_NAME ( 'IGS', 'IGS_GE_UNHANDLED_EXP');
396 FND_MESSAGE.SET_TOKEN ( 'NAME', ' igs_ad_adm_req.ini_adm_trk_itm');
397 errbuf := FND_MESSAGE.GET_STRING ( 'IGS', FND_MESSAGE.GET);
398 -- Rollback the transaction
399 ROLLBACK TO ini_adm_trk_prcs;
400 retcode := 2;
401 -- Handle the standard igs-message stack
402 igs_ge_msg_stack.conc_exception_hndl;
403 END ini_adm_trk_itm;
404 --
405 -- End of Procedure ins_adm_trk_itm
406 --
407 --
408 -- Start of Function crt_adm_trk_itm
409 --
410 FUNCTION crt_adm_trk_itm(
411 p_person_id IN NUMBER,
412 p_admission_appl_number IN NUMBER,
413 p_program_code IN VARCHAR2,
414 p_sequence_number IN NUMBER,
415 p_requirements_type IN VARCHAR2,
416 p_originator_person IN NUMBER,
417 p_tracking_status IN VARCHAR2,
418 p_message_name OUT NOCOPY VARCHAR2
419 ) RETURN BOOLEAN AS
420
421 l_message_name VARCHAR2(30);
422 l_target_days igs_tr_type.target_days%TYPE;
423 l_originator_person_id hz_parties.party_id%type;
424
425 CURSOR trk_comp_dt_cur IS
426 SELECT
427 NVL(TRUNC(IGS_CA_GEN_001.calp_set_alias_value(
428 cdi.absolute_val,
429 IGS_CA_GEN_002.cals_clc_dt_from_dai(
430 cdi.ci_sequence_number, cdi.CAL_TYPE, cdi.DT_ALIAS, cdi.sequence_number)
431 )), TRUNC(sysdate-1)) alias_val
432 FROM
433 igs_ad_ps_appl_inst apai,
434 igs_ad_cal_conf acc,
435 igs_ca_da_inst cdi
436 WHERE
437 -- Application Instance
438 apai.person_id = p_person_id AND
439 apai.admission_appl_number = p_admission_appl_number AND
440 apai.nominated_course_cd = p_program_code AND
441 apai.sequence_number = p_sequence_number AND
442 -- Calendar Instance
443 cdi.ci_sequence_number = apai.adm_ci_sequence_number AND
444 cdi.cal_type = apai.adm_cal_type AND
445 -- Date Alias
446 ((cdi.dt_alias = acc.adm_prc_trk_dt_alias AND
450
447 p_requirements_type = 'ADM_PROCESSING') OR
448 (cdi.dt_alias = acc.post_adm_trk_dt_alias AND
449 p_requirements_type = 'POST_ADMISSION'));
451 trk_comp_dt trk_comp_dt_cur%ROWTYPE;
452
453 CURSOR check_existence_cur IS
454 SELECT
455 ar.rowid, ar.tracking_id, itt.tracking_type
456 FROM
457 igs_ad_aplins_admreq ar,
458 igs_tr_item itt,
459 igs_tr_type tt
460 WHERE
461 ar.person_id = p_person_id AND
462 ar.admission_appl_number = p_admission_appl_number AND
463 ar.course_cd = p_program_code AND
464 ar.sequence_number = p_sequence_number AND
465 itt.tracking_id = ar.tracking_id AND
466 itt.tracking_type = tt.tracking_type AND
467 tt.s_tracking_type = p_requirements_type AND
468 NOT EXISTS
469 (SELECT 'x'
470 FROM igs_tr_step its
471 WHERE its.tracking_id = ar.tracking_id AND
472 (its.step_completion_ind = 'Y' OR
473 its.by_pass_ind = 'Y') -- bug 2776548
474 )
475 FOR UPDATE OF itt.tracking_id NOWAIT;
476
477 CURSOR process_rules_cur IS
478 SELECT
479 aa.admission_cat,
480 aa.s_admission_process_type,
481 apai.crv_version_number
482 FROM
483 igs_ad_ps_appl_inst apai,
484 igs_ad_appl aa
485 WHERE
486 apai.person_id = aa.person_id AND
487 apai.admission_appl_number = aa.admission_appl_number AND
488 apai.person_id = p_person_id AND
489 apai.admission_appl_number = p_admission_appl_number AND
490 apai.nominated_course_cd = p_program_code AND
491 apai.sequence_number = p_sequence_number;
492
493 process_rules_rec process_rules_cur%ROWTYPE;
494
495 CURSOR process_apc_cur IS
496 SELECT ty.tracking_type
497 FROM igs_tr_type ty
498 WHERE ty.s_tracking_type = p_requirements_type
499 AND ty.closed_ind = 'N'
500 AND EXISTS ( SELECT 'X'
501 FROM igs_ad_appl aa,
502 igs_ad_ps_appl_inst apai,
503 igs_ad_prcs_cat_step apcs
504 WHERE apai.person_id = p_person_id
505 AND apai.admission_appl_number = p_admission_appl_number
506 AND apai.sequence_number = p_sequence_number
507 AND apai.nominated_course_cd = p_program_code
508 AND aa.person_id = apai.person_id
509 AND aa.admission_appl_number = apai.admission_appl_number
510 AND aa.admission_cat = apcs.admission_cat
511 AND aa.s_admission_process_type = apcs.s_admission_process_type
512 AND apcs.s_admission_step_type = ty.tracking_type
513 AND apcs.step_group_type = 'TRACK' );
514
515
516 process_apc_rec process_apc_cur%ROWTYPE;
517
518 CURSOR check_duplicate_cur (cp_tracking_type igs_tr_type.tracking_type%TYPE) IS
519 SELECT ar.tracking_id
520 FROM igs_ad_aplins_admreq ar
521 WHERE ar.person_id = p_person_id
522 AND ar.admission_appl_number = p_admission_appl_number
523 AND ar.course_cd = p_program_code
524 AND ar.sequence_number = p_sequence_number
525 AND EXISTS ( SELECT 'X'
526 FROM igs_tr_item itt,
527 igs_tr_type tt
528 WHERE itt.tracking_id = ar.tracking_id
529 AND itt.tracking_type = tt.tracking_type
530 AND tt.s_tracking_type = p_requirements_type
531 AND tt.tracking_type = cp_tracking_type);
532
533
534 check_duplicate_cur_rec check_duplicate_cur%ROWTYPE;
535
536 l_trk_types TRK_TYPE;
537 l_trk_types_final TRK_TYPE;
538 l_trk_types_rpt BOOLEAN;
539 l_trk_ind BOOLEAN;
540 --
541 -- Start of Local Function crt_adm_trkitm
542 --
543 PROCEDURE crt_adm_trkitm(
544 p_person_id IN NUMBER,
545 p_admission_appl_number IN NUMBER,
546 p_program_code IN VARCHAR2,
547 p_sequence_number IN NUMBER,
548 p_tracking_type IN VARCHAR2,
549 p_target_days IN NUMBER,
550 p_completion_due_dt IN DATE
551 ) AS
552
553 l_message_name VARCHAR2(30);
554 l_tracking_id igs_ad_aplins_admreq.tracking_id%TYPE;
555 l_aplins_admreq_id igs_ad_aplins_admreq.aplins_admreq_id%TYPE;
556 l_rowid VARCHAR2(25);
557 l_override_offset_clc_ind VARCHAR2(2);
558 BEGIN
559 /*
563 l_override_offset_clc_ind :='N';
560 Call the tracking item api to create the tracking item for application instance in the loop
561 */
562 IF p_completion_due_dt IS NULL THEN
564 ELSE
565 l_override_offset_clc_ind :='Y';
566 END IF;
567 igs_tr_gen_002.trkp_ins_trk_item(
568 p_tracking_status => p_tracking_status,
569 p_tracking_type => p_tracking_type,
570 p_source_person_id => p_person_id,
571 p_start_dt => SYSDATE,
572 p_target_days => p_target_days,
573 p_sequence_ind => NULL,
574 p_business_days_ind => NULL,
575 p_originator_person_id => l_originator_person_id,
576 p_s_created_ind => NULL,
577 p_completion_due_dt => p_completion_due_dt,
578 p_override_offset_clc_ind => l_override_offset_clc_ind,
579 p_publish_ind => NULL,
580 p_tracking_id => l_tracking_id,
581 p_message_name => l_message_name
582 );
583 IF l_tracking_id IS NOT NULL THEN
584 BEGIN
585 igs_ad_aplins_admreq_pkg.insert_row(
586 x_rowid => l_rowid,
587 x_aplins_admreq_id => l_aplins_admreq_id,
588 x_person_id => p_person_id,
589 x_admission_appl_number => p_admission_appl_number,
590 x_course_cd => p_program_code,
591 x_sequence_number => p_sequence_number,
592 x_tracking_id => l_tracking_id,
593 x_mode => 'R'
594 );
595 IF l_aplins_admreq_id IS NOT NULL THEN
596 --
597 -- Process completed successfully tracking items
598 -- created for the Application Instances entered
599 --
600 log_detail(FND_MESSAGE.GET_STRING ( 'IGS', 'IGS_AD_PROC_ASSIGN_REQ_COMP'));
601 log_detail('Tracking ID :: ' || IGS_GE_NUMBER.TO_CANN ( l_tracking_id) ||' for tracking type :: ' || p_tracking_type);
602
603 ELSE
604 log_detail('Failed to create the tracking item link to admission application instance');
605 log_detail('for the tracking item :: ' || IGS_GE_NUMBER.TO_CANN (l_tracking_id) ||', please create it manually');
606 END IF;
607 EXCEPTION
608 WHEN OTHERS THEN
609 log_detail('Failed to create the tracking item link to admission application instance');
610 log_detail('for the tracking item :: ' || IGS_GE_NUMBER.TO_CANN (l_tracking_id) ||', please create it manually');
611 END;
612 ELSE
613 log_detail('Failed to create the tracking item for the admission application instance');
614 log_detail('For the tracking type :: ' || p_tracking_type);
615 p_message_name := l_message_name;
616 IF l_message_name IS NOT NULL THEN
617 log_detail(FND_MESSAGE.GET_STRING('IGS', l_message_name));
618 END IF;
619 END IF;
620 EXCEPTION
621 WHEN OTHERS THEN
622 log_detail('Failed to create the tracking item for the admission application instance');
623 log_detail('For the tracking type :: ' || p_tracking_type);
624 IF l_message_name IS NOT NULL THEN
625 log_detail(FND_MESSAGE.GET_STRING('IGS', l_message_name));
626 END IF;
627 END crt_adm_trkitm;
628 --
629 -- End Of Local Function crt_adm_trkitm
630 --
631 BEGIN
632
633 l_trk_ind := FALSE;
634
635 /*
636 Issue a savepoint for the tracking items-applications related processing
637 */
638 SAVEPOINT sp_trkitm_aplproc;
639
640 -- Set the originator person ID
641 IF p_originator_person IS NULL THEN
642 l_originator_person_id := p_person_id;
643 ELSE
644 l_originator_person_id := p_originator_person;
645 END IF;
646 --
647 -- Tracking Completion Date validation
648 -- 5.9.7.1 ( This refers to the section in the DLD)
649 --
650 IF p_requirements_type = 'POST_ADMISSION' THEN
651 OPEN trk_comp_dt_cur;
652 FETCH trk_comp_dt_cur INTO trk_comp_dt;
653 IF NVL(trk_comp_dt.alias_val,SYSDATE) < SYSDATE THEN
654 p_message_name := 'IGS_AD_TRAC_COMP_DATE_PASS';
655 CLOSE trk_comp_dt_cur;
656 RETURN FALSE;
657 END IF;
658 CLOSE trk_comp_dt_cur;
659 --
660 -- 5.9.7.2
661 --
662 ELSIF p_requirements_type = 'ADM_PROCESSING' THEN
663 OPEN trk_comp_dt_cur;
664 FETCH trk_comp_dt_cur INTO trk_comp_dt;
665 IF NVL(trk_comp_dt.alias_val,SYSDATE) < SYSDATE THEN
669 END IF;
666 p_message_name := 'IGS_AD_TRAC_COMP_DATE_PASS';
667 CLOSE trk_comp_dt_cur;
668 RETURN FALSE;
670 CLOSE trk_comp_dt_cur;
671 END IF;
672
673 IF trk_comp_dt.alias_val IS NULL THEN
674 l_target_days := NULL;
675 ELSE
676 l_target_days := trk_comp_dt.alias_val - TRUNC(SYSDATE);
677
678 IF l_target_days > 9999 THEN
679 log_detail('Target days for a tracking item cannot be more than 9999 days');
680 RETURN FALSE;
681 END IF;
682 END IF;
683
684
685 /*
686 5.9.7.3
687 Check if records already exist in the linking table for the application instance
688 and the tracking items
689 */
690 FOR check_existence_rec IN check_existence_cur LOOP
691 igs_ad_aplins_admreq_pkg.delete_row ( check_existence_rec.rowid);
692 IF igs_tr_gen_002.trkp_del_tri ( check_existence_rec.tracking_id, l_message_name) THEN
693 log_detail(FND_MESSAGE.GET_STRING( 'IGS', 'IGS_AD_TRAC_NOT_REQ_DELETED'));
694 log_detail('Tracking ID :: ' || IGS_GE_NUMBER.TO_CANN( check_existence_rec.tracking_id) ||
695 ' of tracking type :: ' || check_existence_rec.tracking_type);
696 ELSE
697 log_detail(FND_MESSAGE.GET_STRING( 'IGS', l_message_name));
698 log_detail('Tracking ID :: ' || IGS_GE_NUMBER.TO_CANN( check_existence_rec.tracking_id) ||
699 ' of tracking type :: ' || check_existence_rec.tracking_type);
700 ROLLBACK TO sp_trkitm_aplproc;
701 RETURN FALSE;
702 END IF;
703 END LOOP;
704
705 --
706 -- 5.9.7.15
707 -- Get the admission category, system admission process type, program version
708 -- number for the application instance in loop and input these values to the function
709 -- (admp_get_trkp_types) getting created in the DLD DLD_adsr_rules_changes
710 --
711 l_trk_ind := FALSE;
712
713 OPEN process_rules_cur;
714 FETCH process_rules_cur INTO process_rules_rec;
715 IF process_rules_cur%NOTFOUND THEN
716 CLOSE process_rules_cur;
717 ELSE
718 --
719 -- Modified as a part DLD_adsr_rules_changes DLD enhancement
720 -- call the admp_get_trk_types API which will return the PL/SQL table
721 -- populated with the tracking types attached to the APC through rules
722 --
723 l_trk_types := admp_get_trk_types(
724 p_admission_cat => process_rules_rec.admission_cat,
725 p_admission_process_type => process_rules_rec.s_admission_process_type,
726 p_adm_rule_type => p_requirements_type,
727 p_person_id => p_person_id,
728 p_admission_appl_number => p_admission_appl_number,
729 p_adm_sequence_number => p_sequence_number,
730 p_nominated_prg_cd => p_program_code,
731 p_nominated_prg_version => process_rules_rec.crv_version_number
732 );
733 CLOSE process_rules_cur;
734
735 If l_trk_types.count > 0 THEN
736 l_trk_ind := TRUE;
737 END IF;
738 END IF;
739
740 /*
741 5.9.7.7
742 Process the Tracking steps that are defined in the APC
743 */
744 OPEN process_apc_cur;
745 FETCH process_apc_cur INTO process_apc_rec;
746 IF process_apc_cur%FOUND THEN
747 l_trk_ind := TRUE;
748 LOOP
749 l_trk_types(NVL(l_trk_types.count,0)+1) := process_apc_rec.tracking_type;
750
751 FETCH process_apc_cur INTO process_apc_rec;
752 IF process_apc_cur%NOTFOUND THEN
753 EXIT;
754 END IF;
755 END LOOP;
756 END IF;
757 CLOSE process_apc_cur;
758
759 IF NOT l_trk_ind THEN
760 log_detail(FND_MESSAGE.GET_STRING ( 'IGS', 'IGS_AD_TRAC_NOT_ASSIGN'));
761 ROLLBACK TO sp_trkitm_aplproc;
762 RETURN FALSE;
763 ELSE
764
765 FOR i in 1 .. l_trk_types.count LOOP
766 l_trk_types_rpt := FALSE;
767 FOR j in i+1 .. l_trk_types.count LOOP
768 IF l_trk_types(i) = l_trk_types(j) THEN
769 l_trk_types_rpt := TRUE;
770 EXIT;
771 END IF;
772 END LOOP;
773 IF NOT l_trk_types_rpt THEN
774 l_trk_types_final(NVL(l_trk_types_final.count,0)+1) := l_trk_types(i);
775 END IF;
776 END LOOP;
777
778 FOR k in 1 .. l_trk_types_final.count LOOP
779 OPEN check_duplicate_cur(l_trk_types_final(k));
780 FETCH check_duplicate_cur INTO check_duplicate_cur_rec;
781 IF check_duplicate_cur%NOTFOUND THEN
782 CLOSE check_duplicate_cur;
783 crt_adm_trkitm(
784 p_person_id => p_person_id,
785 p_admission_appl_number => p_admission_appl_number,
789 p_target_days => l_target_days,
786 p_program_code => p_program_code,
787 p_sequence_number => p_sequence_number,
788 p_tracking_type => l_trk_types_final(k),
790 p_completion_due_dt => trk_comp_dt.alias_val
791 );
792 ELSE
793 CLOSE check_duplicate_cur;
794 log_detail('Tracking item :: '|| IGS_GE_NUMBER.TO_CANN(check_duplicate_cur_rec.tracking_id) ||
795 ' for tracking type :: ' || l_trk_types_final(k) ||
796 ' already exist with closed or bypassed steps, hence not recreating');
797 END IF;
798 END LOOP;
799
800 RETURN TRUE;
801 END IF;
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 ROLLBACK TO sp_trkitm_aplproc;
806 p_message_name := 'IGS_AD_PROC_ASSIGN_REQ_FAILED';
807 RETURN FALSE;
808 END crt_adm_trk_itm;
809
810 /* This function will evaluate the Admission Process Category Rules for the APC attached to the application instance
811 and return the list of tracking type(s) to be attached to the application instance in a PLSQL Table.
812 The function(s)/procedure(s) which will attach the tracking type(s) to the application instance should call this
813 function to get the list of the tracking type(s) to be attached to the application Instance.
814 */
815
816 FUNCTION admp_get_trk_types(
817 p_admission_cat IN VARCHAR2,
818 p_admission_process_type IN VARCHAR2,
819 p_adm_rule_type IN VARCHAR2,
820 p_person_id IN NUMBER,
821 p_admission_appl_number IN NUMBER,
822 p_adm_sequence_number IN NUMBER,
823 p_nominated_prg_cd IN VARCHAR2,
824 p_nominated_prg_version IN NUMBER
825 ) RETURN TRK_TYPE AS
826
827 CURSOR c_igs_pe_person_addr IS
828 SELECT row_id
829 FROM igs_pe_person_addr
830 WHERE person_id = p_person_id;
831
832 CURSOR c_igs_pe_mil_services IS
833 SELECT row_id
834 FROM igs_pe_mil_services
835 WHERE person_id = p_person_id;
836
837 CURSOR c_igs_pe_pers_disablty_v IS
838 SELECT rowid row_id
839 FROM igs_pe_pers_disablty
840 WHERE person_id = p_person_id;
841
842 CURSOR c_igs_pe_citizenship_status IS
843 SELECT rowid row_id
844 FROM IGS_PE_EIT
845 WHERE INFORMATION_TYPE = 'PE_STAT_RES_STATUS'
846 AND person_id = p_person_id
847 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);
848
849
850
851 CURSOR c_igs_pe_citizenship_v IS
852 SELECT row_id
853 FROM igs_pe_citizenship_v
854 WHERE party_id = p_person_id;
855
856 CURSOR c_igs_pe_res_dtls IS
857 SELECT row_id
858 FROM igs_pe_res_dtls
859 WHERE person_id = p_person_id;
860
861 CURSOR c_igs_pe_teach_periods_v IS
862 SELECT rowid row_id
863 FROM igs_pe_teach_periods
864 WHERE person_id = p_person_id;
865
866 CURSOR c_igs_ad_tst_rslt_dtls_v IS
867 SELECT iatrdv.rowid row_id
868 FROM igs_ad_tst_rslt_dtls iatrdv
869 WHERE EXISTS (SELECT 'X'
870 FROM igs_ad_test_results iatr
871 WHERE iatr.person_id = p_person_id
872 AND iatrdv.test_results_id = iatr.test_results_id );
873
874 CURSOR c_igs_ad_acad_history_v IS
875 SELECT row_id
876 FROM igs_ad_acad_history_v
877 WHERE person_id = p_person_id;
878
879 CURSOR c_igs_ad_extracurr_act_v IS
880 SELECT rowid
881 FROM HZ_PERSON_INTEREST PI
882 WHERE party_ID = p_person_id;
883
884 CURSOR c_igs_ad_test_results_v IS
885 SELECT rowid row_id
886 FROM igs_ad_test_results
887 WHERE person_id = p_person_id;
888
889 CURSOR c_igs_ad_app_intent_v IS
890 SELECT rowid row_id
891 FROM igs_ad_app_intent
892 WHERE person_id = p_person_id;
893
894 CURSOR c_igs_ad_acad_interest_v IS
895 SELECT rowid row_id
896 FROM igs_ad_acad_interest
897 WHERE person_id = p_person_id;
898
899 CURSOR c_igs_ad_spl_interests_v IS
900 SELECT rowid row_id
901 FROM igs_ad_spl_interests
902 WHERE person_id = p_person_id;
903
904 CURSOR c_igs_ad_spl_talents_v IS
905 SELECT rowid row_id
906 FROM igs_ad_spl_talents
907 WHERE person_id = p_person_id;
908
909 CURSOR c_igs_ad_unit_sets_v IS
910 SELECT rowid row_id
911 FROM igs_ad_unit_sets
912 WHERE person_id = p_person_id;
913
914 CURSOR c_igs_ad_other_inst_v IS
915 SELECT rowid row_id
916 FROM igs_ad_other_inst
917 WHERE person_id = p_person_id;
918
919 CURSOR c_igs_pe_athletic_prg_v IS -- bug 2794983
920 SELECT rowid row_id
921 FROM igs_pe_athletic_prg
925 CURSOR c_igs_pe_acad_honors_v IS
922 WHERE person_id = p_person_id;
923
924 --ssawhney SWS104, ad_acad changed to pe_acad
926 SELECT rowid row_id
927 FROM igs_pe_acad_honors
928 WHERE person_id = p_person_id;
929
930 CURSOR c_rule IS
931 SELECT rul_sequence_number
932 FROM igs_ad_apctr_ru
933 WHERE admission_cat = p_admission_cat
934 AND s_admission_process_type = p_admission_process_type
935 AND s_rule_call_cd = p_adm_rule_type;
936
937
938 l_address_rule_num VARCHAR2(500) := '30171, 30172, 30173, 30174, 30175,30176,30177,30178, 30179'; --address
939 l_military_rule_num VARCHAR2(500) := '30181, 30182, 30183, 30184'; -- Military
940 l_pers_disability_rule_num VARCHAR2(500) := '30185'; -- Pers Disability
941 l_citizen_status_rule_num VARCHAR2(500) := '30186'; -- Citizenship status (Bug Person Stats )
942 l_citizenship_rule_num VARCHAR2(500) := '30187'; -- Citizenship
943 l_pe_results_rule_num VARCHAR2(500) := '30188, 30189'; -- Pe Results
944 l_pe_teaching_num VARCHAR2(500) := '30190,30191'; -- Pe Teaching Periods
945 l_ad_test_rslt_dtls_rule_num VARCHAR2(500) := '30246, 30247, 30248, 30249, 30250, 30251, 30252, 30253, 30254'; -- AD Test Results Details
946 l_acad_hist_rule_num VARCHAR2(500) := '30192, 30193, 30194, 30195, 30196, 30197, 30198, 30199, 30200, 30201, 30202, 30203, 30204, 30205, 30206, 30207, 53211'; -- Acad Hist
947 l_extra_cur_rule_num VARCHAR2(500) := '30208, 30209, 30210, 30211, 30212, 30213, 30230, 30231, 30232, 30233'; -- Extra Curicular activities
948 l_ad_test_rslt_rule_num VARCHAR2(500) := '30236, 30237, 30238, 30239, 30240, 30241, 30242, 30243, 30244, 30245'; -- AD Test Results
949 l_app_intent_rule_num VARCHAR2(500) := '30275'; -- App Intent
950 l_acad_interest_rule_num VARCHAR2(500) := '30276'; -- Ad Acad Interest
951 l_spl_interest_rule_num VARCHAR2(500) := '30277'; -- Spl Interest
952 l_spl_talent_rule_num VARCHAR2(500) := '30278'; -- Spl Talent
953 l_unit_sets_rule_num VARCHAR2(500) := '30279, 30280'; -- UnitSets
954 l_other_inst_rule_num VARCHAR2(500) := '30281'; -- Other Institutions
955 l_athl_prog_rule_num VARCHAR2(500) := '30282, 30283'; -- Athletic PRG
956 l_pe_acad_hnrs_rule_num VARCHAR2(500) := '30286, 30287'; -- Pe Acad Honors
957
958 l_address_rule_exists boolean := false;
959 l_military_rule_exists boolean := false;
960 l_pers_disability_rule_exists boolean := false;
961 l_citizen_status_rule_exists boolean := false;
962 l_citizenship_rule_exists boolean := false;
963 l_pe_results_rule_exists boolean := false;
964 l_pe_teaching_exists boolean := false;
965 l_ad_tst_rslt_dtl_rule_exists boolean := false;
966 l_acad_hist_rule_exists boolean := false;
967 l_extra_cur_rule_exists boolean := false;
968 l_ad_test_rslt_rule_exists boolean := false;
969 l_app_intent_rule_exists boolean := false;
970 l_acad_interest_rule_exists boolean := false;
971 l_spl_interest_rule_exists boolean := false;
972 l_spl_talent_rule_exists boolean := false;
973 l_unit_sets_rule_exists boolean := false;
974 l_other_inst_rule_exists boolean := false;
975 l_athl_prog_rule_exists boolean := false;
976 l_pe_acad_hnrs_rule_exists boolean := false;
977
978
979
980
981
982 /* CURSOR c_rule_items(p_rul_seq_num NUMBER) IS
983 SELECT p_rul_seq_num, item.NAMED_RULE, 'Y'
984 FROM IGS_RU_ITEM item
985 WHERE item.RUL_SEQUENCE_NUMBER = p_rul_seq_num
986 AND item.NAMED_RULE in(30171, 30172, 30173, 30174, 30175,30176,30177,30178, 30174, -- Adreess
987 30181,30182,30183,30184, -- Military Services
988 30185, -- Person Disability
989 30186, -- Citizenship status (Bug Person Stats )
990 30187, -- Citizenship
991 30188, 30189, -- Pe Results
992 30190, -- Pe Teaching Periods
993 30246,30247,30248,30249,30250,30251,30252,30253,30254, -- AD Test Results Details
994 30192,30193,30194,30195,30196,30197,30198,30199,30200,30201,30202,30203,30204,30205,30206,30207,53211 -- Acad Hist
995 30208,30209,30210,30211,30212,30213,30230,30231,30232,30233 -- Extra Curicular activities
996 30236,30237,30238,30239,30240,30241,30242,30243,30244,30245,30248,30249,30250,30251,30252,30253,30254, -- AD Test Results
997 30275 , -- App Intent
998 30276, -- Ad Acad Interest
999 30277, -- Spl Interest
1000 30278, -- Spl Talent
1001 30279,30280, -- UnitSets
1002 30281, -- Other Institutions
1003 30282,30283, -- Athletic PRG
1004 30286,30287 -- Pe Acad Honors
1005 ); */
1006
1007
1008
1009
1010
1011 lv_trk_types TRK_TYPE;
1012 lv_tmp_trk TRK_TYPE;
1013 lv_emp_trk TRK_TYPE;
1014
1015 lv_rowid_per_addr igs_pe_person_addr.row_id%TYPE;
1016 lv_rowid_mil_serv igs_pe_mil_services.row_id%TYPE;
1017 lv_rowid_pers_dis igs_pe_pers_disablty_v.row_id%TYPE;
1018 -- lv_rowid_pe_stat igs_pe_stat_v.row_id%TYPE;
1019 lv_rowid_pe_citizen_status igs_pe_eit_restatus_v.row_id%TYPE;
1020 lv_rowid_pe_ctz igs_pe_citizenship_v.row_id%TYPE;
1021 lv_rowid_pe_res igs_pe_res_dtls.row_id%TYPE;
1022 lv_rowid_pe_teach igs_pe_teach_periods_v.row_id%TYPE;
1026 lv_rowid_test_rslt igs_ad_test_results_v.row_id%TYPE;
1023 lv_rowid_tst_rslt igs_ad_tst_rslt_dtls_v.row_id%TYPE;
1024 lv_rowid_acad_hist igs_ad_acad_history_v.row_id%TYPE;
1025 lv_rowid_extr_act igs_ad_extracurr_act_v.row_id%TYPE;
1027 lv_rowid_app_int igs_ad_app_intent_v.row_id%TYPE;
1028 lv_rowid_acad_int igs_ad_acad_interest_v.row_id%TYPE;
1029 lv_rowid_spl_int igs_ad_spl_interests_v.row_id%TYPE;
1030 lv_rowid_spl_tal IGS_AD_SPL_TALENTS_V.row_id%TYPE;
1031 lv_rowid_unit_set IGS_AD_UNIT_SETS_V.row_id%TYPE;
1032 lv_rowid_oth_inst IGS_AD_OTHER_INST_V.row_id%TYPE;
1033 lv_rowid_ath IGS_PE_ATHLETIC_PRG_V.row_id%TYPE; -- bug 2794983
1034 lv_rowid_acad_hon igs_pe_acad_honors_v.row_id%TYPE;
1035
1036
1037 TYPE rowid_type IS TABLE OF igs_pe_person_addr.row_id%TYPE INDEX BY BINARY_INTEGER;
1038
1039
1040 rowid_per_addr_table rowid_type;
1041 rowid_mil_serv_table rowid_type;
1042 rowid_pers_dis_table rowid_type;
1043 -- rowid_pe_stat_table rowid_type;
1044 rowid_pe_ctzen_stat_table rowid_type;
1045 rowid_pe_ctz_table rowid_type;
1046 rowid_pe_res_table rowid_type;
1047 rowid_pe_teach_table rowid_type;
1048 rowid_tst_rslt_dtl_table rowid_type;
1049 rowid_acad_hist_table rowid_type;
1050 rowid_extr_act_table rowid_type;
1051 rowid_test_rslt_table rowid_type;
1052 rowid_app_int_table rowid_type;
1053 rowid_acad_int_table rowid_type;
1054 rowid_spl_int_table rowid_type;
1055 rowid_spl_tal_table rowid_type;
1056 rowid_unit_set_table rowid_type;
1057 rowid_oth_inst_table rowid_type;
1058 rowid_ath_table rowid_type;
1059 rowid_acad_hon_table rowid_type;
1060
1061
1062
1063 lv_message VARCHAR2(2000);
1064 lv_result VARCHAR2(2000);
1065
1066 -- CONSTANTS
1067 cst_start CONSTANT VARCHAR2(1) := 'S';
1068 cst_processed CONSTANT VARCHAR2(1) := 'P';
1069
1070 lv_per_addr_index VARCHAR2(1);
1071 lv_mil_serv_index VARCHAR2(1);
1072 lv_pers_dis_index VARCHAR2(1);
1073 -- lv_pe_stat_index VARCHAR2(1);
1074 lv_pe_citz_stat_index VARCHAR2(1);
1075 lv_pe_ctz_index VARCHAR2(1);
1076 lv_pe_res_index VARCHAR2(1);
1077 lv_pe_teach_index VARCHAR2(1);
1078 lv_tst_rslt_dtls_index VARCHAR2(1);
1079 lv_acad_hist_index VARCHAR2(1);
1080 lv_extr_act_index VARCHAR2(1);
1081 lv_test_rslt_index VARCHAR2(1);
1082 lv_app_int_index VARCHAR2(1);
1083 lv_acad_int_index VARCHAR2(1);
1084 lv_spl_int_index VARCHAR2(1);
1085 lv_spl_tal_index VARCHAR2(1);
1086 lv_unit_set_index VARCHAR2(1);
1087 lv_oth_inst_index VARCHAR2(1);
1088 lv_ath_index VARCHAR2(1);
1089 lv_acad_hon_index VARCHAR2(1);
1090
1091
1092
1093 -- FLAG to check if the PL/SQL table already has this value
1094 lv_exist VARCHAR2(1);
1095
1096 -- Variable to store the tracking type temporarily to check with the PL/SQL table
1097 -- and to populate it if the table does not hold this value
1098 lv_store VARCHAR2(500);
1099
1100 --Variable to know whether the else PL/SQL table is populated
1101 lv_tmp_pop VARCHAR2(1);
1102
1103 --Variable to know whether the IF Condition is satisfied
1104 lv_if_sat VARCHAR2(1);
1105
1106 lv_rul_sequence_number igs_ad_apctr_ru.rul_sequence_number%TYPE;
1107
1108
1109 FUNCTION is_rul_item_exists ( p_rul_seq_num NUMBER, p_rul_item_seq_num_set VARCHAR2) RETURN BOOLEAN IS
1110
1111 TYPE rul_items_cur_type IS REF CURSOR;
1112 c_rule_items rul_items_cur_type;
1113 l_exists NUMBER := 0;
1114
1115 CURSOR c_rule IS
1116 SELECT RULE_NUMBER FROM IGS_RU_ITEM item
1117 WHERE item.RUL_SEQUENCE_NUMBER = p_rul_seq_num
1118 AND rule_number is NOT NULL
1119 ORDER BY item desc;
1120 l_nest_rule_exists BOOLEAN;
1121
1122 BEGIN
1123 OPEN c_rule_items FOR
1124 ' SELECT 1 FROM IGS_RU_ITEM item ' ||
1125 ' WHERE item.RUL_SEQUENCE_NUMBER = :1 ' ||
1126 ' AND item.NAMED_RULE in (' || p_rul_item_seq_num_set || ') '
1127 USING p_rul_seq_num ;
1128
1129 FETCH c_rule_items INTO l_exists;
1130 CLOSE c_rule_items;
1131 IF (l_exists = 1) then
1132 return TRUE;
1133 ELSE
1134 FOR c_rule_rec IN c_rule LOOP
1135 l_nest_rule_exists := is_rul_item_exists(c_rule_rec.rule_number, p_rul_item_seq_num_set);
1136 IF l_nest_rule_exists THEN
1137 RETURN TRUE;
1138 END IF;
1139 END LOOP;
1140 RETURN FALSE;
1141 END IF;
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 -- DBMS_OUTPUT.PUT_LINE('EXception ' || SQLERRM);
1145 return FALSE;
1146 END;
1147
1148 BEGIN
1149
1150 lv_if_sat := 'N';
1151
1152 /* The PL/SQL table for the IF condition is emptied */
1153 lv_trk_types := lv_emp_trk;
1154
1155
1156 /* Open all the loops so that the rule gets processed for every record of each person */
1157 OPEN c_rule;
1158 LOOP
1162 /* The PL/SQL table for the ELSE condition is emptied */
1159 FETCH c_rule INTO lv_rul_sequence_number;
1160 EXIT WHEN c_rule%NOTFOUND;
1161
1163 lv_tmp_trk := lv_emp_trk;
1164
1165 lv_tmp_pop := 'N';
1166
1167 lv_if_sat := 'N';
1168
1169
1170 l_address_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_address_rule_num);
1171 l_military_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_military_rule_num);
1172 l_pers_disability_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_pers_disability_rule_num);
1173 l_citizen_status_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_citizen_status_rule_num);
1174 l_citizenship_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_citizenship_rule_num);
1175 l_pe_results_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_pe_results_rule_num);
1176 l_pe_teaching_exists := is_rul_item_exists(lv_rul_sequence_number,l_pe_teaching_num);
1177 l_ad_tst_rslt_dtl_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_ad_test_rslt_dtls_rule_num);
1178 l_acad_hist_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_acad_hist_rule_num);
1179 l_extra_cur_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_extra_cur_rule_num);
1180 l_ad_test_rslt_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_ad_test_rslt_rule_num);
1181 l_app_intent_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_app_intent_rule_num);
1182 l_acad_interest_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_acad_interest_rule_num);
1183 l_spl_interest_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_spl_interest_rule_num);
1184 l_spl_talent_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_spl_talent_rule_num);
1185 l_unit_sets_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_unit_sets_rule_num);
1186 l_other_inst_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_other_inst_rule_num);
1187 l_athl_prog_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_athl_prog_rule_num);
1188 l_pe_acad_hnrs_rule_exists := is_rul_item_exists(lv_rul_sequence_number,l_pe_acad_hnrs_rule_num);
1189
1190
1191
1192
1193 IF rowid_per_addr_table.count = 0 AND l_address_rule_exists THEN
1194
1195 OPEN c_igs_pe_person_addr;
1196 FETCH c_igs_pe_person_addr BULK COLLECT INTO rowid_per_addr_table ;
1197 IF c_igs_pe_person_addr%ISOPEN THEN
1198 CLOSE c_igs_pe_person_addr;
1199 END IF;
1200 END IF;
1201
1202 IF rowid_mil_serv_table.count = 0 AND l_military_rule_exists THEN
1203 OPEN c_igs_pe_mil_services;
1204 FETCH c_igs_pe_mil_services BULK COLLECT INTO rowid_mil_serv_table ;
1205 IF c_igs_pe_mil_services%ISOPEN THEN
1206 CLOSE c_igs_pe_mil_services;
1207 END IF;
1208 END IF;
1209
1210
1211 IF rowid_pers_dis_table.count = 0 AND l_pers_disability_rule_exists THEN
1212 OPEN c_igs_pe_pers_disablty_v;
1213 FETCH c_igs_pe_pers_disablty_v BULK COLLECT INTO rowid_pers_dis_table ;
1214 IF c_igs_pe_pers_disablty_v%ISOPEN THEN
1215 CLOSE c_igs_pe_pers_disablty_v;
1216 END IF;
1217 END IF;
1218
1219
1220 IF rowid_pe_ctzen_stat_table.count = 0 AND l_citizen_status_rule_exists THEN
1221 OPEN c_igs_pe_citizenship_status;
1222 FETCH c_igs_pe_citizenship_status BULK COLLECT INTO rowid_pe_ctzen_stat_table ;
1223 IF c_igs_pe_citizenship_status%ISOPEN THEN
1224 CLOSE c_igs_pe_citizenship_status;
1225 END IF;
1226 END IF;
1227
1228 IF rowid_pe_ctz_table.count = 0 AND l_citizenship_rule_exists THEN
1229 OPEN c_igs_pe_citizenship_v;
1230 FETCH c_igs_pe_citizenship_v BULK COLLECT INTO rowid_pe_ctz_table ;
1231 IF c_igs_pe_citizenship_v%ISOPEN THEN
1232 CLOSE c_igs_pe_citizenship_v;
1233 END IF;
1234 END IF;
1235
1236 IF rowid_pe_res_table.count = 0 AND l_pe_results_rule_exists THEN
1237 OPEN c_igs_pe_res_dtls;
1238 FETCH c_igs_pe_res_dtls BULK COLLECT INTO rowid_pe_res_table ;
1239 IF c_igs_pe_res_dtls%ISOPEN THEN
1240 CLOSE c_igs_pe_res_dtls;
1241 END IF;
1242 END IF;
1243
1244 IF rowid_pe_teach_table.count = 0 AND l_pe_teaching_exists THEN
1245 OPEN c_igs_pe_teach_periods_v;
1246 FETCH c_igs_pe_teach_periods_v BULK COLLECT INTO rowid_pe_teach_table ;
1247 IF c_igs_pe_teach_periods_v%ISOPEN THEN
1248 CLOSE c_igs_pe_teach_periods_v;
1249 END IF;
1250 END IF;
1251
1252 IF rowid_tst_rslt_dtl_table.count = 0 AND l_ad_tst_rslt_dtl_rule_exists THEN
1253 OPEN c_igs_ad_tst_rslt_dtls_v;
1254 FETCH c_igs_ad_tst_rslt_dtls_v BULK COLLECT INTO rowid_tst_rslt_dtl_table ;
1255 IF c_igs_ad_tst_rslt_dtls_v%ISOPEN THEN
1256 CLOSE c_igs_ad_tst_rslt_dtls_v;
1257 END IF;
1258 END IF;
1259
1260 IF rowid_acad_hist_table.count = 0 AND l_acad_hist_rule_exists THEN
1261 OPEN c_igs_ad_acad_history_v;
1262 FETCH c_igs_ad_acad_history_v BULK COLLECT INTO rowid_acad_hist_table ;
1263 IF c_igs_ad_acad_history_v%ISOPEN THEN
1264 CLOSE c_igs_ad_acad_history_v;
1265 END IF;
1266 END IF;
1267
1271 IF c_igs_ad_extracurr_act_v%ISOPEN THEN
1268 IF rowid_extr_act_table.count = 0 AND l_extra_cur_rule_exists THEN
1269 OPEN c_igs_ad_extracurr_act_v;
1270 FETCH c_igs_ad_extracurr_act_v BULK COLLECT INTO rowid_extr_act_table ;
1272 CLOSE c_igs_ad_extracurr_act_v;
1273 END IF;
1274 END IF;
1275
1276 IF rowid_test_rslt_table.count = 0 AND l_ad_test_rslt_rule_exists THEN
1277 OPEN c_igs_ad_test_results_v;
1278 FETCH c_igs_ad_test_results_v BULK COLLECT INTO rowid_test_rslt_table ;
1279 IF c_igs_ad_test_results_v%ISOPEN THEN
1280 CLOSE c_igs_ad_test_results_v;
1281 END IF;
1282 END IF;
1283
1284 IF rowid_app_int_table.count = 0 AND l_app_intent_rule_exists THEN
1285 OPEN c_igs_ad_app_intent_v;
1286 FETCH c_igs_ad_app_intent_v BULK COLLECT INTO rowid_app_int_table ;
1287 IF c_igs_ad_app_intent_v%ISOPEN THEN
1288 CLOSE c_igs_ad_app_intent_v;
1289 END IF;
1290 END IF;
1291
1292 IF rowid_acad_int_table.count = 0 AND l_acad_interest_rule_exists THEN
1293 OPEN c_igs_ad_acad_interest_v;
1294 FETCH c_igs_ad_acad_interest_v BULK COLLECT INTO rowid_acad_int_table ;
1295 IF c_igs_ad_acad_interest_v%ISOPEN THEN
1296 CLOSE c_igs_ad_acad_interest_v;
1297 END IF;
1298 END IF;
1299
1300 IF rowid_spl_int_table.count = 0 AND l_spl_interest_rule_exists THEN
1301 OPEN c_igs_ad_spl_interests_v;
1302 FETCH c_igs_ad_spl_interests_v BULK COLLECT INTO rowid_spl_int_table ;
1303 IF c_igs_ad_spl_interests_v%ISOPEN THEN
1304 CLOSE c_igs_ad_spl_interests_v;
1305 END IF;
1306 END IF;
1307
1308 IF rowid_spl_tal_table.count = 0 AND l_spl_talent_rule_exists THEN
1309 OPEN c_igs_ad_spl_talents_v;
1310 FETCH c_igs_ad_spl_talents_v BULK COLLECT INTO rowid_spl_tal_table ;
1311 IF c_igs_ad_spl_talents_v%ISOPEN THEN
1312 CLOSE c_igs_ad_spl_talents_v;
1313 END IF;
1314 END IF;
1315
1316 IF rowid_unit_set_table.count = 0 AND l_unit_sets_rule_exists THEN
1317 OPEN c_igs_ad_unit_sets_v;
1318 FETCH c_igs_ad_unit_sets_v BULK COLLECT INTO rowid_unit_set_table ;
1319 IF c_igs_ad_unit_sets_v%ISOPEN THEN
1320 CLOSE c_igs_ad_unit_sets_v;
1321 END IF;
1322 END IF;
1323
1324 IF rowid_oth_inst_table.count = 0 AND l_other_inst_rule_exists THEN
1325 OPEN c_igs_ad_other_inst_v;
1326 FETCH c_igs_ad_other_inst_v BULK COLLECT INTO rowid_oth_inst_table ;
1327 IF c_igs_ad_other_inst_v%ISOPEN THEN
1328 CLOSE c_igs_ad_other_inst_v;
1329 END IF;
1330 END IF;
1331
1332 IF rowid_ath_table.count = 0 AND l_athl_prog_rule_exists THEN
1333 OPEN c_igs_pe_athletic_prg_v;
1334 FETCH c_igs_pe_athletic_prg_v BULK COLLECT INTO rowid_ath_table ;
1335 IF c_igs_pe_athletic_prg_v%ISOPEN THEN
1336 CLOSE c_igs_pe_athletic_prg_v;
1337 END IF;
1338 END IF;
1339
1340 IF rowid_acad_hon_table.count = 0 AND l_pe_acad_hnrs_rule_exists THEN
1341 OPEN c_igs_pe_acad_honors_v;
1342 FETCH c_igs_pe_acad_honors_v BULK COLLECT INTO rowid_acad_hon_table ;
1343 IF c_igs_pe_acad_honors_v%ISOPEN THEN
1344 CLOSE c_igs_pe_acad_honors_v;
1345 END IF;
1346 END IF;
1347
1348
1349 lv_per_addr_index := rowid_per_addr_table.FIRST;
1350
1351 LOOP
1352
1353 IF lv_per_addr_index IS NOT NULL THEN
1354 lv_rowid_per_addr := rowid_per_addr_table(lv_per_addr_index);
1355 END IF;
1356 lv_mil_serv_index := rowid_mil_serv_table.FIRST;
1357
1358 LOOP
1359
1360 IF lv_mil_serv_index IS NOT NULL THEN
1361 lv_rowid_mil_serv := rowid_mil_serv_table(lv_mil_serv_index);
1362 END IF;
1363 lv_pers_dis_index := rowid_pers_dis_table.FIRST;
1364
1365 LOOP
1366
1367 IF lv_pers_dis_index IS NOT NULL THEN
1368 lv_rowid_pers_dis := rowid_pers_dis_table(lv_pers_dis_index);
1369 END IF;
1370 lv_pe_citz_stat_index := rowid_pe_ctzen_stat_table.FIRST;
1371
1372 LOOP
1373
1374 IF lv_pe_citz_stat_index IS NOT NULL THEN
1375 lv_rowid_pe_citizen_status := rowid_pe_ctzen_stat_table(lv_pe_citz_stat_index);
1376 END IF;
1377 lv_pe_ctz_index := rowid_pe_ctz_table.FIRST;
1378
1379 LOOP
1380
1381 IF lv_pe_ctz_index IS NOT NULL THEN
1382 lv_rowid_pe_ctz := rowid_pe_ctz_table(lv_pe_ctz_index);
1383 END IF;
1384 lv_pe_res_index := rowid_pe_res_table.FIRST;
1385
1386 LOOP
1387
1388 IF lv_pe_res_index IS NOT NULL THEN
1389 lv_rowid_pe_res := rowid_pe_res_table(lv_pe_res_index);
1390 END IF;
1391 lv_pe_teach_index := rowid_pe_teach_table.FIRST;
1392
1393 LOOP
1394
1395 IF lv_pe_teach_index IS NOT NULL THEN
1396 lv_rowid_pe_teach := rowid_pe_teach_table(lv_pe_teach_index);
1397 END IF;
1398
1399 lv_tst_rslt_dtls_index := rowid_tst_rslt_dtl_table.FIRST;
1400
1401 LOOP
1402
1403 IF lv_tst_rslt_dtls_index IS NOT NULL THEN
1404 lv_rowid_tst_rslt := rowid_tst_rslt_dtl_table(lv_tst_rslt_dtls_index);
1405 END IF;
1406 lv_acad_hist_index := rowid_acad_hist_table.FIRST;
1407
1408 LOOP
1412 END IF;
1409
1410 IF lv_acad_hist_index IS NOT NULL THEN
1411 lv_rowid_acad_hist := rowid_acad_hist_table(lv_acad_hist_index);
1413 lv_extr_act_index := rowid_extr_act_table.FIRST;
1414
1415 LOOP
1416
1417 IF lv_extr_act_index IS NOT NULL THEN
1418 lv_rowid_extr_act := rowid_extr_act_table(lv_extr_act_index);
1419 END IF;
1420 lv_test_rslt_index := rowid_test_rslt_table.FIRST;
1421
1422 LOOP
1423
1424 IF lv_test_rslt_index IS NOT NULL THEN
1425 lv_rowid_test_rslt := rowid_test_rslt_table(lv_test_rslt_index);
1426 END IF;
1427 lv_app_int_index := rowid_app_int_table.FIRST;
1428
1429 LOOP
1430
1431 IF lv_app_int_index IS NOT NULL THEN
1432 lv_rowid_app_int := rowid_app_int_table(lv_app_int_index);
1433 END IF;
1434 lv_acad_int_index := rowid_acad_int_table.FIRST;
1435
1436 LOOP
1437
1438
1439 IF lv_acad_int_index IS NOT NULL THEN
1440 lv_rowid_acad_int := rowid_acad_int_table(lv_acad_int_index);
1441 END IF;
1442 lv_spl_int_index := rowid_spl_int_table.FIRST;
1443
1444 LOOP
1445
1446 IF lv_spl_int_index IS NOT NULL THEN
1447 lv_rowid_spl_int := rowid_spl_int_table(lv_spl_int_index);
1448 END IF;
1449 lv_spl_tal_index := rowid_spl_tal_table.FIRST;
1450
1451 LOOP
1452
1453 IF lv_spl_tal_index IS NOT NULL THEN
1454 lv_rowid_spl_tal := rowid_spl_tal_table(lv_spl_tal_index);
1455 END IF;
1456 lv_unit_set_index := rowid_unit_set_table.FIRST;
1457
1458 LOOP
1459
1460 IF lv_unit_set_index IS NOT NULL THEN
1461 lv_rowid_unit_set := rowid_unit_set_table(lv_unit_set_index);
1462 END IF;
1463 lv_oth_inst_index := rowid_oth_inst_table.FIRST;
1464
1465 LOOP
1466
1467 IF lv_oth_inst_index IS NOT NULL THEN
1468 lv_rowid_oth_inst := rowid_oth_inst_table(lv_oth_inst_index);
1469 END IF;
1470 lv_ath_index := rowid_ath_table.FIRST;
1471
1472 LOOP
1473
1474 IF lv_ath_index IS NOT NULL THEN
1475 lv_rowid_ath := rowid_ath_table(lv_ath_index);
1476 END IF;
1477 lv_acad_hon_index := rowid_acad_hon_table.FIRST;
1478
1479 LOOP
1480
1481 IF lv_acad_hon_index IS NOT NULL THEN
1482 lv_rowid_acad_hon := rowid_acad_hon_table(lv_acad_hon_index);
1483 END IF;
1484
1485 lv_result:= NULL;
1486 BEGIN
1487 lv_result := igs_ru_gen_001.rulp_val_senna
1488 (
1489 P_RULE_CALL_NAME => 'AD-TRK-SET',
1490 P_PERSON_ID => p_person_id,
1491 P_MESSAGE => lv_message,
1492 P_RULE_NUMBER => lv_rul_sequence_number,
1493 P_PARAM_1 => p_admission_appl_number,
1494 P_PARAM_2 => p_nominated_prg_cd,
1495 P_PARAM_3 => p_adm_sequence_number,
1496 P_PARAM_10 => ''''||lv_rowid_per_addr||'''',
1497 P_PARAM_11 => ''''||lv_rowid_mil_serv||'''',
1498 P_PARAM_12 => ''''||lv_rowid_pers_dis||'''',
1499 P_PARAM_13 => ''''||lv_rowid_pe_citizen_status||'''',
1500 P_PARAM_14 => ''''||lv_rowid_pe_ctz||'''',
1501 P_PARAM_15 => ''''||lv_rowid_pe_res||'''',
1502 P_PARAM_16 => ''''||lv_rowid_pe_teach||'''',
1503 P_PARAM_17 => ''''||lv_rowid_tst_rslt||'''',
1507 P_PARAM_22 => ''''||lv_rowid_app_int||'''',
1504 P_PARAM_18 => ''''||lv_rowid_acad_hist||'''',
1505 P_PARAM_19 => ''''||lv_rowid_extr_act||'''',
1506 P_PARAM_21 => ''''||lv_rowid_test_rslt||'''',
1508 P_PARAM_23 => ''''||lv_rowid_acad_int||'''',
1509 P_PARAM_24 => ''''||lv_rowid_spl_int||'''',
1510 P_PARAM_25 => ''''||lv_rowid_spl_tal||'''',
1511 P_PARAM_26 => ''''||lv_rowid_unit_set||'''',
1512 P_PARAM_27 => ''''||lv_rowid_oth_inst||'''',
1513 P_PARAM_28 => ''''||lv_rowid_ath||'''',
1514 P_PARAM_29 => ''''||lv_rowid_acad_hon||''''
1515 );
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 NULL;
1519 /*
1520 No need to display in log, since has nothing to do with user defined rule
1521 but parsing and evaluation of the same through code and system defined rule
1522 If it fails here need to debug igs_ru_gen_001.rulp_val_senna
1523
1524 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1525 fnd_message.set_token('NAME','IGS_AD_ADM_REQ.admp_get_trk_types - SQL Error is :'||SQLERRM);
1526 log_detail(FND_MESSAGE.GET);
1527 */
1528 END;
1529
1530 IF lv_result IS NOT NULL THEN
1531 lv_result := ltrim(rtrim(lv_result));
1532 lv_result := substr(lv_result,(instr(lv_result,'{')+1),(instr(lv_result,'}')-2))||',';
1533 END IF;
1534
1535 WHILE lv_result IS NOT NULL
1536 LOOP
1537 lv_store := substr(lv_result,1,(instr(lv_result,',') - 1));
1538 lv_exist := 'N';
1539 lv_result := substr(lv_result,(instr(lv_result,',')+2));
1540
1541 /* This PL/SQL table is to populate the tracking types which are not a part of the ELSE condition*/
1542 IF NVL(igs_ru_gen_001.p_evaluated_part,'*') <> 'ELSE' THEN
1543 /* If the IF condition is satisfied then do not process the rule further.Hence this Flag is activated
1544 and do not populated the same tracking types again.*/
1545 lv_if_sat := 'Y';
1546 IF NVL(lv_trk_types.count,0) <> 0 THEN
1547 FOR i in 1..lv_trk_types.count
1548 LOOP
1549 IF lv_trk_types(i) = lv_store THEN
1550 lv_exist := 'Y';
1551 EXIT WHEN NVL(lv_exist,'N') = 'Y';
1552 END IF;
1553 END LOOP;
1554 END IF;
1555
1556 IF lv_exist = 'N' THEN
1557 lv_trk_types(NVL(lv_trk_types.last,0)+1) := lv_store;
1558 END IF;
1559
1560 ELSE
1561 /* This PL/SQL table is to populate the tracking types which are a part of the ELSE condition*/
1562 IF NVL(lv_tmp_pop,'N') <> 'Y' THEN
1563 IF NVL(lv_tmp_trk.count,0) <> 0 THEN
1564 FOR i in 1..lv_tmp_trk.count
1565 LOOP
1566 IF lv_tmp_trk(i) = lv_store THEN
1567 lv_exist := 'Y';
1568 EXIT WHEN NVL(lv_exist,'N') = 'Y';
1569 END IF;
1570 END LOOP;
1571 END IF;
1572
1573 IF lv_exist = 'N' THEN
1574 lv_tmp_trk(NVL(lv_tmp_trk.last,0)+1) := lv_store;
1575 END IF;
1576
1577 END IF;
1578 END IF;
1582 IF NVL(lv_tmp_trk.count,0) <> 0 AND NVL(lv_tmp_pop,'N')<> 'Y' THEN
1579 END LOOP;
1580
1581 /* To activate the flag to indicate that the else PL/SQL table has been populated */
1583 lv_tmp_pop := 'Y';
1584 END IF;
1585 IF lv_acad_hon_index IS NOT NULL THEN
1586 lv_acad_hon_index := rowid_acad_hon_table.NEXT(lv_acad_hon_index);
1587 END IF;
1588 EXIT WHEN (lv_acad_hon_index IS NULL OR NOT l_pe_acad_hnrs_rule_exists OR lv_if_sat = 'Y' );
1589
1590 END LOOP;
1591
1592 IF lv_ath_index IS NOT NULL THEN
1593 lv_ath_index := rowid_ath_table.NEXT(lv_ath_index);
1594 END IF;
1595 EXIT WHEN (lv_ath_index IS NULL OR NOT l_athl_prog_rule_exists OR lv_if_sat = 'Y' );
1596
1597 END LOOP;
1598
1599 IF lv_oth_inst_index IS NOT NULL THEN
1600 lv_oth_inst_index := rowid_oth_inst_table.NEXT(lv_oth_inst_index);
1601 END IF;
1602 EXIT WHEN (lv_oth_inst_index IS NULL OR NOT l_other_inst_rule_exists OR lv_if_sat = 'Y' );
1603
1604 END LOOP;
1605
1606 IF lv_unit_set_index IS NOT NULL THEN
1607 lv_unit_set_index := rowid_unit_set_table.NEXT(lv_unit_set_index);
1608 END IF;
1609 EXIT WHEN (lv_unit_set_index IS NULL OR NOT l_unit_sets_rule_exists OR lv_if_sat = 'Y' );
1610
1611 END LOOP;
1612
1613 IF lv_spl_tal_index IS NOT NULL THEN
1614 lv_spl_tal_index := rowid_spl_tal_table.NEXT(lv_spl_tal_index);
1615 END IF;
1616 EXIT WHEN (lv_spl_tal_index IS NULL OR NOT l_spl_talent_rule_exists OR lv_if_sat = 'Y' );
1617
1618 END LOOP;
1619
1620 IF lv_spl_int_index IS NOT NULL THEN
1621 lv_spl_int_index := rowid_spl_int_table.NEXT(lv_spl_int_index);
1622 END IF;
1623 EXIT WHEN (lv_spl_int_index IS NULL OR NOT l_spl_interest_rule_exists OR lv_if_sat = 'Y' );
1624
1625 END LOOP;
1626
1627 IF lv_acad_int_index IS NOT NULL THEN
1628 lv_acad_int_index := rowid_acad_int_table.NEXT(lv_acad_int_index);
1629 END IF;
1630 EXIT WHEN (lv_acad_int_index IS NULL OR NOT l_acad_interest_rule_exists OR lv_if_sat = 'Y' );
1631
1632 END LOOP;
1633
1634 IF lv_app_int_index IS NOT NULL THEN
1635 lv_app_int_index := rowid_app_int_table.NEXT(lv_app_int_index);
1636 END IF;
1637 EXIT WHEN (lv_app_int_index IS NULL OR NOT l_app_intent_rule_exists OR lv_if_sat = 'Y' );
1638 END LOOP;
1639
1640 IF lv_test_rslt_index IS NOT NULL THEN
1641 lv_test_rslt_index := rowid_test_rslt_table.NEXT(lv_test_rslt_index);
1642 END IF;
1643 EXIT WHEN (lv_test_rslt_index IS NULL OR NOT l_ad_test_rslt_rule_exists OR lv_if_sat = 'Y' );
1644 END LOOP;
1645
1646 IF lv_extr_act_index IS NOT NULL THEN
1647 lv_extr_act_index := rowid_extr_act_table.NEXT(lv_extr_act_index);
1648 END IF;
1649 EXIT WHEN (lv_extr_act_index IS NULL OR NOT l_extra_cur_rule_exists OR lv_if_sat = 'Y' );
1650 END LOOP;
1651
1652 IF lv_acad_hist_index IS NOT NULL THEN
1653 lv_acad_hist_index := rowid_acad_hist_table.NEXT(lv_acad_hist_index);
1654 END IF;
1655 EXIT WHEN (lv_acad_hist_index IS NULL OR NOT l_acad_hist_rule_exists OR lv_if_sat = 'Y' );
1656 END LOOP;
1657
1658 IF lv_tst_rslt_dtls_index IS NOT NULL THEN
1659 lv_tst_rslt_dtls_index := rowid_tst_rslt_dtl_table.NEXT(lv_tst_rslt_dtls_index);
1660 END IF;
1661 EXIT WHEN (lv_tst_rslt_dtls_index IS NULL OR NOT l_ad_tst_rslt_dtl_rule_exists OR lv_if_sat = 'Y' );
1662 END LOOP;
1663
1664 IF lv_pe_teach_index IS NOT NULL THEN
1665 lv_pe_teach_index := rowid_pe_teach_table.NEXT(lv_pe_teach_index);
1666 END IF;
1667 EXIT WHEN (lv_pe_teach_index IS NULL OR NOT l_pe_teaching_exists OR lv_if_sat = 'Y' );
1668 END LOOP;
1669
1670 IF lv_pe_res_index IS NOT NULL THEN
1674 END LOOP;
1671 lv_pe_res_index := rowid_pe_res_table.NEXT(lv_pe_res_index);
1672 END IF;
1673 EXIT WHEN (lv_pe_res_index IS NULL OR NOT l_pe_results_rule_exists OR lv_if_sat = 'Y' );
1675
1676 IF lv_pe_ctz_index IS NOT NULL THEN
1677 lv_pe_ctz_index := rowid_pe_ctz_table.NEXT(lv_pe_ctz_index);
1678 END IF;
1679 EXIT WHEN (lv_pe_ctz_index IS NULL OR NOT l_citizenship_rule_exists OR lv_if_sat = 'Y' );
1680 END LOOP;
1681
1682 IF lv_pe_citz_stat_index IS NOT NULL THEN
1683 lv_pe_citz_stat_index := rowid_pe_ctzen_stat_table.NEXT(lv_pe_citz_stat_index);
1684 END IF;
1685 EXIT WHEN (lv_pe_citz_stat_index IS NULL OR NOT l_citizen_status_rule_exists OR lv_if_sat = 'Y' );
1686 END LOOP;
1687
1688 IF lv_pers_dis_index IS NOT NULL THEN
1689 lv_pers_dis_index := rowid_pers_dis_table.NEXT(lv_pers_dis_index);
1690 END IF;
1691 EXIT WHEN (lv_pers_dis_index IS NULL OR NOT l_pers_disability_rule_exists OR lv_if_sat = 'Y' );
1692 END LOOP;
1693
1694 IF lv_mil_serv_index IS NOT NULL THEN
1695 lv_mil_serv_index := rowid_mil_serv_table.NEXT(lv_mil_serv_index);
1696 END IF;
1697 EXIT WHEN (lv_mil_serv_index IS NULL OR NOT l_military_rule_exists OR lv_if_sat = 'Y' );
1698 END LOOP;
1699
1700 IF lv_per_addr_index IS NOT NULL THEN
1701 lv_per_addr_index := rowid_per_addr_table.NEXT(lv_per_addr_index);
1702 END IF;
1703 EXIT WHEN (lv_per_addr_index IS NULL OR NOT l_address_rule_exists OR lv_if_sat = 'Y' );
1704 END LOOP;
1705
1706 /* Assign the tracking types returned from the else clause to
1707 the return value if the PL/SQL table is not returning anything */
1708
1709 IF NVL(lv_if_sat,'N') = 'N' AND NVL(lv_tmp_pop,'N') = 'Y' THEN
1710 -- Tracking types from previous rules IF set exist
1711 IF NVL(lv_trk_types.count,0) <> 0 THEN
1712 FOR k in lv_tmp_trk.first..lv_tmp_trk.last
1713 LOOP
1714 lv_exist := 'N';
1715 FOR l in lv_trk_types.first..lv_trk_types.last
1716 LOOP
1717 IF lv_trk_types(l) = lv_tmp_trk(k) THEN
1718 lv_exist := 'Y';
1719 EXIT WHEN lv_exist = 'Y';
1720 END IF;
1721 END LOOP;
1722 IF NVL(lv_exist,'N') = 'N' THEN
1723 lv_trk_types(NVL(lv_trk_types.last,0) + 1) := lv_tmp_trk(k);
1724 END IF;
1725 END LOOP;
1726
1727 -- Tracking types from previous rules IF set do not exist
1728 ELSE
1729 FOR k in lv_tmp_trk.first..lv_tmp_trk.last
1730 LOOP
1731 lv_trk_types(NVL(lv_trk_types.count,0)) := lv_tmp_trk(k);
1732 END LOOP;
1733 END IF;
1734 END IF;
1735
1736 END LOOP;
1737 CLOSE c_rule;
1738
1739 RETURN lv_trk_types;
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 IF c_rule%ISOPEN THEN
1743 CLOSE c_rule;
1744 END IF;
1745 RETURN lv_emp_trk;
1746 END admp_get_trk_types;
1747
1748
1749
1750 END igs_ad_adm_req;
[Home] [Help]