[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_STAT_DETAILS_PKG
Source
1 PACKAGE BODY Igs_Pe_Stat_Details_Pkg AS
2 /* $Header: IGSNI72B.pls 120.2 2006/02/17 06:52:23 gmaheswa ship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 -- npalanis 11-SEP-2002 bug - 2608360
7 -- igs_pe_code_classes is
8 -- removed due to transition of code
9 -- class to lookups , new columns added
10 -- for codes. the tbh are modified accordingly
11 --
12 -- Bug ID : 2000408
13 -- who when what
14 -- CDCRUZ Sep 24,2002 New Col's added for
15 -- Person DLD
16 -- Columns Obsoleted - CRIMINAL_CONVICT/ACAD_DISMISSAL/NON_ACAD_DISMISSAL/COUNTRY_CD3
17 -- RES_STAT_ID/STATE_OF_RESIDENCE
18 -- Columns Added - MATR_CAL_TYPE/MATR_SEQUENCE_NUMBER/INIT_CAL_TYPE/INIT_SEQUENCE_NUMBER
19 -- RECENT_CAL_TYPE/RECENT_SEQUENCE_NUMBER/CATALOG_CAL_TYPE/CATALOG_SEQUENCE_NUMBER
20 -- Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
21 -- ssawhney 2203778
22 -- added person_id mandatory FK with HZ_PARTIES. removed person_profile_id and all the obsoleted columns
23 ------------------------------------------------------------------
24
25 l_rowid VARCHAR2(25);
26 old_references igs_pe_stat_details%ROWTYPE;
27 new_references igs_pe_stat_details%ROWTYPE;
28
29 PROCEDURE set_column_values (
30 p_action IN VARCHAR2,
31 x_rowid IN VARCHAR2 ,-- DEFAULT NULL,
32 -- x_person_profile_id IN NUMBER DEFAULT NULL,
33 x_person_id IN NUMBER ,--DEFAULT NULL,
34 x_effective_start_date IN DATE ,-- DEFAULT NULL,
35 x_effective_end_date IN DATE ,-- DEFAULT NULL,
36 x_religion_cd IN VARCHAR2 ,-- DEFAULT NULL,
37 --x_criminal_convict IN VARCHAR2 DEFAULT NULL,
38 --x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
39 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
40 --x_country_cd3 IN VARCHAR2 DEFAULT NULL,
41 --x_state_of_residence IN VARCHAR2 DEFAULT NULL,
42 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
43 x_socio_eco_cd IN VARCHAR2 ,-- DEFAULT NULL,
44 x_next_to_kin IN VARCHAR2 ,-- DEFAULT NULL,
45 x_in_state_tuition IN VARCHAR2 ,-- DEFAULT NULL,
46 x_tuition_st_date IN DATE ,-- DEFAULT NULL,
47 x_tuition_end_date IN DATE ,-- DEFAULT NULL,
48 x_further_education_cd IN VARCHAR2 ,-- DEFAULT NULL,
49 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
50 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
51 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
52 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
53 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
54 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
55 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
56 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
57 x_creation_date IN DATE ,-- DEFAULT NULL,
58 x_created_by IN NUMBER ,-- DEFAULT NULL,
59 x_last_update_date IN DATE ,-- DEFAULT NULL,
60 x_last_updated_by IN NUMBER ,-- DEFAULT NULL,
61 x_last_update_login IN NUMBER ,-- DEFAULT NULL ,
62 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
63 X_ATTRIBUTE1 IN VARCHAR2 ,--DEFAULT NULL,
64 X_ATTRIBUTE2 IN VARCHAR2 ,--DEFAULT NULL,
65 X_ATTRIBUTE3 IN VARCHAR2 ,--DEFAULT NULL,
66 X_ATTRIBUTE4 IN VARCHAR2 ,--DEFAULT NULL,
67 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
68 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
69 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
70 X_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
71 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
72 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
73 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
74 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
75 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
76 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
77 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
78 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
79 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
80 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
81 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
82 X_ATTRIBUTE20 IN VARCHAR2, --DEFAULT NULL
83 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 --DEFAULT NULL
84 ) AS
85 /*
86 || Created By : bshankar
87 || Created On : 28-AUG-2000
88 || Purpose : Initialises the Old and New references for the columns of the table.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94
95 CURSOR cur_old_ref_values IS
96 SELECT *
97 FROM IGS_PE_STAT_DETAILS
98 WHERE ROWID = x_rowid;
99
100 BEGIN
101
102 l_rowid := x_rowid;
103
104 -- Code for setting the Old and New Reference Values.
105 -- Populate Old Values.
106 OPEN cur_old_ref_values;
107 FETCH cur_old_ref_values INTO old_references;
108 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
109 CLOSE cur_old_ref_values;
110 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED 1');
111 igs_ge_msg_stack.ADD;
112 app_exception.raise_exception;
113 RETURN;
114 END IF;
115 CLOSE cur_old_ref_values;
116
117 -- Populate New Values.
118 -- new_references.person_profile_id := x_person_profile_id;
119 new_references.person_id := x_person_id;
120 new_references.effective_start_date := x_effective_start_date;
121 new_references.effective_end_date := x_effective_end_date;
122 new_references.religion_cd := x_religion_cd;
123 -- new_references.criminal_convict := x_criminal_convict;
124 -- new_references.acad_dismissal := x_acad_dismissal;
125 -- new_references.non_acad_dismissal := x_non_acad_dismissal;
126 -- new_references.country_cd3 := x_country_cd3;
127 -- new_references.state_of_residence := x_state_of_residence;
128 -- new_references.resid_stat_id := x_resid_stat_id;
129 new_references.socio_eco_cd := x_socio_eco_cd;
130 new_references.next_to_kin := x_next_to_kin;
131 new_references.in_state_tuition := x_in_state_tuition;
132 new_references.tuition_st_date := x_tuition_st_date;
133 new_references.tuition_end_date := x_tuition_end_date;
134 new_references.further_education_cd := x_further_education_cd;
135 NEW_REFERENCES.MATR_CAL_TYPE := X_MATR_CAL_TYPE ;
136 NEW_REFERENCES.MATR_SEQUENCE_NUMBER := X_MATR_SEQUENCE_NUMBER ;
137 NEW_REFERENCES.INIT_CAL_TYPE := X_INIT_CAL_TYPE ;
138 NEW_REFERENCES.INIT_SEQUENCE_NUMBER := X_INIT_SEQUENCE_NUMBER ;
139 NEW_REFERENCES.RECENT_CAL_TYPE := X_RECENT_CAL_TYPE ;
140 NEW_REFERENCES.RECENT_SEQUENCE_NUMBER := X_RECENT_SEQUENCE_NUMBER ;
141 NEW_REFERENCES.CATALOG_CAL_TYPE := X_CATALOG_CAL_TYPE ;
142 NEW_REFERENCES.CATALOG_SEQUENCE_NUMBER := X_CATALOG_SEQUENCE_NUMBER ;
143 new_references.attribute_category := x_attribute_category;
144 new_references.attribute1 := x_attribute1;
145 new_references.attribute2 := x_attribute2;
146 new_references.attribute3 := x_attribute3;
147 new_references.attribute4 := x_attribute4;
148 new_references.attribute5 := x_attribute5;
149 new_references.attribute6 := x_attribute6;
150 new_references.attribute7 := x_attribute7;
151 new_references.attribute8 := x_attribute8;
152 new_references.attribute9 := x_attribute9;
153 new_references.attribute10 := x_attribute10;
154 new_references.attribute11 := x_attribute11;
155 new_references.attribute12 := x_attribute12;
156 new_references.attribute13 := x_attribute13;
157 new_references.attribute14 := x_attribute14;
158 new_references.attribute15 := x_attribute15;
159 new_references.attribute16 := x_attribute16;
160 new_references.attribute17 := x_attribute17;
161 new_references.attribute18 := x_attribute18;
162 new_references.attribute19 := x_attribute19;
163 new_references.attribute20 := x_attribute20;
164
165
166 IF (p_action = 'UPDATE') THEN
167 new_references.creation_date := old_references.creation_date;
168 new_references.created_by := old_references.created_by;
169 ELSE
170 new_references.creation_date := x_creation_date;
171 new_references.created_by := x_created_by;
172 END IF;
173
174 new_references.last_update_date := x_last_update_date;
175 new_references.last_updated_by := x_last_updated_by;
176 new_references.last_update_login := x_last_update_login;
177 new_references.birth_cntry_resn_code := x_birth_cntry_resn_code;
178
179 END set_column_values;
180
181
182 FUNCTION get_pk_for_validation (
183 -- x_person_profile_id IN NUMBER
184 x_person_id IN NUMBER
185 ) RETURN BOOLEAN AS
186 /*
187 || Created By : bshankar
188 || Created On : 28-AUG-2000
189 || Purpose : Validates the Primary Key of the table.
190 || Known limitations, enhancements or remarks :
191 || Change History :
192 || Who When What
193 || (reverse chronological order - newest change first)
194 */
195 CURSOR cur_rowid IS
196 SELECT ROWID
197 FROM igs_pe_stat_details
198 -- WHERE person_profile_id = x_person_profile_id
199 WHERE person_id = x_person_id
200 FOR UPDATE NOWAIT;
201
202 lv_rowid cur_rowid%ROWTYPE;
203
204 BEGIN
205
206 OPEN cur_rowid;
207 FETCH cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 CLOSE cur_rowid;
210 RETURN(TRUE);
211 ELSE
212 CLOSE cur_rowid;
213 RETURN(FALSE);
214 END IF;
215
216 END get_pk_for_validation;
217
218 PROCEDURE GET_FK_IGS_CA_INST (
219 x_cal_type IN VARCHAR2,
220 x_ci_sequence_number IN NUMBER
221 ) AS
222 CURSOR cur_rowid IS
223 SELECT rowid
224 FROM IGS_PE_STAT_DETAILS
225 WHERE (
226 ( MATR_CAL_TYPE = X_CAL_TYPE AND MATR_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER ) OR
227 ( INIT_CAL_TYPE = X_CAL_TYPE AND INIT_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER ) OR
228 ( RECENT_CAL_TYPE = X_CAL_TYPE AND RECENT_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER ) OR
229 ( CATALOG_CAL_TYPE = X_CAL_TYPE AND CATALOG_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER )
230 );
231
232 lv_rowid cur_rowid%RowType;
233 BEGIN
234 Open cur_rowid;
235 Fetch cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PST_CI_FK');
238 Igs_Ge_Msg_Stack.Add;
239 Close cur_rowid;
240 App_Exception.Raise_Exception;
241 Return;
242 END IF;
243 Close cur_rowid;
244 END GET_FK_IGS_CA_INST;
245
246 PROCEDURE Check_Parent_Existance AS
247 CURSOR cur_rowid IS
248 SELECT ROWID
249 FROM HZ_PARTIES
250 WHERE PARTY_ID = new_references.PERSON_ID ;
251 lv_rowid cur_rowid%ROWTYPE;
252 /*
253 || Created By : bshankar
254 || Created On : 28-AUG-2000
255 || Purpose : Checks for parent record existance.
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || (reverse chronological order - newest change first)
260 || ssawhney 6feb 2203778 person_profile_id is remvoed and person_id PK now
261 */
262 BEGIN
263
264 --IF (((old_references.PERSON_PROFILE_ID = new_references.PERSON_PROFILE_ID)) OR
265 -- ((new_references.PERSON_PROFILE_ID IS NULL))) THEN
266
267 --NULL;
268 IF (((old_references.PERSON_ID = new_references.PERSON_ID)) OR
269 ((new_references.PERSON_ID IS NULL))) THEN
270 NULL;
271 ELSE
272
273 OPEN cur_rowid;
274 FETCH cur_rowid INTO lv_rowid;
275 IF (cur_rowid%NOTFOUND) THEN
276 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED 2');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280 CLOSE cur_rowid;
281
282 END IF;
283
284 IF (((old_references.MATR_CAL_TYPE = new_references.MATR_CAL_TYPE) AND
285 (old_references.MATR_SEQUENCE_NUMBER = new_references.MATR_SEQUENCE_NUMBER)) OR
286 ((new_references.MATR_CAL_TYPE IS NULL) OR
287 (new_references.MATR_SEQUENCE_NUMBER IS NULL))) THEN
288 NULL;
289 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
290 new_references.MATR_CAL_TYPE,
291 new_references.MATR_SEQUENCE_NUMBER
292 ) THEN
293 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED 3');
294 igs_ge_msg_stack.add;
295 app_exception.raise_exception;
296 END IF;
297
298 IF (((old_references.INIT_CAL_TYPE = new_references.INIT_CAL_TYPE) AND
299 (old_references.INIT_SEQUENCE_NUMBER = new_references.INIT_SEQUENCE_NUMBER)) OR
300 ((new_references.INIT_CAL_TYPE IS NULL) OR
301 (new_references.INIT_SEQUENCE_NUMBER IS NULL))) THEN
302 NULL;
303 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
304 new_references.INIT_CAL_TYPE,
305 new_references.INIT_SEQUENCE_NUMBER
306 ) THEN
307 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED 4');
308 igs_ge_msg_stack.add;
309 app_exception.raise_exception;
310 END IF;
311
312 IF (((old_references.RECENT_CAL_TYPE = new_references.RECENT_CAL_TYPE) AND
313 (old_references.RECENT_SEQUENCE_NUMBER = new_references.RECENT_SEQUENCE_NUMBER)) OR
314 ((new_references.RECENT_CAL_TYPE IS NULL) OR
315 (new_references.RECENT_SEQUENCE_NUMBER IS NULL))) THEN
316 NULL;
317 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
318 new_references.RECENT_CAL_TYPE,
319 new_references.RECENT_SEQUENCE_NUMBER
320 ) THEN
321 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED 5');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 END IF;
325
326 IF (((old_references.CATALOG_CAL_TYPE = new_references.CATALOG_CAL_TYPE) AND
327 (old_references.CATALOG_SEQUENCE_NUMBER = new_references.CATALOG_SEQUENCE_NUMBER)) OR
328 ((new_references.CATALOG_CAL_TYPE IS NULL) OR
329 (new_references.CATALOG_SEQUENCE_NUMBER IS NULL))) THEN
330 NULL;
331 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
332 new_references.CATALOG_CAL_TYPE,
333 new_references.CATALOG_SEQUENCE_NUMBER
334 ) THEN
335 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED 6');
336 igs_ge_msg_stack.add;
337 app_exception.raise_exception;
338 END IF;
339
340
341 END Check_Parent_Existance;
342
343 PROCEDURE before_dml (
344 p_action IN VARCHAR2,
345 x_rowid IN VARCHAR2,-- DEFAULT NULL,
346 -- x_person_profile_id IN NUMBER DEFAULT NULL,
347 x_person_id IN NUMBER ,-- DEFAULT NULL,
348 x_effective_start_date IN DATE ,--DEFAULT NULL,
349 x_effective_end_date IN DATE ,--DEFAULT NULL,
350 x_religion_cd IN VARCHAR2 ,-- DEFAULT NULL,
351 -- x_criminal_convict IN VARCHAR2 DEFAULT NULL,
352 -- x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
353 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
354 -- x_country_cd3 IN VARCHAR2 DEFAULT NULL,
355 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
356 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
357 x_socio_eco_cd IN VARCHAR2 ,-- DEFAULT NULL,
358 x_next_to_kin IN VARCHAR2 ,-- DEFAULT NULL,
359 x_in_state_tuition IN VARCHAR2 ,-- DEFAULT NULL,
360 x_tuition_st_date IN DATE ,-- DEFAULT NULL,
361 x_tuition_end_date IN DATE ,-- DEFAULT NULL,
362 x_further_education_cd IN VARCHAR2 ,-- DEFAULT NULL,
363 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
364 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
365 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
366 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
367 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
368 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
369 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
370 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
371 x_creation_date IN DATE ,-- DEFAULT NULL,
372 x_created_by IN NUMBER ,-- DEFAULT NULL,
373 x_last_update_date IN DATE ,-- DEFAULT NULL,
374 x_last_updated_by IN NUMBER ,-- DEFAULT NULL,
375 x_last_update_login IN NUMBER ,-- DEFAULT NULL ,
376 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
377 X_ATTRIBUTE1 IN VARCHAR2 ,-- DEFAULT NULL,
378 X_ATTRIBUTE2 IN VARCHAR2 ,--DEFAULT NULL,
379 X_ATTRIBUTE3 IN VARCHAR2 ,--DEFAULT NULL,
380 X_ATTRIBUTE4 IN VARCHAR2 ,--DEFAULT NULL,
381 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
382 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
383 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
384 X_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
385 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
386 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
387 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
388 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
389 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
390 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
391 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
392 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
393 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
394 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
395 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
396 X_ATTRIBUTE20 IN VARCHAR2 ,--DEFAULT NULL
397 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
398 ) AS
399 /*
400 || Created By : bshankar
401 || Created On : 28-AUG-2000
402 || Purpose : Initialises the columns, Checks Constraints, Calls the
403 || Trigger Handlers for the table, before any DML operation.
404 || Known limitations, enhancements or remarks :
405 || Change History :
406 || Who When What
407 || Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
408 || (reverse chronological order - newest change first)
409 */
410 BEGIN
411
412 set_column_values (
413 p_action,
414 x_rowid,
415 -- x_person_profile_id,
416 x_person_id,
417 x_effective_start_date,
418 x_effective_end_date,
419 x_religion_cd,
420 --x_criminal_convict,
421 --x_acad_dismissal,
422 --x_non_acad_dismissal,
423 --x_country_cd3,
424 --x_state_of_residence,
425 --x_resid_stat_id,
426 x_socio_eco_cd,
427 x_next_to_kin,
428 x_in_state_tuition,
429 x_tuition_st_date,
430 x_tuition_end_date,
431 x_further_education_cd,
432 X_MATR_CAL_TYPE,
433 X_MATR_SEQUENCE_NUMBER,
434 X_INIT_CAL_TYPE,
435 X_INIT_SEQUENCE_NUMBER,
436 X_RECENT_CAL_TYPE,
437 X_RECENT_SEQUENCE_NUMBER,
438 X_CATALOG_CAL_TYPE,
439 X_CATALOG_SEQUENCE_NUMBER,
440 x_creation_date,
441 x_created_by,
442 x_last_update_date,
443 x_last_updated_by,
444 x_last_update_login ,
445 x_ATTRIBUTE_CATEGORY,
446 x_ATTRIBUTE1 ,
447 x_ATTRIBUTE2 ,
448 x_ATTRIBUTE3 ,
449 x_ATTRIBUTE4 ,
450 x_ATTRIBUTE5 ,
451 x_ATTRIBUTE6 ,
452 x_ATTRIBUTE7 ,
453 x_ATTRIBUTE8 ,
454 x_ATTRIBUTE9 ,
455 x_ATTRIBUTE10 ,
456 x_ATTRIBUTE11 ,
457 x_ATTRIBUTE12 ,
458 x_ATTRIBUTE13 ,
459 x_ATTRIBUTE14 ,
460 x_ATTRIBUTE15 ,
461 x_ATTRIBUTE16 ,
462 x_ATTRIBUTE17 ,
463 x_ATTRIBUTE18 ,
464 x_ATTRIBUTE19 ,
465 x_ATTRIBUTE20 ,
466 X_BIRTH_CNTRY_RESN_CODE
467 );
468
469 IF (p_action = 'INSERT') THEN
470 -- Call all the procedures related to Before Insert.
471 Check_Parent_Existance;
472 IF ( get_pk_for_validation(
473 new_references.person_id
474 )
475 ) THEN
476 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
477 igs_ge_msg_stack.ADD;
478 app_exception.raise_exception;
479 END IF;
480 ELSIF (p_action = 'UPDATE') THEN
481 -- Call all the procedures related to Before Update.
482 Check_Parent_Existance;
483 ELSIF (p_action = 'VALIDATE_INSERT') THEN
484 -- Call all the procedures related to Before Insert.
485 IF ( get_pk_for_validation (
486 new_references.person_id
487 )
488 ) THEN
489 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
490 igs_ge_msg_stack.ADD;
491 app_exception.raise_exception;
492 END IF;
493 END IF;
494
495 END before_dml;
496
497 PROCEDURE insert_row (
498 x_rowid IN OUT NOCOPY VARCHAR2,
499 -- x_person_profile_id IN NUMBER,
500 x_person_id IN NUMBER,
501 x_effective_start_date IN DATE,
502 x_effective_end_date IN DATE,
503 x_religion_cd IN VARCHAR2,
504 --x_criminal_convict IN VARCHAR2 DEFAULT NULL,
505 --x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
506 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
507 --x_country_cd3 IN VARCHAR2 DEFAULT NULL,
508 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
509 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
510 x_socio_eco_cd IN VARCHAR2,
511 x_next_to_kin IN VARCHAR2,
512 x_in_state_tuition IN VARCHAR2,
513 x_tuition_st_date IN DATE,
514 x_tuition_end_date IN DATE,
515 x_further_education_cd IN VARCHAR2 ,
516 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
517 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
518 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
519 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
520 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
521 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
522 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
523 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
524 x_mode IN VARCHAR2,-- DEFAULT 'R' ,
525 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
526 X_ATTRIBUTE1 IN VARCHAR2,-- DEFAULT NULL,
527 X_ATTRIBUTE2 IN VARCHAR2,-- DEFAULT NULL,
528 X_ATTRIBUTE3 IN VARCHAR2,-- DEFAULT NULL,
529 X_ATTRIBUTE4 IN VARCHAR2,-- DEFAULT NULL,
530 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
531 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
532 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
533 X_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
534 X_ATTRIBUTE9 IN VARCHAR2,-- DEFAULT NULL,
535 X_ATTRIBUTE10 IN VARCHAR2,-- DEFAULT NULL,
536 X_ATTRIBUTE11 IN VARCHAR2,-- DEFAULT NULL,
537 X_ATTRIBUTE12 IN VARCHAR2,-- DEFAULT NULL,
538 X_ATTRIBUTE13 IN VARCHAR2,-- DEFAULT NULL,
539 X_ATTRIBUTE14 IN VARCHAR2,-- DEFAULT NULL,
540 X_ATTRIBUTE15 IN VARCHAR2,-- DEFAULT NULL,
541 X_ATTRIBUTE16 IN VARCHAR2,-- DEFAULT NULL,
542 X_ATTRIBUTE17 IN VARCHAR2,-- DEFAULT NULL,
543 X_ATTRIBUTE18 IN VARCHAR2,-- DEFAULT NULL,
544 X_ATTRIBUTE19 IN VARCHAR2,-- DEFAULT NULL,
545 X_ATTRIBUTE20 IN VARCHAR2, --DEFAULT NULL
546 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
547 ) AS
548 /*
549 || Created By : bshankar
550 || Created On : 28-AUG-2000
551 || Purpose : Handles the INSERT DML logic for the table.
552 || Known limitations, enhancements or remarks :
553 || Change History :
554 || Who When What
555 || Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
556 || (reverse chronological order - newest change first)
557 || ssawhney 2203778 person_id replaces person_profile_id
558 */
559 CURSOR c IS
560 SELECT ROWID
561 FROM igs_pe_stat_details
562 WHERE person_id = x_person_id;
563
564 x_last_update_date DATE;
565 x_last_updated_by NUMBER;
566 x_last_update_login NUMBER;
567 x_request_id NUMBER;
568 x_program_id NUMBER;
569 x_program_application_id NUMBER;
570 x_program_update_date DATE;
571
572 BEGIN
573
574 x_last_update_date := SYSDATE;
575 IF (x_mode = 'I') THEN
576 x_last_updated_by := 1;
577 x_last_update_login := 0;
578 ELSIF (X_MODE IN ('R', 'S')) THEN
579 x_last_updated_by := fnd_global.user_id;
580 IF (x_last_updated_by IS NULL) THEN
581 x_last_updated_by := -1;
582 END IF;
583 x_last_update_login := fnd_global.login_id;
584 IF (x_last_update_login IS NULL) THEN
585 x_last_update_login := -1;
586 END IF;
587 x_request_id := fnd_global.conc_request_id;
588 x_program_id := fnd_global.conc_program_id;
589 x_program_application_id := fnd_global.prog_appl_id;
590
591 IF (x_request_id = -1) THEN
592 x_request_id := NULL;
593 x_program_id := NULL;
594 x_program_application_id := NULL;
595 x_program_update_date := NULL;
596 ELSE
597 x_program_update_date := SYSDATE;
598 END IF;
599 ELSE
600 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
601 igs_ge_msg_stack.ADD;
602 app_exception.raise_exception;
603 END IF;
604
605 before_dml(
606 p_action => 'INSERT',
607 x_rowid => x_rowid,
608 --x_person_profile_id => x_person_profile_id,
609 x_person_id => x_person_id,
610 x_effective_start_date => x_effective_start_date,
611 x_effective_end_date => x_effective_end_date,
612 x_religion_cd => x_religion_cd,
613 --x_criminal_convict => x_criminal_convict,
614 --x_acad_dismissal => x_acad_dismissal,
615 --x_non_acad_dismissal => x_non_acad_dismissal,
616 --x_country_cd3 => x_country_cd3,
617 -- x_state_of_residence => x_state_of_residence,
618 --x_resid_stat_id => x_resid_stat_id,
619 x_socio_eco_cd => x_socio_eco_cd,
620 x_next_to_kin => x_next_to_kin,
621 x_in_state_tuition => x_in_state_tuition,
622 x_tuition_st_date => x_tuition_st_date,
623 x_tuition_end_date => x_tuition_end_date,
624 x_further_education_cd => x_further_education_cd,
625 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
626 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
627 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
628 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
629 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
630 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
631 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
632 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
633 x_creation_date => x_last_update_date,
634 x_created_by => x_last_updated_by,
635 x_last_update_date => x_last_update_date,
636 x_last_updated_by => x_last_updated_by,
637 x_last_update_login => x_last_update_login ,
638 X_ATTRIBUTE_CATEGORY =>X_ATTRIBUTE_CATEGORY,
639 X_ATTRIBUTE1 =>X_ATTRIBUTE1,
640 X_ATTRIBUTE2 =>X_ATTRIBUTE2,
641 X_ATTRIBUTE3 =>X_ATTRIBUTE3,
642 X_ATTRIBUTE4 =>X_ATTRIBUTE4,
643 X_ATTRIBUTE5 =>X_ATTRIBUTE5,
644 X_ATTRIBUTE6 =>X_ATTRIBUTE6,
645 X_ATTRIBUTE7 =>X_ATTRIBUTE7,
646 X_ATTRIBUTE8 =>X_ATTRIBUTE8,
647 X_ATTRIBUTE9 =>X_ATTRIBUTE9,
648 X_ATTRIBUTE10 =>X_ATTRIBUTE10,
649 X_ATTRIBUTE11 =>X_ATTRIBUTE11,
650 X_ATTRIBUTE12 =>X_ATTRIBUTE12,
651 X_ATTRIBUTE13 =>X_ATTRIBUTE13,
652 X_ATTRIBUTE14 =>X_ATTRIBUTE14,
653 X_ATTRIBUTE15 =>X_ATTRIBUTE15,
654 X_ATTRIBUTE16 =>X_ATTRIBUTE16,
655 X_ATTRIBUTE17 =>X_ATTRIBUTE17,
656 X_ATTRIBUTE18 =>X_ATTRIBUTE18,
657 X_ATTRIBUTE19 =>X_ATTRIBUTE19,
658 X_ATTRIBUTE20 =>X_ATTRIBUTE20,
659 X_BIRTH_CNTRY_RESN_CODE =>X_BIRTH_CNTRY_RESN_CODE
660 );
661
662 IF (x_mode = 'S') THEN
663 igs_sc_gen_001.set_ctx('R');
664 END IF;
665 INSERT INTO igs_pe_stat_details (
666 -- person_profile_id,
667 person_id,
668 effective_start_date,
669 effective_end_date,
670 religion_cd,
671 --criminal_convict,
672 --acad_dismissal,
673 --non_acad_dismissal,
674 --country_cd3,
675 --state_of_residence,
676 --resid_stat_id,
677 socio_eco_cd,
678 next_to_kin,
679 in_state_tuition,
680 tuition_st_date,
681 tuition_end_date,
682 further_education_cd,
683 MATR_CAL_TYPE,
684 MATR_SEQUENCE_NUMBER,
685 INIT_CAL_TYPE,
686 INIT_SEQUENCE_NUMBER,
687 RECENT_CAL_TYPE,
688 RECENT_SEQUENCE_NUMBER,
689 CATALOG_CAL_TYPE,
690 CATALOG_SEQUENCE_NUMBER,
691 creation_date,
692 created_by,
693 last_update_date,
694 last_updated_by,
695 last_update_login,
696 request_id,
697 program_id,
698 program_application_id,
699 program_update_date ,
700 ATTRIBUTE_CATEGORY,
701 ATTRIBUTE1,
702 ATTRIBUTE2,
703 ATTRIBUTE3,
704 ATTRIBUTE4,
705 ATTRIBUTE5,
706 ATTRIBUTE6,
707 ATTRIBUTE7,
708 ATTRIBUTE8,
709 ATTRIBUTE9,
710 ATTRIBUTE10,
711 ATTRIBUTE11,
712 ATTRIBUTE12,
713 ATTRIBUTE13,
714 ATTRIBUTE14,
715 ATTRIBUTE15,
716 ATTRIBUTE16,
717 ATTRIBUTE17,
718 ATTRIBUTE18,
719 ATTRIBUTE19,
720 ATTRIBUTE20,
721 BIRTH_CNTRY_RESN_CODE)
722 VALUES
723 (
724 -- new_references.person_profile_id,
725 new_references.person_id,
726 new_references.effective_start_date,
727 new_references.effective_end_date,
728 new_references.religion_cd,
729 --new_references.criminal_convict,
730 --new_references.acad_dismissal,
731 --new_references.non_acad_dismissal,
732 --new_references.country_cd3,
733 --new_references.state_of_residence,
734 --new_references.resid_stat_id,
735 new_references.socio_eco_cd,
736 new_references.next_to_kin,
737 new_references.in_state_tuition,
738 new_references.tuition_st_date,
739 new_references.tuition_end_date,
740 new_references.further_education_cd,
741 new_references.MATR_CAL_TYPE,
742 new_references.MATR_SEQUENCE_NUMBER,
743 new_references.INIT_CAL_TYPE,
744 new_references.INIT_SEQUENCE_NUMBER,
745 new_references.RECENT_CAL_TYPE,
746 new_references.RECENT_SEQUENCE_NUMBER,
747 new_references.CATALOG_CAL_TYPE,
748 new_references.CATALOG_SEQUENCE_NUMBER,
749 x_last_update_date,
750 x_last_updated_by,
751 x_last_update_date,
752 x_last_updated_by,
753 x_last_update_login ,
754 x_request_id,
755 x_program_id,
756 x_program_application_id,
757 x_program_update_date ,
758 new_references.ATTRIBUTE_CATEGORY,
759 new_references.ATTRIBUTE1,
760 new_references.ATTRIBUTE2,
761 new_references.ATTRIBUTE3,
762 new_references.ATTRIBUTE4,
763 new_references.ATTRIBUTE5,
764 new_references.ATTRIBUTE6,
765 new_references.ATTRIBUTE7,
766 new_references.ATTRIBUTE8,
767 new_references.ATTRIBUTE9,
768 new_references.ATTRIBUTE10,
769 new_references.ATTRIBUTE11,
770 new_references.ATTRIBUTE12,
771 new_references.ATTRIBUTE13,
772 new_references.ATTRIBUTE14,
773 new_references.ATTRIBUTE15,
774 new_references.ATTRIBUTE16,
775 new_references.ATTRIBUTE17,
776 new_references.ATTRIBUTE18,
777 new_references.ATTRIBUTE19,
778 new_references.ATTRIBUTE20,
779 new_references.birth_cntry_resn_code
780 );
781 IF (x_mode = 'S') THEN
782 igs_sc_gen_001.unset_ctx('R');
783 END IF;
784
785
786 OPEN c;
787 FETCH c INTO x_rowid;
788 IF (c%NOTFOUND) THEN
789 CLOSE c;
790 RAISE NO_DATA_FOUND;
791 END IF;
792 CLOSE c;
793
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
798 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
799 fnd_message.set_token ('ERR_CD', SQLCODE);
800 igs_ge_msg_stack.add;
801 igs_sc_gen_001.unset_ctx('R');
802 app_exception.raise_exception;
803 ELSE
804 igs_sc_gen_001.unset_ctx('R');
805 RAISE;
806 END IF;
807 END insert_row;
808
809
810 PROCEDURE lock_row (
811 x_rowid IN VARCHAR2,
812 --x_person_profile_id IN NUMBER,
813 x_person_id IN NUMBER,
814 x_effective_start_date IN DATE,
815 x_effective_end_date IN DATE,
816 x_religion_cd IN VARCHAR2,
817 --x_criminal_convict IN VARCHAR2,
818 --x_acad_dismissal IN VARCHAR2,
819 --x_non_acad_dismissal IN VARCHAR2,
820 --x_country_cd3 IN VARCHAR2,
821 --x_state_of_residence IN VARCHAR2,
822 --x_resid_stat_id IN NUMBER,
823 x_socio_eco_cd IN VARCHAR2,
824 x_next_to_kin IN VARCHAR2,
825 x_in_state_tuition IN VARCHAR2,
826 x_tuition_st_date IN DATE,
827 x_tuition_end_date IN DATE,
828 x_further_education_cd IN VARCHAR2,
829 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
830 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
831 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
832 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
833 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
834 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
835 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
836 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
837 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
838 X_ATTRIBUTE1 IN VARCHAR2,-- DEFAULT NULL,
839 X_ATTRIBUTE2 IN VARCHAR2,-- DEFAULT NULL,
840 X_ATTRIBUTE3 IN VARCHAR2,-- DEFAULT NULL,
841 X_ATTRIBUTE4 IN VARCHAR2,-- DEFAULT NULL,
842 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
843 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
844 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
845 X_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
846 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
847 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
848 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
849 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
850 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
851 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
852 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
853 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
854 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
855 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
856 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
857 X_ATTRIBUTE20 IN VARCHAR2, --DEFAULT NULL
858 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
859 ) AS
860 /*
861 || Created By : bshankar
862 || Created On : 28-AUG-2000
863 || Purpose : Handles the LOCK mechanism for the table.
864 || Known limitations, enhancements or remarks :
865 || Change History :
866 || Who When What
867 || Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
868 || (reverse chronological order - newest change first)
869 */
870
871 CURSOR c1 IS
872 SELECT
873 effective_start_date,
874 effective_end_date,
875 religion_cd,
876 --criminal_convict,
877 --acad_dismissal,
878 --non_acad_dismissal,
879 --country_cd3,
880 --state_of_residence,
881 --resid_stat_id,
882 socio_eco_cd,
883 next_to_kin,
884 in_state_tuition,
885 tuition_st_date,
886 tuition_end_date,
887 further_education_cd,
888 MATR_CAL_TYPE,
889 MATR_SEQUENCE_NUMBER,
890 INIT_CAL_TYPE,
891 INIT_SEQUENCE_NUMBER,
892 RECENT_CAL_TYPE,
893 RECENT_SEQUENCE_NUMBER,
894 CATALOG_CAL_TYPE,
895 CATALOG_SEQUENCE_NUMBER,
896 ATTRIBUTE_CATEGORY,
897 ATTRIBUTE1,
898 ATTRIBUTE2,
899 ATTRIBUTE3,
900 ATTRIBUTE4,
901 ATTRIBUTE5,
902 ATTRIBUTE6,
903 ATTRIBUTE7,
904 ATTRIBUTE8,
905 ATTRIBUTE9,
906 ATTRIBUTE10,
907 ATTRIBUTE11,
908 ATTRIBUTE12,
909 ATTRIBUTE13,
910 ATTRIBUTE14,
911 ATTRIBUTE15,
912 ATTRIBUTE16,
913 ATTRIBUTE17,
914 ATTRIBUTE18,
915 ATTRIBUTE19,
916 ATTRIBUTE20,
917 BIRTH_CNTRY_RESN_CODE
918 FROM igs_pe_stat_details
919 WHERE ROWID = x_rowid
920 FOR UPDATE NOWAIT;
921
922 tlinfo c1%ROWTYPE;
923
924 BEGIN
925 OPEN c1;
926 FETCH c1 INTO tlinfo;
927 IF (c1%NOTFOUND) THEN
928 fnd_message.set_name('FND', 'FORM_RECORD_DELETED 7');
929 igs_ge_msg_stack.ADD;
930 CLOSE c1;
931 app_exception.raise_exception;
932 RETURN;
933 END IF;
934 CLOSE c1;
935
936 IF (
937 (tlinfo.effective_start_date = x_effective_start_date)
938 AND ((tlinfo.effective_end_date = x_effective_end_date) OR ((tlinfo.effective_end_date IS NULL) AND (X_effective_end_date IS NULL)))
939 AND ((tlinfo.religion_cd = x_religion_cd) OR ((tlinfo.religion_cd IS NULL) AND (X_religion_cd IS NULL)))
940 -- AND ((tlinfo.criminal_convict = x_criminal_convict) OR ((tlinfo.criminal_convict IS NULL) AND (X_criminal_convict IS NULL)))
941 -- AND ((tlinfo.acad_dismissal = x_acad_dismissal) OR ((tlinfo.acad_dismissal IS NULL) AND (X_acad_dismissal IS NULL)))
942 -- AND ((tlinfo.non_acad_dismissal = x_non_acad_dismissal) OR ((tlinfo.non_acad_dismissal IS NULL) AND (X_non_acad_dismissal IS NULL)))
943 -- AND ((tlinfo.country_cd3 = x_country_cd3) OR ((tlinfo.country_cd3 IS NULL) AND (X_country_cd3 IS NULL)))
944 -- AND ((tlinfo.state_of_residence = x_state_of_residence) OR ((tlinfo.state_of_residence IS NULL) AND (X_state_of_residence IS NULL)))
945 -- AND ((tlinfo.resid_stat_id = x_resid_stat_id) OR ((tlinfo.resid_stat_id IS NULL) AND (X_resid_stat_id IS NULL)))
946 AND ((tlinfo.socio_eco_cd = x_socio_eco_cd) OR ((tlinfo.socio_eco_cd IS NULL) AND (X_socio_eco_cd IS NULL)))
947 AND ((tlinfo.next_to_kin = x_next_to_kin) OR ((tlinfo.next_to_kin IS NULL) AND (X_next_to_kin IS NULL)))
948 AND ((tlinfo.in_state_tuition = x_in_state_tuition) OR ((tlinfo.in_state_tuition IS NULL) AND (X_in_state_tuition IS NULL)))
949 AND ((tlinfo.tuition_st_date = x_tuition_st_date) OR ((tlinfo.tuition_st_date IS NULL) AND (X_tuition_st_date IS NULL)))
950 AND ((tlinfo.tuition_end_date = x_tuition_end_date) OR ((tlinfo.tuition_end_date IS NULL) AND (X_tuition_end_date IS NULL)))
951
952 AND ((tlinfo.further_education_cd = x_further_education_cd) OR ((tlinfo.further_education_cd IS NULL) AND (X_further_education_cd IS NULL)))
953 AND ((tlinfo.MATR_CAL_TYPE = X_MATR_CAL_TYPE) OR ((tlinfo.MATR_CAL_TYPE IS NULL) AND (X_MATR_CAL_TYPE IS NULL)))
954 AND ((tlinfo.MATR_SEQUENCE_NUMBER = X_MATR_SEQUENCE_NUMBER) OR ((tlinfo.MATR_SEQUENCE_NUMBER IS NULL) AND (X_MATR_SEQUENCE_NUMBER IS NULL)))
955 AND ((tlinfo.INIT_CAL_TYPE = X_INIT_CAL_TYPE) OR ((tlinfo.INIT_CAL_TYPE IS NULL) AND (X_INIT_CAL_TYPE IS NULL)))
956 AND ((tlinfo.INIT_SEQUENCE_NUMBER = X_INIT_SEQUENCE_NUMBER) OR ((tlinfo.INIT_SEQUENCE_NUMBER IS NULL) AND (X_INIT_SEQUENCE_NUMBER IS NULL)))
957 AND ((tlinfo.RECENT_CAL_TYPE = X_RECENT_CAL_TYPE) OR ((tlinfo.RECENT_CAL_TYPE IS NULL) AND (X_RECENT_CAL_TYPE IS NULL)))
958 AND ((tlinfo.RECENT_SEQUENCE_NUMBER = X_RECENT_SEQUENCE_NUMBER) OR ((tlinfo.RECENT_SEQUENCE_NUMBER IS NULL) AND (X_RECENT_SEQUENCE_NUMBER IS NULL)))
959 AND ((tlinfo.BIRTH_CNTRY_RESN_CODE = X_BIRTH_CNTRY_RESN_CODE) OR ((tlinfo.BIRTH_CNTRY_RESN_CODE IS NULL) AND (X_BIRTH_CNTRY_RESN_CODE IS NULL)))
960 AND ((tlinfo.CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE) OR ((tlinfo.CATALOG_CAL_TYPE IS NULL) AND (X_CATALOG_CAL_TYPE IS NULL)))
961 AND ((tlinfo.CATALOG_SEQUENCE_NUMBER = X_CATALOG_SEQUENCE_NUMBER) OR ((tlinfo.CATALOG_SEQUENCE_NUMBER IS NULL) AND (X_CATALOG_SEQUENCE_NUMBER IS NULL)))
962 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
963 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
964 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
965 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
966 OR ((tlinfo.ATTRIBUTE1 IS NULL)
967 AND (X_ATTRIBUTE1 IS NULL)))
968 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
969 OR ((tlinfo.ATTRIBUTE2 IS NULL)
970 AND (X_ATTRIBUTE2 IS NULL)))
971 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
972 OR ((tlinfo.ATTRIBUTE3 IS NULL)
973 AND (X_ATTRIBUTE3 IS NULL)))
974 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
975 OR ((tlinfo.ATTRIBUTE4 IS NULL)
976 AND (X_ATTRIBUTE4 IS NULL)))
977 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
978 OR ((tlinfo.ATTRIBUTE5 IS NULL)
979 AND (X_ATTRIBUTE5 IS NULL)))
980 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
981 OR ((tlinfo.ATTRIBUTE6 IS NULL)
982 AND (X_ATTRIBUTE6 IS NULL)))
983 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
984 OR ((tlinfo.ATTRIBUTE7 IS NULL)
985 AND (X_ATTRIBUTE7 IS NULL)))
986 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
987 OR ((tlinfo.ATTRIBUTE8 IS NULL)
988 AND (X_ATTRIBUTE8 IS NULL)))
989 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
990 OR ((tlinfo.ATTRIBUTE9 IS NULL)
991 AND (X_ATTRIBUTE9 IS NULL)))
992 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
993 OR ((tlinfo.ATTRIBUTE10 IS NULL)
994 AND (X_ATTRIBUTE10 IS NULL)))
995 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
996 OR ((tlinfo.ATTRIBUTE11 IS NULL)
997 AND (X_ATTRIBUTE11 IS NULL)))
998 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
999 OR ((tlinfo.ATTRIBUTE12 IS NULL)
1000 AND (X_ATTRIBUTE12 IS NULL)))
1001 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1002 OR ((tlinfo.ATTRIBUTE13 IS NULL)
1003 AND (X_ATTRIBUTE13 IS NULL)))
1004 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1005 OR ((tlinfo.ATTRIBUTE14 IS NULL)
1006 AND (X_ATTRIBUTE14 IS NULL)))
1007 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1008 OR ((tlinfo.ATTRIBUTE15 IS NULL)
1009 AND (X_ATTRIBUTE15 IS NULL)))
1010 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1011 OR ((tlinfo.ATTRIBUTE16 IS NULL)
1012 AND (X_ATTRIBUTE16 IS NULL)))
1013 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1014 OR ((tlinfo.ATTRIBUTE17 IS NULL)
1015 AND (X_ATTRIBUTE17 IS NULL)))
1016 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1017 OR ((tlinfo.ATTRIBUTE18 IS NULL)
1018 AND (X_ATTRIBUTE18 IS NULL)))
1019 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1020 OR ((tlinfo.ATTRIBUTE19 IS NULL)
1021 AND (X_ATTRIBUTE19 IS NULL)))
1022 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1023 OR ((tlinfo.ATTRIBUTE20 IS NULL)
1024 AND (X_ATTRIBUTE20 IS NULL)))
1025 ) THEN
1026 NULL;
1027 ELSE
1028 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1029 igs_ge_msg_stack.ADD;
1030 app_exception.raise_exception;
1031 END IF;
1032
1033 RETURN;
1034
1035 END lock_row;
1036
1037
1038 PROCEDURE update_row (
1039 x_rowid IN VARCHAR2,
1040 -- x_person_profile_id IN NUMBER,
1041 x_person_id IN NUMBER,
1042 x_effective_start_date IN DATE,
1043 x_effective_end_date IN DATE,
1044 x_religion_cd IN VARCHAR2,
1045 --x_criminal_convict IN VARCHAR2 DEFAULT NULL,
1046 --x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
1047 --x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
1048 --x_country_cd3 IN VARCHAR2 DEFAULT NULL,
1049 --x_state_of_residence IN VARCHAR2 DEFAULT NULL,
1050 --x_resid_stat_id IN NUMBER DEFAULT NULL,
1051 x_socio_eco_cd IN VARCHAR2,
1052 x_next_to_kin IN VARCHAR2,
1053 x_in_state_tuition IN VARCHAR2,
1054 x_tuition_st_date IN DATE,
1055 x_tuition_end_date IN DATE,
1056 x_further_education_cd IN VARCHAR2,
1057 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1058 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1059 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1060 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1061 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1062 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1063 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1064 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1065 x_mode IN VARCHAR2 ,--DEFAULT 'R' ,
1066 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
1067 X_ATTRIBUTE1 IN VARCHAR2,-- DEFAULT NULL,
1068 X_ATTRIBUTE2 IN VARCHAR2,-- DEFAULT NULL,
1069 X_ATTRIBUTE3 IN VARCHAR2,-- DEFAULT NULL,
1070 X_ATTRIBUTE4 IN VARCHAR2,-- DEFAULT NULL,
1071 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
1072 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
1073 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
1074 X_ATTRIBUTE8 IN VARCHAR2,-- DEFAULT NULL,
1075 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
1076 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
1077 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
1078 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
1079 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
1080 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
1081 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
1082 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
1083 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
1084 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
1085 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
1086 X_ATTRIBUTE20 IN VARCHAR2, -- DEFAULT NULL
1087 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
1088 ) AS
1089 /*
1090 || Created By : bshankar
1091 || Created On : 28-AUG-2000
1092 || Purpose : Handles the UPDATE DML logic for the table.
1093 || Known limitations, enhancements or remarks :
1094 || Change History :
1095 || Who When What
1096 || Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
1097 || (reverse chronological order - newest change first)
1098 */
1099 x_last_update_date DATE ;
1100 x_last_updated_by NUMBER;
1101 x_last_update_login NUMBER;
1102 x_request_id NUMBER;
1103 x_program_id NUMBER;
1104 x_program_application_id NUMBER;
1105 x_program_update_date DATE;
1106
1107 BEGIN
1108
1109 x_last_update_date := SYSDATE;
1110 IF (X_MODE = 'I') THEN
1111 x_last_updated_by := 1;
1112 x_last_update_login := 0;
1113 ELSIF (X_MODE IN ('R', 'S')) THEN
1114 x_last_updated_by := fnd_global.user_id;
1115 IF x_last_updated_by IS NULL THEN
1116 x_last_updated_by := -1;
1117 END IF;
1118 x_last_update_login := fnd_global.login_id;
1119 IF (x_last_update_login IS NULL) THEN
1120 x_last_update_login := -1;
1121 END IF;
1122 ELSE
1123 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1124 igs_ge_msg_stack.ADD;
1125 app_exception.raise_exception;
1126 END IF;
1127
1128 before_dml(
1129 p_action => 'UPDATE',
1130 x_rowid => x_rowid,
1131 --x_person_profile_id => x_person_profile_id,
1132 x_person_id => x_person_id,
1133 x_effective_start_date => x_effective_start_date,
1134 x_effective_end_date => x_effective_end_date,
1135 x_religion_cd => x_religion_cd,
1136 --x_criminal_convict => x_criminal_convict,
1137 --x_acad_dismissal => x_acad_dismissal,
1138 --x_non_acad_dismissal => x_non_acad_dismissal,
1139 --x_country_cd3 => x_country_cd3,
1140 --x_state_of_residence => x_state_of_residence,
1141 --x_resid_stat_id => x_resid_stat_id,
1142 x_socio_eco_cd => x_socio_eco_cd,
1143 x_next_to_kin => x_next_to_kin,
1144 x_in_state_tuition => x_in_state_tuition,
1145 x_tuition_st_date => x_tuition_st_date,
1146 x_tuition_end_date => x_tuition_end_date,
1147 x_further_education_cd => x_further_education_cd,
1148 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
1149 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
1150 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
1151 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
1152 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
1153 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
1154 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
1155 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
1156 x_creation_date => x_last_update_date,
1157 x_created_by => x_last_updated_by,
1158 x_last_update_date => x_last_update_date,
1159 x_last_updated_by => x_last_updated_by,
1160 x_last_update_login => x_last_update_login ,
1161 X_ATTRIBUTE_CATEGORY =>X_ATTRIBUTE_CATEGORY,
1162 X_ATTRIBUTE1 =>X_ATTRIBUTE1,
1163 X_ATTRIBUTE2 =>X_ATTRIBUTE2,
1164 X_ATTRIBUTE3 =>X_ATTRIBUTE3,
1165 X_ATTRIBUTE4 =>X_ATTRIBUTE4,
1166 X_ATTRIBUTE5 =>X_ATTRIBUTE5,
1167 X_ATTRIBUTE6 =>X_ATTRIBUTE6,
1168 X_ATTRIBUTE7 =>X_ATTRIBUTE7,
1169 X_ATTRIBUTE8 =>X_ATTRIBUTE8,
1170 X_ATTRIBUTE9 =>X_ATTRIBUTE9,
1171 X_ATTRIBUTE10 =>X_ATTRIBUTE10,
1172 X_ATTRIBUTE11 =>X_ATTRIBUTE11,
1173 X_ATTRIBUTE12 =>X_ATTRIBUTE12,
1174 X_ATTRIBUTE13 =>X_ATTRIBUTE13,
1175 X_ATTRIBUTE14 =>X_ATTRIBUTE14,
1176 X_ATTRIBUTE15 =>X_ATTRIBUTE15,
1177 X_ATTRIBUTE16 =>X_ATTRIBUTE16,
1178 X_ATTRIBUTE17 =>X_ATTRIBUTE17,
1179 X_ATTRIBUTE18 =>X_ATTRIBUTE18,
1180 X_ATTRIBUTE19 =>X_ATTRIBUTE19,
1181 X_ATTRIBUTE20 =>X_ATTRIBUTE20,
1182 X_BIRTH_CNTRY_RESN_CODE =>X_BIRTH_CNTRY_RESN_CODE
1183 );
1184
1185 IF (X_MODE IN ('R', 'S')) THEN
1186 x_request_id := fnd_global.conc_request_id;
1187 x_program_id := fnd_global.conc_program_id;
1188 x_program_application_id := fnd_global.prog_appl_id;
1189 IF (x_request_id = -1) THEN
1190 x_request_id := old_references.request_id;
1191 x_program_id := old_references.program_id;
1192 x_program_application_id := old_references.program_application_id;
1193 x_program_update_date := old_references.program_update_date;
1194 ELSE
1195 x_program_update_date := SYSDATE;
1196 END IF;
1197 END IF;
1198
1199 IF (x_mode = 'S') THEN
1200 igs_sc_gen_001.set_ctx('R');
1201 END IF;
1202 UPDATE igs_pe_stat_details
1203 SET
1204 effective_start_date = new_references.effective_start_date,
1205 effective_end_date = new_references.effective_end_date,
1206 religion_cd = new_references.religion_cd,
1207 --criminal_convict = new_references.criminal_convict,
1208 --acad_dismissal = new_references.acad_dismissal,
1209 --non_acad_dismissal = new_references.non_acad_dismissal,
1210 --country_cd3 = new_references.country_cd3,
1211 --state_of_residence = new_references.state_of_residence,
1212 --resid_stat_id = new_references.resid_stat_id,
1213 socio_eco_cd = new_references.socio_eco_cd,
1214 next_to_kin = new_references.next_to_kin,
1215 in_state_tuition = new_references.in_state_tuition,
1216 tuition_st_date = new_references.tuition_st_date,
1217 tuition_end_date = new_references.tuition_end_date,
1218 further_education_cd = new_references.further_education_cd,
1219 MATR_CAL_TYPE = NEW_REFERENCES.MATR_CAL_TYPE,
1220 MATR_SEQUENCE_NUMBER = NEW_REFERENCES.MATR_SEQUENCE_NUMBER,
1221 INIT_CAL_TYPE = NEW_REFERENCES.INIT_CAL_TYPE,
1222 INIT_SEQUENCE_NUMBER = NEW_REFERENCES.INIT_SEQUENCE_NUMBER,
1223 RECENT_CAL_TYPE = NEW_REFERENCES.RECENT_CAL_TYPE,
1224 RECENT_SEQUENCE_NUMBER = NEW_REFERENCES.RECENT_SEQUENCE_NUMBER,
1225 CATALOG_CAL_TYPE = NEW_REFERENCES.CATALOG_CAL_TYPE,
1226 CATALOG_SEQUENCE_NUMBER = NEW_REFERENCES.CATALOG_SEQUENCE_NUMBER,
1227 last_update_date = x_last_update_date,
1228 last_updated_by = x_last_updated_by,
1229 last_update_login = x_last_update_login ,
1230 request_id = x_request_id,
1231 program_id = x_program_id,
1232 program_application_id = x_program_application_id,
1233 program_update_date = x_program_update_date ,
1234 ATTRIBUTE_CATEGORY = new_references.ATTRIBUTE_CATEGORY,
1235 ATTRIBUTE1 =new_references.ATTRIBUTE1,
1236 ATTRIBUTE2 =new_references.ATTRIBUTE2,
1237 ATTRIBUTE3 =new_references.ATTRIBUTE3,
1238 ATTRIBUTE4 =new_references.ATTRIBUTE4,
1239 ATTRIBUTE5 =new_references.ATTRIBUTE5,
1240 ATTRIBUTE6 =new_references.ATTRIBUTE6,
1241 ATTRIBUTE7 =new_references.ATTRIBUTE7,
1242 ATTRIBUTE8 =new_references.ATTRIBUTE8,
1243 ATTRIBUTE9 =new_references.ATTRIBUTE9,
1244 ATTRIBUTE10 =new_references.ATTRIBUTE10,
1245 ATTRIBUTE11 =new_references.ATTRIBUTE11,
1246 ATTRIBUTE12 =new_references.ATTRIBUTE12,
1247 ATTRIBUTE13 =new_references.ATTRIBUTE13,
1248 ATTRIBUTE14 =new_references.ATTRIBUTE14,
1249 ATTRIBUTE15 =new_references.ATTRIBUTE15,
1250 ATTRIBUTE16 =new_references.ATTRIBUTE16,
1251 ATTRIBUTE17 =new_references.ATTRIBUTE17,
1252 ATTRIBUTE18 =new_references.ATTRIBUTE18,
1253 ATTRIBUTE19 =new_references.ATTRIBUTE19,
1254 ATTRIBUTE20 =new_references.ATTRIBUTE20,
1255 BIRTH_CNTRY_RESN_CODE =new_references.BIRTH_CNTRY_RESN_CODE
1256 WHERE ROWID = x_rowid;
1257
1258 IF (SQL%NOTFOUND) THEN
1259 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1260 igs_ge_msg_stack.add;
1261 igs_sc_gen_001.unset_ctx('R');
1262 app_exception.raise_exception;
1263 END IF;
1264 IF (x_mode = 'S') THEN
1265 igs_sc_gen_001.unset_ctx('R');
1266 END IF;
1267
1268
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 IF (SQLCODE = (-28115)) THEN
1273 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1274 fnd_message.set_token ('ERR_CD', SQLCODE);
1275 igs_ge_msg_stack.add;
1276 igs_sc_gen_001.unset_ctx('R');
1277 app_exception.raise_exception;
1278 ELSE
1279 igs_sc_gen_001.unset_ctx('R');
1280 RAISE;
1281 END IF;
1282 END update_row;
1283
1284
1285 PROCEDURE add_row (
1286 x_rowid IN OUT NOCOPY VARCHAR2,
1287 -- x_person_profile_id IN NUMBER,
1288 x_person_id IN NUMBER,
1289 x_effective_start_date IN DATE,
1290 x_effective_end_date IN DATE,
1291 x_religion_cd IN VARCHAR2,
1292 --x_criminal_convict IN VARCHAR2 DEFAULT NULL,
1293 --x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
1294 --x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
1295 --x_country_cd3 IN VARCHAR2 DEFAULT NULL,
1296 --x_state_of_residence IN VARCHAR2 DEFAULT NULL,
1297 --x_resid_stat_id IN NUMBER DEFAULT NULL,
1298 x_socio_eco_cd IN VARCHAR2,
1299 x_next_to_kin IN VARCHAR2,
1300 x_in_state_tuition IN VARCHAR2,
1301 x_tuition_st_date IN DATE,
1302 x_tuition_end_date IN DATE,
1303 x_further_education_cd IN VARCHAR2 ,
1304 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1305 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1306 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1307 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1308 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1309 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1310 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
1311 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
1312 x_mode IN VARCHAR2 ,--DEFAULT 'R' ,
1313 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
1314 X_ATTRIBUTE1 IN VARCHAR2 ,-- DEFAULT NULL,
1315 X_ATTRIBUTE2 IN VARCHAR2 ,--DEFAULT NULL,
1316 X_ATTRIBUTE3 IN VARCHAR2 ,--DEFAULT NULL,
1317 X_ATTRIBUTE4 IN VARCHAR2 ,--DEFAULT NULL,
1318 X_ATTRIBUTE5 IN VARCHAR2,-- DEFAULT NULL,
1319 X_ATTRIBUTE6 IN VARCHAR2,-- DEFAULT NULL,
1320 X_ATTRIBUTE7 IN VARCHAR2,-- DEFAULT NULL,
1321 X_ATTRIBUTE8 IN VARCHAR2,-- DEFAULT NULL,
1322 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
1323 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
1324 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
1325 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
1326 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
1327 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
1328 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
1329 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
1330 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
1331 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
1332 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
1333 X_ATTRIBUTE20 IN VARCHAR2, --DEFAULT NULL
1334 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
1335 ) AS
1336 /*
1337 || Created By : bshankar
1338 || Created On : 28-AUG-2000
1339 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1340 || Known limitations, enhancements or remarks :
1341 || Change History :
1342 || Who When What
1343 || Bayadav 31-Jan-2002 Bug number 2203778 .addded descriptive flexfield columns (IGS_PE_PERS_STAT )
1344 || (reverse chronological order - newest change first)
1345 || ssawhney 2203778 : person_id replaces person_profile_id
1346 */
1347 CURSOR c1 IS
1348 SELECT ROWID
1349 FROM igs_pe_stat_details
1350 WHERE person_id = x_person_id;
1351
1352 BEGIN
1353
1354 OPEN c1;
1355 FETCH c1 INTO x_rowid;
1356 IF (c1%NOTFOUND) THEN
1357 CLOSE c1;
1358
1359 insert_row (
1360 x_rowid,
1361 -- x_person_profile_id,
1362 x_person_id,
1363 x_effective_start_date,
1364 x_effective_end_date,
1365 x_religion_cd,
1366 --x_criminal_convict,
1367 -- x_acad_dismissal,
1368 --x_non_acad_dismissal,
1369 --x_country_cd3,
1370 --x_state_of_residence,
1371 --x_resid_stat_id,
1372 x_socio_eco_cd,
1373 x_next_to_kin,
1374 x_in_state_tuition,
1375 x_tuition_st_date,
1376 x_tuition_end_date,
1377 x_further_education_cd,
1378 X_MATR_CAL_TYPE,
1379 X_MATR_SEQUENCE_NUMBER,
1380 X_INIT_CAL_TYPE,
1381 X_INIT_SEQUENCE_NUMBER,
1382 X_RECENT_CAL_TYPE,
1383 X_RECENT_SEQUENCE_NUMBER,
1384 X_CATALOG_CAL_TYPE,
1385 X_CATALOG_SEQUENCE_NUMBER,
1386 x_mode ,
1387 X_ATTRIBUTE_CATEGORY ,
1388 X_ATTRIBUTE1 ,
1389 X_ATTRIBUTE2 ,
1390 X_ATTRIBUTE3 ,
1391 X_ATTRIBUTE4 ,
1392 X_ATTRIBUTE5 ,
1393 X_ATTRIBUTE6 ,
1394 X_ATTRIBUTE7 ,
1395 X_ATTRIBUTE8 ,
1396 X_ATTRIBUTE9 ,
1397 X_ATTRIBUTE10 ,
1398 X_ATTRIBUTE11 ,
1399 X_ATTRIBUTE12 ,
1400 X_ATTRIBUTE13 ,
1401 X_ATTRIBUTE14 ,
1402 X_ATTRIBUTE15 ,
1403 X_ATTRIBUTE16 ,
1404 X_ATTRIBUTE17 ,
1405 X_ATTRIBUTE18 ,
1406 X_ATTRIBUTE19 ,
1407 X_ATTRIBUTE20 ,
1408 X_BIRTH_CNTRY_RESN_CODE
1409 );
1410 RETURN;
1411 END IF;
1412 CLOSE c1;
1413
1414 update_row (
1415 x_rowid,
1416 --x_person_profile_id,
1417 x_person_id,
1418 x_effective_start_date,
1419 x_effective_end_date,
1420 x_religion_cd,
1421 --x_criminal_convict,
1422 --x_acad_dismissal,
1423 --x_non_acad_dismissal,
1424 --x_country_cd3,
1425 --x_state_of_residence,
1426 --x_resid_stat_id,
1427 x_socio_eco_cd,
1428 x_next_to_kin,
1429 x_in_state_tuition,
1430 x_tuition_st_date,
1431 x_tuition_end_date,
1432 x_further_education_cd,
1433 X_MATR_CAL_TYPE,
1434 X_MATR_SEQUENCE_NUMBER,
1435 X_INIT_CAL_TYPE,
1436 X_INIT_SEQUENCE_NUMBER,
1437 X_RECENT_CAL_TYPE,
1438 X_RECENT_SEQUENCE_NUMBER,
1439 X_CATALOG_CAL_TYPE,
1440 X_CATALOG_SEQUENCE_NUMBER,
1441 x_mode ,
1442 X_ATTRIBUTE_CATEGORY ,
1443 X_ATTRIBUTE1 ,
1444 X_ATTRIBUTE2 ,
1445 X_ATTRIBUTE3 ,
1446 X_ATTRIBUTE4 ,
1447 X_ATTRIBUTE5 ,
1448 X_ATTRIBUTE6 ,
1449 X_ATTRIBUTE7 ,
1450 X_ATTRIBUTE8 ,
1451 X_ATTRIBUTE9 ,
1452 X_ATTRIBUTE10 ,
1453 X_ATTRIBUTE11 ,
1454 X_ATTRIBUTE12 ,
1455 X_ATTRIBUTE13 ,
1456 X_ATTRIBUTE14 ,
1457 X_ATTRIBUTE15 ,
1458 X_ATTRIBUTE16 ,
1459 X_ATTRIBUTE17 ,
1460 X_ATTRIBUTE18 ,
1461 X_ATTRIBUTE19 ,
1462 X_ATTRIBUTE20 ,
1463 X_BIRTH_CNTRY_RESN_CODE
1464 );
1465
1466 END add_row;
1467
1468
1469 PROCEDURE delete_row (
1470 x_rowid IN VARCHAR2,
1471 x_mode IN VARCHAR2
1472 ) AS
1473 /*
1474 || Created By : bshankar
1475 || Created On : 28-AUG-2000
1476 || Purpose : Handles the DELETE DML logic for the table.
1477 || Known limitations, enhancements or remarks :
1478 || Change History :
1479 || Who When What
1480 || (reverse chronological order - newest change first)
1481 */
1482 BEGIN
1483
1484 before_dml (
1485 p_action => 'DELETE',
1486 x_rowid => x_rowid
1487 );
1488
1489 IF (x_mode = 'S') THEN
1490 igs_sc_gen_001.set_ctx('R');
1491 END IF;
1492 DELETE FROM igs_pe_stat_details
1493 WHERE ROWID = x_rowid;
1494
1495 IF (SQL%NOTFOUND) THEN
1496 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1497 igs_ge_msg_stack.add;
1498 igs_sc_gen_001.unset_ctx('R');
1499 app_exception.raise_exception;
1500 END IF;
1501 IF (x_mode = 'S') THEN
1502 igs_sc_gen_001.unset_ctx('R');
1503 END IF;
1504
1505
1506 END delete_row;
1507
1508
1509 END Igs_Pe_Stat_Details_Pkg;