1 PACKAGE BODY igs_ad_recruitments_pkg AS
2 /* $Header: IGSAI87B.pls 115.15 2003/12/09 11:07:12 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_recruitments%RowType;
5 new_references igs_ad_recruitments%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,--DEFAULT NULL,
10 x_certainty_of_choice_id IN NUMBER,-- DEFAULT NULL,
11 x_religion_cd IN VARCHAR2 ,--DEFAULT NULL,
12 x_adv_studies_classes IN NUMBER ,--DEFAULT NULL,
13 x_honors_classes IN NUMBER,-- DEFAULT NULL,
14 x_class_size IN NUMBER,-- DEFAULT NULL,
15 x_sec_school_location_id IN NUMBER,-- DEFAULT NULL,
16 x_percent_plan_higher_edu IN NUMBER,-- DEFAULT NULL,
17 x_recruitment_id IN NUMBER ,--DEFAULT NULL,
18 x_person_id IN NUMBER ,--DEFAULT NULL,
19 x_special_interest_id IN NUMBER ,--DEFAULT NULL,
20 x_priority IN VARCHAR2 ,--DEFAULT NULL,
21 x_vip IN VARCHAR2,-- DEFAULT NULL,
22 x_deactivate_recruit_status IN VARCHAR2 ,--DEFAULT NULL,
23 x_program_interest_id IN NUMBER ,--DEFAULT NULL,
24 x_institution_size_id IN NUMBER ,--DEFAULT NULL,
25 x_institution_control_id IN NUMBER,-- DEFAULT NULL,
26 x_institution_setting_id IN NUMBER ,--DEFAULT NULL,
27 x_institution_location_id IN NUMBER ,--DEFAULT NULL,
28 x_special_services_id IN NUMBER,-- DEFAULT NULL,
29 x_employment_id IN NUMBER,-- DEFAULT NULL,
30 x_housing_id IN NUMBER ,--DEFAULT NULL,
31 x_degree_goal_id IN NUMBER,-- DEFAULT NULL,
32 x_unit_set_id IN NUMBER ,--DEFAULT NULL,
33 x_creation_date IN DATE ,--DEFAULT NULL,
34 x_created_by IN NUMBER ,--DEFAULT NULL,
35 x_last_update_date IN DATE ,--DEFAULT NULL,
36 x_last_updated_by IN NUMBER ,--DEFAULT NULL,
37 x_last_update_login IN NUMBER-- DEFAULT NULL
38 ) AS
39
40 /*************************************************************
41 Created By : samaresh
42 Date Created By : 15-MAY-2000
43 Purpose : to set values for the columns
44 Know limitations, enhancements or remarks
45 Change History
46 Who When What
47 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
48 Removed the processing for the Obsolete column 'PROBABILITY'
49 (reverse chronological order - newest change first)
50 ***************************************************************/
51
52 CURSOR cur_old_ref_values IS
53 SELECT *
54 FROM IGS_AD_RECRUITMENTS
55 WHERE rowid = x_rowid;
56
57 BEGIN
58
59 l_rowid := x_rowid;
60
61 -- Code for setting the Old and New Reference Values.
62 -- Populate Old Values.
63 Open cur_old_ref_values;
64 Fetch cur_old_ref_values INTO old_references;
65 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
66 Close cur_old_ref_values;
67 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
68 IGS_GE_MSG_STACK.ADD;
69 App_Exception.Raise_Exception;
70 Return;
71 END IF;
72 Close cur_old_ref_values;
73
74 -- Populate New Values.
75 new_references.certainty_of_choice_id := x_certainty_of_choice_id;
76 new_references.religion_cd := x_religion_cd;
77 new_references.adv_studies_classes := x_adv_studies_classes;
78 new_references.honors_classes := x_honors_classes;
79 new_references.class_size := x_class_size;
80 new_references.sec_school_location_id := x_sec_school_location_id;
81 new_references.percent_plan_higher_edu := x_percent_plan_higher_edu;
82 new_references.recruitment_id := x_recruitment_id;
83 new_references.person_id := x_person_id;
84 new_references.special_interest_id := x_special_interest_id;
85 new_references.priority := x_priority;
86 new_references.vip := x_vip;
87 new_references.deactivate_recruit_status := x_deactivate_recruit_status;
88 new_references.program_interest_id := x_program_interest_id;
89 new_references.institution_size_id := x_institution_size_id;
90 new_references.institution_control_id := x_institution_control_id;
91 new_references.institution_setting_id := x_institution_setting_id;
92 new_references.institution_location_id := x_institution_location_id;
93 new_references.special_services_id := x_special_services_id;
94 new_references.employment_id := x_employment_id;
95 new_references.housing_id := x_housing_id;
96 new_references.degree_goal_id := x_degree_goal_id;
97 new_references.unit_set_id := x_unit_set_id;
98 IF (p_action = 'UPDATE') THEN
99 new_references.creation_date := old_references.creation_date;
100 new_references.created_by := old_references.created_by;
101 ELSE
102 new_references.creation_date := x_creation_date;
103 new_references.created_by := x_created_by;
104 END IF;
105 new_references.last_update_date := x_last_update_date;
106 new_references.last_updated_by := x_last_updated_by;
107 new_references.last_update_login := x_last_update_login;
108
109 END Set_Column_Values;
110
111 PROCEDURE Check_Constraints (
112 Column_Name IN VARCHAR2 ,
113 Column_Value IN VARCHAR2 ) AS
114 /*************************************************************
115 Created By : samaresh
116 Date Created By : 15-MAY-2000
117 Purpose : for adding check constraints on the columns
118 Know limitations, enhancements or remarks
119 Change History
120 Who When What
121
122 (reverse chronological order - newest change first)
123 vdixit 11-Oct-2001 Added the check constraint for
124 adv_studies_classes bug 2030644
125 ***************************************************************/
126
127 BEGIN
128
129 IF column_name IS NULL THEN
130 NULL;
131 ELSIF UPPER(column_name) = 'HONORS_CLASSES' THEN
132 new_references.honors_classes := IGS_GE_NUMBER.TO_NUM(column_value);
133 ELSIF UPPER(column_name) = 'VIP' THEN
134 new_references.vip := column_value;
135 ELSIF UPPER(column_name) = 'DEACTIVATE_RECRUIT_STATUS' THEN
136 new_references.deactivate_recruit_status := column_value;
137 ELSIF UPPER(column_name) = 'PERCENT_PLAN_HIGHER_EDU' THEN
138 new_references.percent_plan_higher_edu := IGS_GE_NUMBER.TO_NUM(column_value);
139 ELSIF UPPER(column_name) = 'CLASS_SIZE' THEN
140 new_references.class_size := IGS_GE_NUMBER.TO_NUM(column_value);
141 ELSIF UPPER(column_name) = 'ADV_STUDIES_CLASSES' THEN --added as a part of bug 2030644
142 new_references.adv_studies_classes := IGS_GE_NUMBER.TO_NUM(column_value);
143 END IF;
144
145 -- The following code checks for check constraints on the Columns.
146 IF Upper(Column_Name) = 'HONORS_CLASSES' OR
147 Column_Name IS NULL THEN
148 IF NOT (new_references.honors_classes >= 0) THEN
149 Fnd_Message.Set_Name('IGS','IGS_AD_HONORS_CLS');
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153
154 -- The following code checks for check constraints on the Columns.
155 IF Upper(Column_Name) = 'VIP' OR
156 Column_Name IS NULL THEN
157 IF NOT (new_references.vip IN ('Y','N')) THEN
158 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
159 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_VIP'));
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164
165 -- The following code checks for check constraints on the Columns.
166 IF Upper(Column_Name) = 'DEACTIVATE_RECRUIT_STATUS' OR
167 Column_Name IS NULL THEN
168 IF NOT (new_references.deactivate_recruit_status IN ('Y','N')) THEN
169 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
170 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DEACTIVATE_REC_STAT'));
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 -- The following code checks for check constraints on the Columns.
177 IF Upper(Column_Name) = 'PERCENT_PLAN_HIGHER_EDU' OR
178 Column_Name IS NULL THEN
179 IF NOT (new_references.percent_plan_higher_edu > 0) THEN
180 Fnd_Message.Set_Name('IGS','IGS_AD_PER_HIGH_EDU');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185
186 -- The following code checks for check constraints on the Columns.
187 IF Upper(Column_Name) = 'CLASS_SIZE' OR
188 Column_Name IS NULL THEN
189 IF NOT (new_references.class_size > 0) THEN
190 Fnd_Message.Set_Name('IGS','IGS_AD_CLASS_SIZE');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END IF;
195
196 -- The following code checks for check constraints on the Columns.
197 -- Added as a part of bug 2030644
198
199 IF Upper(Column_Name) = 'ADV_STUDIES_CLASSES' OR
200 Column_Name IS NULL THEN
201 IF new_references.adv_studies_classes < 0 THEN
202 Fnd_Message.Set_Name('IGS','IGS_AD_ADCLS_NOT_VAL');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206 END IF;
207
208 END Check_Constraints;
209
210 PROCEDURE Check_Uniqueness AS
211 /*************************************************************
212 Created By : samaresh.in
213 Date Created By : 15-MAY-2000
214 Purpose :
215 Know limitations, enhancements or remarks
216 Change History
217 Who When What
218
219 (reverse chronological order - newest change first)
220 ***************************************************************/
221
222 begin
223 IF Get_Uk_For_Validation (
224 new_references.person_id
225 ) THEN
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
227 IGS_GE_MSG_STACK.ADD;
228 app_exception.raise_exception;
229 END IF;
230 END Check_Uniqueness ;
231
232 PROCEDURE Check_Parent_Existance AS
233 /*************************************************************
234 Created By : samaresh
235 Date Created By : 15-MAY-2000
236 Purpose : to check whether parent exists
237 Know limitations, enhancements or remarks
238 Change History
239 Who When What
240
241 (reverse chronological order - newest change first)
242 ***************************************************************/
243
244 BEGIN
245
246 IF (((old_references.institution_location_id = new_references.institution_location_id)) OR
247 ((new_references.institution_location_id IS NULL))) THEN
248 NULL;
249 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
250 new_references.institution_location_id,
251 'INSTITUTION_LOCATION',
252 'N'
253 ) THEN
254 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
255 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INST_LOC'));
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END IF;
259
260 IF (((old_references.sec_school_location_id = new_references.sec_school_location_id)) OR
261 ((new_references.sec_school_location_id IS NULL))) THEN
262 NULL;
263 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
264 new_references.sec_school_location_id,
265 'SEC_SCHOOL_LOCATION',
266 'N'
267 ) THEN
268 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
269 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEC_SCH_LOC'));
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273
274 IF (((old_references.religion_cd = new_references.religion_cd)) OR
275 ((new_references.religion_cd IS NULL))) THEN
276 NULL;
277 ELSIF NOT Igs_lookups_view_pkg.Get_PK_For_Validation (
278 'PE_RELIGION',new_references.religion_cd
279 ) THEN
280 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
281 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_RELG'));
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285
286 IF (((old_references.person_id = new_references.person_id)) OR
287 ((new_references.person_id IS NULL))) THEN
288 NULL;
289 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
290 new_references.person_id
291 ) THEN
292 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
293 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297
298 IF (((old_references.degree_goal_id = new_references.degree_goal_id)) OR
299 ((new_references.degree_goal_id IS NULL))) THEN
300 NULL;
301 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
302 new_references.degree_goal_id,
303 'DESIRED_DEGREE',
304 'N'
305 ) THEN
306 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
307 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DEGREE_GOAL'));
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311
312 IF (((old_references.unit_set_id = new_references.unit_set_id)) OR
313 ((new_references.unit_set_id IS NULL))) THEN
314 NULL;
315 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
316 new_references.unit_set_id,
317 'DESIRED_UNIT_SET',
318 'N'
319 ) THEN
320 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
321 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_PS_UNIT_SET'));
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 END IF;
325
326 IF (((old_references.institution_setting_id = new_references.institution_setting_id)) OR
327 ((new_references.institution_setting_id IS NULL))) THEN
328 NULL;
329 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
330 new_references.institution_setting_id ,
331 'INSTITUTION_SETTING',
332 'N'
333 ) THEN
334 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
335 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INST_SETTING'));
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339
340 IF (((old_references.program_interest_id = new_references.program_interest_id)) OR
341 ((new_references.program_interest_id IS NULL))) THEN
342 NULL;
343 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
344 new_references.program_interest_id ,
345 'PROGRAM_INTEREST',
346 'N'
347 ) THEN
348 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
349 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_INTEREST'));
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353
354 IF (((old_references.certainty_of_choice_id = new_references.certainty_of_choice_id)) OR
355 ((new_references.certainty_of_choice_id IS NULL))) THEN
356 NULL;
357 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
358 new_references.certainty_of_choice_id ,
359 'CERTAINTY_OF_CHOICE',
360 'N'
361 ) THEN
362 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CERTAIN_OF_CHOICE'));
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 END IF;
367
368 IF (((old_references.special_services_id = new_references.special_services_id)) OR
369 ((new_references.special_services_id IS NULL))) THEN
370 NULL;
371 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
372 new_references.special_services_id ,
373 'SPECIAL_SERVICES',
374 'N'
375 ) THEN
376 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
377 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_SERVICES'));
378 IGS_GE_MSG_STACK.ADD;
379 App_Exception.Raise_Exception;
380 END IF;
381
382 IF (((old_references.employment_id = new_references.employment_id)) OR
383 ((new_references.employment_id IS NULL))) THEN
384 NULL;
385 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
386 new_references.employment_id ,
387 'DESIRED_EMPLOYMENT',
388 'N'
389 ) THEN
390 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
391 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EMPLOYMENT_INF'));
392 IGS_GE_MSG_STACK.ADD;
393 App_Exception.Raise_Exception;
394 END IF;
395
396 IF (((old_references.institution_size_id = new_references.institution_size_id)) OR
397 ((new_references.institution_size_id IS NULL))) THEN
398 NULL;
399 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
400 new_references.institution_size_id ,
401 'INSTITUTION_SIZE',
402 'N'
403 ) THEN
404 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
405 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INST_SIZE'));
406 IGS_GE_MSG_STACK.ADD;
407 App_Exception.Raise_Exception;
408 END IF;
409
410 IF (((old_references.special_interest_id = new_references.special_interest_id)) OR
411 ((new_references.special_interest_id IS NULL))) THEN
412 NULL;
413 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
414 new_references.special_interest_id ,
415 'SPECIAL_INTEREST',
416 'N'
417 ) THEN
418 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
419 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_INTEREST'));
420 IGS_GE_MSG_STACK.ADD;
421 App_Exception.Raise_Exception;
422 END IF;
423
424 IF (((old_references.housing_id = new_references.housing_id)) OR
425 ((new_references.housing_id IS NULL))) THEN
426 NULL;
427 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
428 new_references.housing_id ,
429 'DESIRED_HOUSING',
430 'N'
431 ) THEN
432 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
433 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_HOUSE_INF'));
434 IGS_GE_MSG_STACK.ADD;
435 App_Exception.Raise_Exception;
436 END IF;
437
438 IF (((old_references.institution_control_id = new_references.institution_control_id)) OR
439 ((new_references.institution_control_id IS NULL))) THEN
440 NULL;
441 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
442 new_references.institution_control_id ,
443 'INSTITUTION_CONTROL',
444 'N'
445 ) THEN
446 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
447 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INST_CONTROL'));
448 IGS_GE_MSG_STACK.ADD;
449 App_Exception.Raise_Exception;
450 END IF;
451
452 END Check_Parent_Existance;
453
454 FUNCTION Get_PK_For_Validation (
455 x_recruitment_id IN NUMBER
456 ) RETURN BOOLEAN AS
457
458 /*************************************************************
459 Created By : samaresh
460 Date Created By : 15-MAY-2000
461 Purpose : to check for primary key
462 Know limitations, enhancements or remarks
463 Change History
464 Who When What
465
466 (reverse chronological order - newest change first)
467 ***************************************************************/
468
469 CURSOR cur_rowid IS
470 SELECT rowid
471 FROM igs_ad_recruitments
472 WHERE recruitment_id = x_recruitment_id
473 FOR UPDATE NOWAIT;
474
475 lv_rowid cur_rowid%RowType;
476
477 BEGIN
478
479 Open cur_rowid;
480 Fetch cur_rowid INTO lv_rowid;
481 IF (cur_rowid%FOUND) THEN
482 Close cur_rowid;
483 Return(TRUE);
484 ELSE
485 Close cur_rowid;
486 Return(FALSE);
487 END IF;
488 END Get_PK_For_Validation;
489
490 FUNCTION Get_UK_For_Validation (
491 x_person_id IN NUMBER
492 ) RETURN BOOLEAN AS
493
494 /*************************************************************
495 Created By : samaresh.in
496 Date Created By : 15-MAY-2000
497 Purpose :
498 Know limitations, enhancements or remarks
499 Change History
500 Who When What
501
502 (reverse chronological order - newest change first)
503 ***************************************************************/
504
505 CURSOR cur_rowid IS
506 SELECT rowid
507 FROM igs_ad_recruitments
508 WHERE person_id = x_person_id
509 and ((l_rowid is null) or (rowid <> l_rowid));
510 lv_rowid cur_rowid%RowType;
511
512 BEGIN
513
514 Open cur_rowid;
515 Fetch cur_rowid INTO lv_rowid;
516 IF (cur_rowid%FOUND) THEN
517 Close cur_rowid;
518 return (true);
519 ELSE
520 close cur_rowid;
521 return(false);
522 END IF;
523 END Get_UK_For_Validation ;
524
525 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
526 x_code_id IN NUMBER
527 ) AS
528
529 /*************************************************************
530 Created By : samaresh
531 Date Created By : 15-MAY-2000
532 Purpose : check for the existance of the foreign keys
533 Know limitations, enhancements or remarks
534 Change History
535 Who When What
536
537 (reverse chronological order - newest change first)
538 ***************************************************************/
539
540 CURSOR cur_rowid1 IS
541 SELECT rowid
542 FROM igs_ad_recruitments
543 WHERE institution_location_id = x_code_id ;
544
545 CURSOR cur_rowid2 IS
546 SELECT rowid
547 FROM igs_ad_recruitments
548 WHERE degree_goal_id = x_code_id ;
549
550 CURSOR cur_rowid3 IS
551 SELECT rowid
552 FROM igs_ad_recruitments
553 WHERE unit_set_id = x_code_id ;
554
555 CURSOR cur_rowid4 IS
556 SELECT rowid
557 FROM igs_ad_recruitments
558 WHERE institution_setting_id = x_code_id ;
559
560 CURSOR cur_rowid5 IS
561 SELECT rowid
562 FROM igs_ad_recruitments
563 WHERE program_interest_id = x_code_id ;
564
565 CURSOR cur_rowid6 IS
566 SELECT rowid
567 FROM igs_ad_recruitments
568 WHERE certainty_of_choice_id = x_code_id ;
569
570 CURSOR cur_rowid7 IS
571 SELECT rowid
572 FROM igs_ad_recruitments
573 WHERE special_services_id = x_code_id ;
574
575 CURSOR cur_rowid8 IS
576 SELECT rowid
577 FROM igs_ad_recruitments
578 WHERE employment_id = x_code_id ;
579
580 CURSOR cur_rowid9 IS
581 SELECT rowid
582 FROM igs_ad_recruitments
583 WHERE special_interest_id = x_code_id ;
584
585 CURSOR cur_rowid10 IS
586 SELECT rowid
587 FROM igs_ad_recruitments
588 WHERE housing_id = x_code_id ;
589
590 CURSOR cur_rowid11 IS
591 SELECT rowid
592 FROM igs_ad_recruitments
593 WHERE institution_control_id = x_code_id ;
594
595 CURSOR cur_rowid12 IS
596 SELECT rowid
597 FROM igs_ad_recruitments
598 WHERE institution_size_id = x_code_id ;
599
600 lv_rowid cur_rowid1%RowType;
601
602 BEGIN
603
604 Open cur_rowid1;
605 Fetch cur_rowid1 INTO lv_rowid;
606 IF (cur_rowid1%FOUND) THEN
607 Close cur_rowid1;
608 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK3');
609 IGS_GE_MSG_STACK.ADD;
610 App_Exception.Raise_Exception;
611 Return;
612 END IF;
613 Close cur_rowid1;
614
615 Open cur_rowid2;
616 Fetch cur_rowid2 INTO lv_rowid;
617 IF (cur_rowid2%FOUND) THEN
618 Close cur_rowid2;
619 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK8');
620 IGS_GE_MSG_STACK.ADD;
621 App_Exception.Raise_Exception;
622 Return;
623 END IF;
624 Close cur_rowid2;
625
626 Open cur_rowid3;
627 Fetch cur_rowid3 INTO lv_rowid;
628 IF (cur_rowid3%FOUND) THEN
629 Close cur_rowid3;
630 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK9');
631 IGS_GE_MSG_STACK.ADD;
632 App_Exception.Raise_Exception;
633 Return;
634 END IF;
635 Close cur_rowid3;
636
637 Open cur_rowid4;
638 Fetch cur_rowid4 INTO lv_rowid;
639 IF (cur_rowid4%FOUND) THEN
640 Close cur_rowid4;
641 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK4');
642 IGS_GE_MSG_STACK.ADD;
643 App_Exception.Raise_Exception;
644 Return;
645 END IF;
646 Close cur_rowid4;
647
648 Open cur_rowid5;
649 Fetch cur_rowid5 INTO lv_rowid;
650 IF (cur_rowid5%FOUND) THEN
651 Close cur_rowid5;
652 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK');
653 IGS_GE_MSG_STACK.ADD;
654 App_Exception.Raise_Exception;
655 Return;
656 END IF;
657 Close cur_rowid5;
658
659 Open cur_rowid6;
660 Fetch cur_rowid6 INTO lv_rowid;
661 IF (cur_rowid6%FOUND) THEN
662 Close cur_rowid6;
663 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK10');
664 IGS_GE_MSG_STACK.ADD;
665 App_Exception.Raise_Exception;
666 Return;
667 END IF;
668 Close cur_rowid6;
669
670 Open cur_rowid7;
671 Fetch cur_rowid7 INTO lv_rowid;
672 IF (cur_rowid7%FOUND) THEN
673 Close cur_rowid7;
674 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK5');
675 IGS_GE_MSG_STACK.ADD;
676 App_Exception.Raise_Exception;
677 Return;
678 END IF;
679 Close cur_rowid7;
680
681 Open cur_rowid8;
682 Fetch cur_rowid8 INTO lv_rowid;
683 IF (cur_rowid8%FOUND) THEN
684 Close cur_rowid8;
685 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK6');
686 IGS_GE_MSG_STACK.ADD;
687 App_Exception.Raise_Exception;
688 Return;
689 END IF;
690 Close cur_rowid8;
691
692 Open cur_rowid9;
693 Fetch cur_rowid9 INTO lv_rowid;
694 IF (cur_rowid9%FOUND) THEN
695 Close cur_rowid9;
696 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK1');
697 IGS_GE_MSG_STACK.ADD;
698 App_Exception.Raise_Exception;
699 Return;
700 END IF;
701 Close cur_rowid9;
702
703 Open cur_rowid10;
704 Fetch cur_rowid10 INTO lv_rowid;
705 IF (cur_rowid10%FOUND) THEN
706 Close cur_rowid10;
707 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK7');
708 IGS_GE_MSG_STACK.ADD;
709 App_Exception.Raise_Exception;
710 Return;
711 END IF;
712 Close cur_rowid10;
713
714 Open cur_rowid11;
715 Fetch cur_rowid11 INTO lv_rowid;
716 IF (cur_rowid11%FOUND) THEN
717 Close cur_rowid11;
718 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK2');
719 IGS_GE_MSG_STACK.ADD;
720 App_Exception.Raise_Exception;
721 Return;
722 END IF;
723 Close cur_rowid11;
724
725 Open cur_rowid12;
726 Fetch cur_rowid12 INTO lv_rowid;
727 IF (cur_rowid12%FOUND) THEN
728 Close cur_rowid12;
729 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_ACDC_FK11');
730 IGS_GE_MSG_STACK.ADD;
731 App_Exception.Raise_Exception;
732 Return;
733 END IF;
734 Close cur_rowid12;
735
736
737 END Get_FK_Igs_Ad_Code_Classes;
738
739 PROCEDURE Get_FK_Igs_Pe_Person (
740 x_person_id IN NUMBER
741 ) AS
742
743 /*************************************************************
744 Created By : samaresh
745 Date Created By : 15-MAY-2000
746 Purpose :
747 Know limitations, enhancements or remarks
748 Change History
749 Who When What
750
751 (reverse chronological order - newest change first)
752 ***************************************************************/
753
754 CURSOR cur_rowid IS
755 SELECT rowid
756 FROM igs_ad_recruitments
757 WHERE person_id = x_person_id ;
758
759 lv_rowid cur_rowid%RowType;
760
761 BEGIN
762
763 Open cur_rowid;
764 Fetch cur_rowid INTO lv_rowid;
765 IF (cur_rowid%FOUND) THEN
766 Close cur_rowid;
767 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AREC_PE_FK');
768 IGS_GE_MSG_STACK.ADD;
769 App_Exception.Raise_Exception;
770 Return;
771 END IF;
772 Close cur_rowid;
773
774 END Get_FK_Igs_Pe_Person;
775
776 PROCEDURE Before_DML (
777 p_action IN VARCHAR2,
778 x_rowid IN VARCHAR2,-- DEFAULT NULL,
779 x_certainty_of_choice_id IN NUMBER,-- DEFAULT NULL,
780 x_religion_cd IN VARCHAR2,-- DEFAULT NULL,
781 x_adv_studies_classes IN NUMBER,-- DEFAULT NULL,
782 x_honors_classes IN NUMBER ,--DEFAULT NULL,
783 x_class_size IN NUMBER ,--DEFAULT NULL,
784 x_sec_school_location_id IN NUMBER ,--DEFAULT NULL,
785 x_percent_plan_higher_edu IN NUMBER ,--DEFAULT NULL,
786 x_recruitment_id IN NUMBER ,--DEFAULT NULL,
787 x_person_id IN NUMBER ,--DEFAULT NULL,
788 x_special_interest_id IN NUMBER ,--DEFAULT NULL,
789 x_priority IN VARCHAR2 ,--DEFAULT NULL,
790 x_vip IN VARCHAR2 ,--DEFAULT NULL,
791 x_deactivate_recruit_status IN VARCHAR2 ,--DEFAULT NULL,
792 x_program_interest_id IN NUMBER ,--DEFAULT NULL,
793 x_institution_size_id IN NUMBER ,--DEFAULT NULL,
794 x_institution_control_id IN NUMBER ,--DEFAULT NULL,
795 x_institution_setting_id IN NUMBER ,--DEFAULT NULL,
796 x_institution_location_id IN NUMBER ,--DEFAULT NULL,
797 x_special_services_id IN NUMBER ,--DEFAULT NULL,
798 x_employment_id IN NUMBER ,--DEFAULT NULL,
799 x_housing_id IN NUMBER ,--DEFAULT NULL,
800 x_degree_goal_id IN NUMBER,-- DEFAULT NULL,
801 x_unit_set_id IN NUMBER,-- DEFAULT NULL,
802 x_creation_date IN DATE ,--DEFAULT NULL,
803 x_created_by IN NUMBER ,--DEFAULT NULL,
804 x_last_update_date IN DATE,-- DEFAULT NULL,
805 x_last_updated_by IN NUMBER,-- DEFAULT NULL,
806 x_last_update_login IN NUMBER-- DEFAULT NULL
807 ) AS
808 /*************************************************************
809 Created By : samaresh
810 Date Created By : 15-MAY-2000
811 Purpose :
812 Know limitations, enhancements or remarks
813 Change History
814 Who When What
815 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
816 Removed the processing for the Obsolete column 'PROBABILITY'
817 (reverse chronological order - newest change first)
818 ***************************************************************/
819
820 BEGIN
821
822 Set_Column_Values (
823 p_action,
824 x_rowid,
825 x_certainty_of_choice_id,
826 x_religion_cd,
827 x_adv_studies_classes,
828 x_honors_classes,
829 x_class_size,
830 x_sec_school_location_id,
831 x_percent_plan_higher_edu,
832 x_recruitment_id,
833 x_person_id,
834 x_special_interest_id,
835 x_priority,
836 x_vip,
837 x_deactivate_recruit_status,
838 x_program_interest_id,
839 x_institution_size_id,
840 x_institution_control_id,
841 x_institution_setting_id,
842 x_institution_location_id,
843 x_special_services_id,
844 x_employment_id,
845 x_housing_id,
846 x_degree_goal_id,
847 x_unit_set_id,
848 x_creation_date,
849 x_created_by,
850 x_last_update_date,
851 x_last_updated_by,
852 x_last_update_login
853 );
854
855 IF (p_action = 'INSERT') THEN
856 -- Call all the procedures related to Before Insert.
857 Null;
858 IF Get_Pk_For_Validation(
859 new_references.recruitment_id) THEN
860 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
861 IGS_GE_MSG_STACK.ADD;
862 App_Exception.Raise_Exception;
863 END IF;
864 Check_Uniqueness;
865 Check_Constraints;
866 Check_Parent_Existance;
867 ELSIF (p_action = 'UPDATE') THEN
868 -- Call all the procedures related to Before Update.
869 Check_Uniqueness;
870 Check_Constraints;
871 Check_Parent_Existance;
872 ELSIF (p_action = 'DELETE') THEN
873 -- Call all the procedures related to Before Delete.
874 Null;
875 ELSIF (p_action = 'VALIDATE_INSERT') THEN
876 -- Call all the procedures related to Before Insert.
877 IF Get_PK_For_Validation (
878 new_references.recruitment_id) THEN
879 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
880 IGS_GE_MSG_STACK.ADD;
881 App_Exception.Raise_Exception;
882 END IF;
883 Check_Uniqueness;
884 Check_Constraints;
885 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
886 Check_Uniqueness;
887 Check_Constraints;
888 ELSIF (p_action = 'VALIDATE_DELETE') THEN
889 Null;
890 END IF;
891
892 END Before_DML;
893
894 PROCEDURE After_DML (
895 p_action IN VARCHAR2,
896 x_rowid IN VARCHAR2
897 ) IS
898 /*************************************************************
899 Created By : samaresh
900 Date Created By : 15-MAY-2000
901 Purpose :
902 Know limitations, enhancements or remarks
903 Change History
904 Who When What
905
906 (reverse chronological order - newest change first)
907 ***************************************************************/
908
909 BEGIN
910
911 l_rowid := x_rowid;
912
913 IF (p_action = 'INSERT') THEN
914 -- Call all the procedures related to After Insert.
915 Null;
916 ELSIF (p_action = 'UPDATE') THEN
917 -- Call all the procedures related to After Update.
918 Null;
919 ELSIF (p_action = 'DELETE') THEN
920 -- Call all the procedures related to After Delete.
921 Null;
922 END IF;
923
924 l_rowid:=NULL;
925 END After_DML;
926
927 procedure INSERT_ROW (
928 X_ROWID in out NOCOPY VARCHAR2,
929 x_CERTAINTY_OF_CHOICE_ID IN NUMBER,
930 x_RELIGION_CD IN VARCHAR2,
931 x_ADV_STUDIES_CLASSES IN NUMBER,
932 x_HONORS_CLASSES IN NUMBER,
933 x_CLASS_SIZE IN NUMBER,
934 x_SEC_SCHOOL_LOCATION_ID IN NUMBER,
935 x_PERCENT_PLAN_HIGHER_EDU IN NUMBER,
936 x_RECRUITMENT_ID IN OUT NOCOPY NUMBER,
937 x_PERSON_ID IN NUMBER,
938 x_SPECIAL_INTEREST_ID IN NUMBER,
939 x_PRIORITY IN VARCHAR2,
940 x_VIP IN VARCHAR2,
941 x_DEACTIVATE_RECRUIT_STATUS IN VARCHAR2,
942 x_PROGRAM_INTEREST_ID IN NUMBER,
943 x_INSTITUTION_SIZE_ID IN NUMBER,
944 x_INSTITUTION_CONTROL_ID IN NUMBER,
945 x_INSTITUTION_SETTING_ID IN NUMBER,
946 x_INSTITUTION_LOCATION_ID IN NUMBER,
947 x_SPECIAL_SERVICES_ID IN NUMBER,
948 x_EMPLOYMENT_ID IN NUMBER,
949 x_HOUSING_ID IN NUMBER,
950 x_DEGREE_GOAL_ID IN NUMBER,
951 x_UNIT_SET_ID IN NUMBER,
952 X_MODE in VARCHAR2-- default 'R'
953 ) AS
954 /*************************************************************
955 Created By : samaresh
956 Date Created By : 15-MAY-2000
957 Purpose :
958 Know limitations, enhancements or remarks
959 Change History
960 Who When What
961 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
962 Removed the processing for the Obsolete column 'PROBABILITY'
963 (reverse chronological order - newest change first)
964 ***************************************************************/
965
966 cursor C is select ROWID from IGS_AD_RECRUITMENTS
967 where RECRUITMENT_ID= X_RECRUITMENT_ID
968 ;
969 X_LAST_UPDATE_DATE DATE ;
970 X_LAST_UPDATED_BY NUMBER ;
971 X_LAST_UPDATE_LOGIN NUMBER ;
972 begin
973 X_LAST_UPDATE_DATE := SYSDATE;
974 if(X_MODE = 'I') then
975 X_LAST_UPDATED_BY := 1;
976 X_LAST_UPDATE_LOGIN := 0;
977 elsif (X_MODE = 'R') then
978 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
979 if X_LAST_UPDATED_BY is NULL then
980 X_LAST_UPDATED_BY := -1;
981 end if;
982 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
983 if X_LAST_UPDATE_LOGIN is NULL then
984 X_LAST_UPDATE_LOGIN := -1;
985 end if;
986 else
987 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
988 IGS_GE_MSG_STACK.ADD;
989 app_exception.raise_exception;
990 end if;
991
992 X_RECRUITMENT_ID := -1;
993 Before_DML(
994 p_action=>'INSERT',
995 x_rowid=>X_ROWID,
996 x_certainty_of_choice_id=>X_CERTAINTY_OF_CHOICE_ID,
997 x_religion_cd=>X_RELIGION_CD,
998 x_adv_studies_classes=>X_ADV_STUDIES_CLASSES,
999 x_honors_classes=>X_HONORS_CLASSES,
1000 x_class_size=>X_CLASS_SIZE,
1001 x_sec_school_location_id=>X_SEC_SCHOOL_LOCATION_ID,
1002 x_percent_plan_higher_edu=>X_PERCENT_PLAN_HIGHER_EDU,
1003 x_recruitment_id=>X_RECRUITMENT_ID,
1004 x_person_id=>X_PERSON_ID,
1005 x_special_interest_id=>X_SPECIAL_INTEREST_ID,
1006 x_priority=>X_PRIORITY,
1007 x_vip=>X_VIP,
1008 x_deactivate_recruit_status=>X_DEACTIVATE_RECRUIT_STATUS,
1009 x_program_interest_id=>X_PROGRAM_INTEREST_ID,
1010 x_institution_size_id=>X_INSTITUTION_SIZE_ID,
1011 x_institution_control_id=>X_INSTITUTION_CONTROL_ID,
1012 x_institution_setting_id=>X_INSTITUTION_SETTING_ID,
1013 x_institution_location_id=>X_INSTITUTION_LOCATION_ID,
1014 x_special_services_id=>X_SPECIAL_SERVICES_ID,
1015 x_employment_id=>X_EMPLOYMENT_ID,
1016 x_housing_id=>X_HOUSING_ID,
1017 x_degree_goal_id=>X_DEGREE_GOAL_ID,
1018 x_unit_set_id=>X_UNIT_SET_ID,
1019 x_creation_date=>X_LAST_UPDATE_DATE,
1020 x_created_by=>X_LAST_UPDATED_BY,
1021 x_last_update_date=>X_LAST_UPDATE_DATE,
1022 x_last_updated_by=>X_LAST_UPDATED_BY,
1023 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1024 insert into IGS_AD_RECRUITMENTS (
1025 CERTAINTY_OF_CHOICE_ID
1026 ,RELIGION_CD
1027 ,ADV_STUDIES_CLASSES
1028 ,HONORS_CLASSES
1029 ,CLASS_SIZE
1030 ,SEC_SCHOOL_LOCATION_ID
1031 ,PERCENT_PLAN_HIGHER_EDU
1032 ,RECRUITMENT_ID
1033 ,PERSON_ID
1034 ,SPECIAL_INTEREST_ID
1035 ,PRIORITY
1036 ,VIP
1037 ,DEACTIVATE_RECRUIT_STATUS
1038 ,PROGRAM_INTEREST_ID
1039 ,INSTITUTION_SIZE_ID
1040 ,INSTITUTION_CONTROL_ID
1041 ,INSTITUTION_SETTING_ID
1042 ,INSTITUTION_LOCATION_ID
1043 ,SPECIAL_SERVICES_ID
1044 ,EMPLOYMENT_ID
1045 ,HOUSING_ID
1046 ,DEGREE_GOAL_ID
1047 ,UNIT_SET_ID
1048 ,CREATION_DATE
1049 ,CREATED_BY
1050 ,LAST_UPDATE_DATE
1051 ,LAST_UPDATED_BY
1052 ,LAST_UPDATE_LOGIN
1053 ) values (
1054 NEW_REFERENCES.CERTAINTY_OF_CHOICE_ID
1055 ,NEW_REFERENCES.RELIGION_CD
1056 ,NEW_REFERENCES.ADV_STUDIES_CLASSES
1057 ,NEW_REFERENCES.HONORS_CLASSES
1058 ,NEW_REFERENCES.CLASS_SIZE
1059 ,NEW_REFERENCES.SEC_SCHOOL_LOCATION_ID
1060 ,NEW_REFERENCES.PERCENT_PLAN_HIGHER_EDU
1061 ,IGS_AD_RECRUITMENT_S.NEXTVAL
1062 ,NEW_REFERENCES.PERSON_ID
1063 ,NEW_REFERENCES.SPECIAL_INTEREST_ID
1064 ,NEW_REFERENCES.PRIORITY
1065 ,NEW_REFERENCES.VIP
1066 ,NEW_REFERENCES.DEACTIVATE_RECRUIT_STATUS
1067 ,NEW_REFERENCES.PROGRAM_INTEREST_ID
1068 ,NEW_REFERENCES.INSTITUTION_SIZE_ID
1069 ,NEW_REFERENCES.INSTITUTION_CONTROL_ID
1070 ,NEW_REFERENCES.INSTITUTION_SETTING_ID
1071 ,NEW_REFERENCES.INSTITUTION_LOCATION_ID
1072 ,NEW_REFERENCES.SPECIAL_SERVICES_ID
1073 ,NEW_REFERENCES.EMPLOYMENT_ID
1074 ,NEW_REFERENCES.HOUSING_ID
1075 ,NEW_REFERENCES.DEGREE_GOAL_ID
1076 ,NEW_REFERENCES.UNIT_SET_ID
1077 ,X_LAST_UPDATE_DATE
1078 ,X_LAST_UPDATED_BY
1079 ,X_LAST_UPDATE_DATE
1080 ,X_LAST_UPDATED_BY
1081 ,X_LAST_UPDATE_LOGIN )
1082 RETURNING RECRUITMENT_ID INTO X_RECRUITMENT_ID ;
1083 open c;
1084 fetch c into X_ROWID;
1085 if (c%notfound) then
1086 close c;
1087 raise no_data_found;
1088 end if;
1089 close c;
1090 After_DML (
1091 p_action => 'INSERT' ,
1092 x_rowid => X_ROWID );
1093 end INSERT_ROW;
1094 procedure LOCK_ROW (
1095 X_ROWID in VARCHAR2,
1096 x_CERTAINTY_OF_CHOICE_ID IN NUMBER,
1097 x_RELIGION_CD IN VARCHAR2,
1098 x_ADV_STUDIES_CLASSES IN NUMBER,
1099 x_HONORS_CLASSES IN NUMBER,
1100 x_CLASS_SIZE IN NUMBER,
1101 x_SEC_SCHOOL_LOCATION_ID IN NUMBER,
1102 x_PERCENT_PLAN_HIGHER_EDU IN NUMBER,
1103 x_RECRUITMENT_ID IN NUMBER,
1104 x_PERSON_ID IN NUMBER,
1105 x_SPECIAL_INTEREST_ID IN NUMBER,
1106 x_PRIORITY IN VARCHAR2,
1107 x_VIP IN VARCHAR2,
1108 x_DEACTIVATE_RECRUIT_STATUS IN VARCHAR2,
1109 x_PROGRAM_INTEREST_ID IN NUMBER,
1110 x_INSTITUTION_SIZE_ID IN NUMBER,
1111 x_INSTITUTION_CONTROL_ID IN NUMBER,
1112 x_INSTITUTION_SETTING_ID IN NUMBER,
1113 x_INSTITUTION_LOCATION_ID IN NUMBER,
1114 x_SPECIAL_SERVICES_ID IN NUMBER,
1115 x_EMPLOYMENT_ID IN NUMBER,
1116 x_HOUSING_ID IN NUMBER,
1117 x_DEGREE_GOAL_ID IN NUMBER,
1118 x_UNIT_SET_ID IN NUMBER ) AS
1119 /*************************************************************
1120 Created By : samaresh
1121 Date Created By : 15-MAY-2000
1122 Purpose :
1123 Know limitations, enhancements or remarks
1124 Change History
1125 Who When What
1126 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
1127 Removed the processing for the Obsolete column 'PROBABILITY'
1128 (reverse chronological order - newest change first)
1129 ***************************************************************/
1130
1131 cursor c1 is select
1132 CERTAINTY_OF_CHOICE_ID
1133 , RELIGION_CD
1134 , ADV_STUDIES_CLASSES
1135 , HONORS_CLASSES
1136 , CLASS_SIZE
1137 , SEC_SCHOOL_LOCATION_ID
1138 , PERCENT_PLAN_HIGHER_EDU
1139 , PERSON_ID
1140 , SPECIAL_INTEREST_ID
1141 , PRIORITY
1142 , VIP
1143 , DEACTIVATE_RECRUIT_STATUS
1144 , PROGRAM_INTEREST_ID
1145 , INSTITUTION_SIZE_ID
1146 , INSTITUTION_CONTROL_ID
1147 , INSTITUTION_SETTING_ID
1148 , INSTITUTION_LOCATION_ID
1149 , SPECIAL_SERVICES_ID
1150 , EMPLOYMENT_ID
1151 , HOUSING_ID
1152 , DEGREE_GOAL_ID
1153 , UNIT_SET_ID
1154 from IGS_AD_RECRUITMENTS
1155 where ROWID = X_ROWID
1156 for update nowait;
1157 tlinfo c1%rowtype;
1158 begin
1159 open c1;
1160 fetch c1 into tlinfo;
1161 if (c1%notfound) then
1162 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1163 IGS_GE_MSG_STACK.ADD;
1164 close c1;
1165 app_exception.raise_exception;
1166 return;
1167 end if;
1168 close c1;
1169 if ( ( (tlinfo.CERTAINTY_OF_CHOICE_ID = X_CERTAINTY_OF_CHOICE_ID)
1170 OR ((tlinfo.CERTAINTY_OF_CHOICE_ID is null)
1171 AND (X_CERTAINTY_OF_CHOICE_ID is null)))
1172 AND ((tlinfo.RELIGION_CD = X_RELIGION_CD)
1173 OR ((tlinfo.RELIGION_CD is null)
1174 AND (X_RELIGION_CD is null)))
1175 AND ((tlinfo.ADV_STUDIES_CLASSES = X_ADV_STUDIES_CLASSES)
1176 OR ((tlinfo.ADV_STUDIES_CLASSES is null)
1177 AND (X_ADV_STUDIES_CLASSES is null)))
1178 AND ((tlinfo.HONORS_CLASSES = X_HONORS_CLASSES)
1179 OR ((tlinfo.HONORS_CLASSES is null)
1180 AND (X_HONORS_CLASSES is null)))
1181 AND ((tlinfo.CLASS_SIZE = X_CLASS_SIZE)
1182 OR ((tlinfo.CLASS_SIZE is null)
1183 AND (X_CLASS_SIZE is null)))
1184 AND ((tlinfo.SEC_SCHOOL_LOCATION_ID = X_SEC_SCHOOL_LOCATION_ID)
1185 OR ((tlinfo.SEC_SCHOOL_LOCATION_ID is null)
1186 AND (X_SEC_SCHOOL_LOCATION_ID is null)))
1187 AND ((tlinfo.PERCENT_PLAN_HIGHER_EDU = X_PERCENT_PLAN_HIGHER_EDU)
1188 OR ((tlinfo.PERCENT_PLAN_HIGHER_EDU is null)
1189 AND (X_PERCENT_PLAN_HIGHER_EDU is null)))
1190 AND (tlinfo.PERSON_ID = X_PERSON_ID)
1191 AND ((tlinfo.SPECIAL_INTEREST_ID = X_SPECIAL_INTEREST_ID)
1192 OR ((tlinfo.SPECIAL_INTEREST_ID is null)
1193 AND (X_SPECIAL_INTEREST_ID is null)))
1194 AND ((tlinfo.PRIORITY = X_PRIORITY)
1195 OR ((tlinfo.PRIORITY is null)
1196 AND (X_PRIORITY is null)))
1197 AND ((tlinfo.VIP = X_VIP)
1198 OR ((tlinfo.VIP is null)
1199 AND (X_VIP is null)))
1200 AND ((tlinfo.DEACTIVATE_RECRUIT_STATUS = X_DEACTIVATE_RECRUIT_STATUS)
1201 OR ((tlinfo.DEACTIVATE_RECRUIT_STATUS is null)
1202 AND (X_DEACTIVATE_RECRUIT_STATUS is null)))
1203 AND ((tlinfo.PROGRAM_INTEREST_ID = X_PROGRAM_INTEREST_ID)
1204 OR ((tlinfo.PROGRAM_INTEREST_ID is null)
1205 AND (X_PROGRAM_INTEREST_ID is null)))
1206 AND ((tlinfo.INSTITUTION_SIZE_ID = X_INSTITUTION_SIZE_ID)
1207 OR ((tlinfo.INSTITUTION_SIZE_ID is null)
1208 AND (X_INSTITUTION_SIZE_ID is null)))
1209 AND ((tlinfo.INSTITUTION_CONTROL_ID = X_INSTITUTION_CONTROL_ID)
1210 OR ((tlinfo.INSTITUTION_CONTROL_ID is null)
1211 AND (X_INSTITUTION_CONTROL_ID is null)))
1212 AND ((tlinfo.INSTITUTION_SETTING_ID = X_INSTITUTION_SETTING_ID)
1213 OR ((tlinfo.INSTITUTION_SETTING_ID is null)
1214 AND (X_INSTITUTION_SETTING_ID is null)))
1215 AND ((tlinfo.INSTITUTION_LOCATION_ID = X_INSTITUTION_LOCATION_ID)
1216 OR ((tlinfo.INSTITUTION_LOCATION_ID is null)
1217 AND (X_INSTITUTION_LOCATION_ID is null)))
1218 AND ((tlinfo.SPECIAL_SERVICES_ID = X_SPECIAL_SERVICES_ID)
1219 OR ((tlinfo.SPECIAL_SERVICES_ID is null)
1220 AND (X_SPECIAL_SERVICES_ID is null)))
1221 AND ((tlinfo.EMPLOYMENT_ID = X_EMPLOYMENT_ID)
1222 OR ((tlinfo.EMPLOYMENT_ID is null)
1223 AND (X_EMPLOYMENT_ID is null)))
1224 AND ((tlinfo.HOUSING_ID = X_HOUSING_ID)
1225 OR ((tlinfo.HOUSING_ID is null)
1226 AND (X_HOUSING_ID is null)))
1227 AND ((tlinfo.DEGREE_GOAL_ID = X_DEGREE_GOAL_ID)
1228 OR ((tlinfo.DEGREE_GOAL_ID is null)
1229 AND (X_DEGREE_GOAL_ID is null)))
1230 AND ((tlinfo.UNIT_SET_ID = X_UNIT_SET_ID)
1231 OR ((tlinfo.UNIT_SET_ID is null)
1232 AND (X_UNIT_SET_ID is null)))
1233 ) then
1234 null;
1235 else
1236 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1237 IGS_GE_MSG_STACK.ADD;
1238 app_exception.raise_exception;
1239 end if;
1240 return;
1241 end LOCK_ROW;
1242 Procedure UPDATE_ROW (
1243 X_ROWID in VARCHAR2,
1244 x_CERTAINTY_OF_CHOICE_ID IN NUMBER,
1245 x_RELIGION_CD IN VARCHAR2,
1246 x_ADV_STUDIES_CLASSES IN NUMBER,
1247 x_HONORS_CLASSES IN NUMBER,
1248 x_CLASS_SIZE IN NUMBER,
1249 x_SEC_SCHOOL_LOCATION_ID IN NUMBER,
1250 x_PERCENT_PLAN_HIGHER_EDU IN NUMBER,
1251 x_RECRUITMENT_ID IN NUMBER,
1252 x_PERSON_ID IN NUMBER,
1253 x_SPECIAL_INTEREST_ID IN NUMBER,
1254 x_PRIORITY IN VARCHAR2,
1255 x_VIP IN VARCHAR2,
1256 x_DEACTIVATE_RECRUIT_STATUS IN VARCHAR2,
1257 x_PROGRAM_INTEREST_ID IN NUMBER,
1258 x_INSTITUTION_SIZE_ID IN NUMBER,
1259 x_INSTITUTION_CONTROL_ID IN NUMBER,
1260 x_INSTITUTION_SETTING_ID IN NUMBER,
1261 x_INSTITUTION_LOCATION_ID IN NUMBER,
1262 x_SPECIAL_SERVICES_ID IN NUMBER,
1263 x_EMPLOYMENT_ID IN NUMBER,
1264 x_HOUSING_ID IN NUMBER,
1265 x_DEGREE_GOAL_ID IN NUMBER,
1266 x_UNIT_SET_ID IN NUMBER,
1267 X_MODE in VARCHAR2 --default 'R'
1268 ) AS
1269 /*************************************************************
1270 Created By : samaresh
1271 Date Created By : 15-MAY-2000
1272 Purpose :
1273 Know limitations, enhancements or remarks
1274 Change History
1275 Who When What
1276 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
1277 Removed the processing for the Obsolete column 'PROBABILITY'
1278 (reverse chronological order - newest change first)
1279 ***************************************************************/
1280
1281 X_LAST_UPDATE_DATE DATE ;
1282 X_LAST_UPDATED_BY NUMBER ;
1283 X_LAST_UPDATE_LOGIN NUMBER ;
1284 begin
1285 X_LAST_UPDATE_DATE := SYSDATE;
1286 if(X_MODE = 'I') then
1287 X_LAST_UPDATED_BY := 1;
1288 X_LAST_UPDATE_LOGIN := 0;
1289 elsif (X_MODE = 'R') then
1290 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1291 if X_LAST_UPDATED_BY is NULL then
1292 X_LAST_UPDATED_BY := -1;
1293 end if;
1294 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1295 if X_LAST_UPDATE_LOGIN is NULL then
1296 X_LAST_UPDATE_LOGIN := -1;
1297 end if;
1298 else
1299 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1300 IGS_GE_MSG_STACK.ADD;
1301 app_exception.raise_exception;
1302 end if;
1303 Before_DML(
1304 p_action=>'UPDATE',
1305 x_rowid=>X_ROWID,
1306 x_certainty_of_choice_id=>X_CERTAINTY_OF_CHOICE_ID,
1307 x_religion_cd=>X_RELIGION_CD,
1308 x_adv_studies_classes=>X_ADV_STUDIES_CLASSES,
1309 x_honors_classes=>X_HONORS_CLASSES,
1310 x_class_size=>X_CLASS_SIZE,
1311 x_sec_school_location_id=>X_SEC_SCHOOL_LOCATION_ID,
1312 x_percent_plan_higher_edu=>X_PERCENT_PLAN_HIGHER_EDU,
1313 x_recruitment_id=>X_RECRUITMENT_ID,
1314 x_person_id=>X_PERSON_ID,
1315 x_special_interest_id=>X_SPECIAL_INTEREST_ID,
1316 x_priority=>X_PRIORITY,
1317 x_vip=>X_VIP,
1318 x_deactivate_recruit_status=>X_DEACTIVATE_RECRUIT_STATUS,
1319 x_program_interest_id=>X_PROGRAM_INTEREST_ID,
1320 x_institution_size_id=>X_INSTITUTION_SIZE_ID,
1321 x_institution_control_id=>X_INSTITUTION_CONTROL_ID,
1322 x_institution_setting_id=>X_INSTITUTION_SETTING_ID,
1323 x_institution_location_id=>X_INSTITUTION_LOCATION_ID,
1324 x_special_services_id=>X_SPECIAL_SERVICES_ID,
1325 x_employment_id=>X_EMPLOYMENT_ID,
1326 x_housing_id=>X_HOUSING_ID,
1327 x_degree_goal_id=>X_DEGREE_GOAL_ID,
1328 x_unit_set_id=>X_UNIT_SET_ID,
1329 x_creation_date=>X_LAST_UPDATE_DATE,
1330 x_created_by=>X_LAST_UPDATED_BY,
1331 x_last_update_date=>X_LAST_UPDATE_DATE,
1332 x_last_updated_by=>X_LAST_UPDATED_BY,
1333 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1334 update IGS_AD_RECRUITMENTS set
1335 CERTAINTY_OF_CHOICE_ID = NEW_REFERENCES.CERTAINTY_OF_CHOICE_ID,
1336 RELIGION_CD = NEW_REFERENCES.RELIGION_CD,
1337 ADV_STUDIES_CLASSES = NEW_REFERENCES.ADV_STUDIES_CLASSES,
1338 HONORS_CLASSES = NEW_REFERENCES.HONORS_CLASSES,
1339 CLASS_SIZE = NEW_REFERENCES.CLASS_SIZE,
1340 SEC_SCHOOL_LOCATION_ID = NEW_REFERENCES.SEC_SCHOOL_LOCATION_ID,
1341 PERCENT_PLAN_HIGHER_EDU = NEW_REFERENCES.PERCENT_PLAN_HIGHER_EDU,
1342 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1343 SPECIAL_INTEREST_ID = NEW_REFERENCES.SPECIAL_INTEREST_ID,
1344 PRIORITY = NEW_REFERENCES.PRIORITY,
1345 VIP = NEW_REFERENCES.VIP,
1346 DEACTIVATE_RECRUIT_STATUS = NEW_REFERENCES.DEACTIVATE_RECRUIT_STATUS,
1347 PROGRAM_INTEREST_ID = NEW_REFERENCES.PROGRAM_INTEREST_ID,
1348 INSTITUTION_SIZE_ID = NEW_REFERENCES.INSTITUTION_SIZE_ID,
1349 INSTITUTION_CONTROL_ID = NEW_REFERENCES.INSTITUTION_CONTROL_ID,
1350 INSTITUTION_SETTING_ID = NEW_REFERENCES.INSTITUTION_SETTING_ID,
1351 INSTITUTION_LOCATION_ID = NEW_REFERENCES.INSTITUTION_LOCATION_ID,
1352 SPECIAL_SERVICES_ID = NEW_REFERENCES.SPECIAL_SERVICES_ID,
1353 EMPLOYMENT_ID = NEW_REFERENCES.EMPLOYMENT_ID,
1354 HOUSING_ID = NEW_REFERENCES.HOUSING_ID,
1355 DEGREE_GOAL_ID = NEW_REFERENCES.DEGREE_GOAL_ID,
1356 UNIT_SET_ID = NEW_REFERENCES.UNIT_SET_ID,
1357 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1358 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1359 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1360 where ROWID = X_ROWID;
1361 if (sql%notfound) then
1362 raise no_data_found;
1363 end if;
1364
1365 After_DML (
1366 p_action => 'UPDATE' ,
1367 x_rowid => X_ROWID
1368 );
1369 end UPDATE_ROW;
1370 procedure ADD_ROW (
1371 X_ROWID in out NOCOPY VARCHAR2,
1372 x_CERTAINTY_OF_CHOICE_ID IN NUMBER,
1373 x_RELIGION_CD IN VARCHAR2,
1374 x_ADV_STUDIES_CLASSES IN NUMBER,
1375 x_HONORS_CLASSES IN NUMBER,
1376 x_CLASS_SIZE IN NUMBER,
1377 x_SEC_SCHOOL_LOCATION_ID IN NUMBER,
1378 x_PERCENT_PLAN_HIGHER_EDU IN NUMBER,
1379 x_RECRUITMENT_ID IN OUT NOCOPY NUMBER,
1380 x_PERSON_ID IN NUMBER,
1381 x_SPECIAL_INTEREST_ID IN NUMBER,
1382 x_PRIORITY IN VARCHAR2,
1383 x_VIP IN VARCHAR2,
1384 x_DEACTIVATE_RECRUIT_STATUS IN VARCHAR2,
1385 x_PROGRAM_INTEREST_ID IN NUMBER,
1386 x_INSTITUTION_SIZE_ID IN NUMBER,
1387 x_INSTITUTION_CONTROL_ID IN NUMBER,
1388 x_INSTITUTION_SETTING_ID IN NUMBER,
1389 x_INSTITUTION_LOCATION_ID IN NUMBER,
1390 x_SPECIAL_SERVICES_ID IN NUMBER,
1391 x_EMPLOYMENT_ID IN NUMBER,
1392 x_HOUSING_ID IN NUMBER,
1393 x_DEGREE_GOAL_ID IN NUMBER,
1394 x_UNIT_SET_ID IN NUMBER,
1395 X_MODE in VARCHAR2 --default 'R'
1396 ) AS
1397 /*************************************************************
1398 Created By : samaresh
1399 Date Created By : 15-MAY-2000
1400 Purpose :
1401 Know limitations, enhancements or remarks
1402 Change History
1403 Who When What
1404 pkpatel 24_JUL-2001 Bug no.1890270 Admissions Standards and Rules Dld_adsr_setup
1405 Removed the processing for the Obsolete column 'PROBABILITY'
1406
1407 (reverse chronological order - newest change first)
1408 ***************************************************************/
1409
1410 cursor c1 is select ROWID from IGS_AD_RECRUITMENTS
1411 where RECRUITMENT_ID= X_RECRUITMENT_ID
1412 ;
1413 begin
1414 open c1;
1415 fetch c1 into X_ROWID;
1416 if (c1%notfound) then
1417 close c1;
1418 INSERT_ROW (
1419 X_ROWID,
1420 X_CERTAINTY_OF_CHOICE_ID,
1421 X_RELIGION_CD,
1422 X_ADV_STUDIES_CLASSES,
1423 X_HONORS_CLASSES,
1424 X_CLASS_SIZE,
1425 X_SEC_SCHOOL_LOCATION_ID,
1426 X_PERCENT_PLAN_HIGHER_EDU,
1427 X_RECRUITMENT_ID,
1428 X_PERSON_ID,
1429 X_SPECIAL_INTEREST_ID,
1430 X_PRIORITY,
1431 X_VIP,
1432 X_DEACTIVATE_RECRUIT_STATUS,
1433 X_PROGRAM_INTEREST_ID,
1434 X_INSTITUTION_SIZE_ID,
1435 X_INSTITUTION_CONTROL_ID,
1436 X_INSTITUTION_SETTING_ID,
1437 X_INSTITUTION_LOCATION_ID,
1438 X_SPECIAL_SERVICES_ID,
1439 X_EMPLOYMENT_ID,
1440 X_HOUSING_ID,
1441 X_DEGREE_GOAL_ID,
1442 X_UNIT_SET_ID,
1443 X_MODE );
1444 return;
1445 end if;
1446 close c1;
1447 UPDATE_ROW (
1448 X_ROWID,
1449 X_CERTAINTY_OF_CHOICE_ID,
1450 X_RELIGION_CD,
1451 X_ADV_STUDIES_CLASSES,
1452 X_HONORS_CLASSES,
1453 X_CLASS_SIZE,
1454 X_SEC_SCHOOL_LOCATION_ID,
1455 X_PERCENT_PLAN_HIGHER_EDU,
1456 X_RECRUITMENT_ID,
1457 X_PERSON_ID,
1458 X_SPECIAL_INTEREST_ID,
1459 X_PRIORITY,
1460 X_VIP,
1461 X_DEACTIVATE_RECRUIT_STATUS,
1462 X_PROGRAM_INTEREST_ID,
1463 X_INSTITUTION_SIZE_ID,
1464 X_INSTITUTION_CONTROL_ID,
1465 X_INSTITUTION_SETTING_ID,
1466 X_INSTITUTION_LOCATION_ID,
1467 X_SPECIAL_SERVICES_ID,
1468 X_EMPLOYMENT_ID,
1469 X_HOUSING_ID,
1470 X_DEGREE_GOAL_ID,
1471 X_UNIT_SET_ID,
1472 X_MODE );
1473 end ADD_ROW;
1474 procedure DELETE_ROW (
1475 X_ROWID in VARCHAR2
1476 ) AS
1477 /*************************************************************
1478 Created By : samaresh
1479 Date Created By : 15-MAY-2000
1480 Purpose :
1481 Know limitations, enhancements or remarks
1482 Change History
1483 Who When What
1484
1485 (reverse chronological order - newest change first)
1486 ***************************************************************/
1487
1488 begin
1489 Before_DML (
1490 p_action => 'DELETE',
1491 x_rowid => X_ROWID
1492 );
1493 delete from IGS_AD_RECRUITMENTS
1494 where ROWID = X_ROWID;
1495 if (sql%notfound) then
1496 raise no_data_found;
1497 end if;
1498 After_DML (
1499 p_action => 'DELETE',
1500 x_rowid => X_ROWID
1501 );
1502 end DELETE_ROW;
1503 END igs_ad_recruitments_pkg;
[Home] [Help]